Search Results

Search found 99646 results on 3986 pages for 'sql server 2005 tempdb'.

Page 181/3986 | < Previous Page | 177 178 179 180 181 182 183 184 185 186 187 188  | Next Page >

• Selecting the most common value from relation - SQL statement

  - by Ronnie

  I have a table within my database that has many records, some records share the same value for one of the columns. e.g. | id | name | software | ______________________________ | 1 | john | photoshop | | 2 | paul | photoshop | | 3 | gary | textmate | | 4 | ade | fireworks | | 5 | fred | textmate | | 6 | bob | photoshop | I would like to return the value of the most common occurring piece of software, by using an SQL statement. So in the example above the required SQL statement would return 'photoshop' as it occurs more than any other piece of software. Is this possible? Thank you for your time.

  Read the article

• Very uneven CPU utilization with SQL Server 2012 on 2 processor computer with 16 cores / processor

  - by cooplarsh

  After installing SQL Server Enterprise 2012 with the Server + Cal license model, on a computer with 2 processors each with 16 cores (and no hyperthreading involved) and putting the server under extremely heavy load the 16 cores on the first processor were very underutilized, the first 4 cores on the 2nd CPU were heavily utilized, and the last 12 cores were not used at all (because of the 20 core limit for this sql server version). Total CPU utilization was displaying as around 25%. Unfortunately, the server suffered from extremely poor performance even though if the tasks were evenly distributed across the 20 cores it wouldn't have been anywhere near as bad. The Windows Server was running on a VMWare virtual image under ESX Server, but all of the CPU was allocated to the windows server. We tried changing affinity settings (e.g., allocating most cores to CPU and the others to I/O), but that didn't help solve the performance problems. Upgrading the product edition to SQL Server Enterprise Core 2012 not only allowed the SQL Server to utilize the 12 previously unused cores on the 2nd processor, but it also resulted in a much more even distribution of tasks across all of the processors. To get through the backlog of requests cpU utilization jumped to around 90%, and then came down to around 33% once it was caught up, but performance improved dramatically since we failed over to the newly updated version And the performance issues went away. I was wondering if anyone knows what might cause SQL Server to unevenly distribute the load, relying almost exclusively on the first 4 cores of the 2nd processor that had 12 cores idle, and allocate only a few tasks to each of the 16 cores on the first processor. Also, is there any way we could have more evenly distributed the load across the 20 cores that were being used without the product edition upgrade? The flip side of that question is what did the product upgrade do that caused SQL Server to start evenly distributing the load across all of the cores that it recognized? Thanks to any insight to answer these questions and/or links that might help me better understand how to make sense of what was happenings.

  Read the article

• INNER JOIN vs LEFT JOIN performance in SQL Server

  - by Ekkapop

  I've created SQL command that use INNER JOIN for 9 tables, anyway this command take a very long time (more than five minutes). So my folk suggest me to change INNER JOIN to LEFT JOIN because the performance of LEFT JOIN is better, at first time its despite what I know. After I changed, the speed of query is significantly improve. I want to know why LEFT JOIN is faster than INNER JOIN? My SQL command look like below: SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D and so no

  Read the article

• SQL Server - Query Short-Circuiting?

  - by Sam Schutte

  Do T-SQL queries in SQL Server support short-circuiting? For instance, I have a situation where I have two database and I'm comparing data between the two tables to match and copy some info across. In one table, the "ID" field will always have leading zeros (such as "000000001234"), and in the other table, the ID field may or may not have leading zeros (might be "000000001234" or "1234"). So my query to match the two is something like: select * from table1 where table1.ID LIKE '%1234' To speed things up, I'm thinking of adding an OR before the like that just says: table1.ID = table2.ID to handle the case where both ID's have the padded zeros and are equal. Will doing so speed up the query by matching items on the "=" and not evaluating the LIKE for every single row (will it short circuit and skip the LIKE)?

  Read the article

• Help Reading Binary Image Data from SQL Server into PHP

  - by Joe Majewski

  I cannot seem to figure out a way to read binary data from SQL server into PHP. I am working on a project where I need to be able to store the image directly in the SQL table, not on the file system. Currently, I have been using a query like this one: INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\image.jpg', SINGLE_BLOB) as BLAH This works fine to actually insert the image into the table, but I haven't yet figured a way to retrieve it and get my image back. I am doing this with PHP, and ultimately will have to make a stored procedure out of it, but can anyone enlighten me on a way to get that binary data (varbinary(MAX)) and generate an image on the fly. I expected it to be simple to use a SELECT statement and add a content-type to the headers that indicated it was an image, but it's simply not working. Instead, the page will just display the name of the file, which I have encountered in the past and understand it to be an error with the image data.

  Read the article

• Getting clusters of rows close together in time

  - by Mike

  I have a table basically like so ID | ItemID | Start | End | --------------------------------------------------------------- 1 234 10/20/09 8:34:22 10/20/09 8:35:10 2 274 10/20/09 8:35:30 10/20/09 8:36:27 3 272 10/21/09 12:15:00 10/21/09 12:17:00 4 112 10/21/09 12:20:14 10/21/09 12:21:21 5 15 10/21/09 12:22:39 10/21/09 12:24:15 There are two "clusters" of entries here, 1-2 and 3-5 separated by a gap in time, specifically 30 minutes is what I'm interested in. What I would like is the first and last rows of the cluster of entries. This is fairly easy to achieve by retrieving all the rows and looping through them in order of start time, but I'd like to have it in SQL if possible. I'm using SQL Server 2008, thanks.

  Read the article

• SQL Injection on INSERT

  - by freddy

  Hi, I'm currently testing Vulnerabiltys to SQL Injections for my companys application as an it-trainee. So I found, that the application is indeed vulnerable to injections because I can alter some of the insert statements. So I altered the insert Statement to this: INSERT INTO tablename( column, column1, column2, column3, column4,column5, column6, column7, column8 ) VALUES ( 10965972, 185796154, 25, 23,2023, '', CURRENT_DATE, 'v0201100', 18); DELETE * FROM tablename;-- , 2023,'a', CURRENT_DATE, 'v0201100', 18 ) I thought this should be a correct statement, but the MySQL Server returned this Error: MySQL Error: 1064 (You have an error in your SQL syntax;[...] Would be nice if somebody could help and tell my why the syntax is wrong... Thanks for your help :-)

  Read the article

• Generate SQL Server Express database from Entity Framework 4 model

  - by Cranialsurge

  I am able to auto-generate a SQL Server CE 4.0 *.sdf file using code-first generation as explained by Scott Guthrie here. The connection string for the same is as follows: <add name="NerdDinners" providerName="System.Data.SqlServerCe.4.0" connectionString="data source=|DataDirectory|NerdDinner.sdf"/> However if I try to generate an mdf instead using the following connection string, it fails to do so with the following error - "The provider did not return a ProviderManifestToken string.". <add name="NerdDinners" providerName="System.Data.SqlClient" connectionString="data source=|DataDirectory|NerdDinner.mdf"/> Even directly hooking into a SQLEXPRESS instance using the following connection string fails <add name="NerdDinners" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=NerdDinner;Integrated Security=True"/> Does EF 4 only support SQL CE 4.0 for database creation from a model for now or am I doing something wrong here?

  Read the article

• Using multiple aggregate functions in an algebraic expression in (ANSI) SQL statement

  - by morpheous

  I have the following aggregate functions (AGG FUNCs): foo(), foobar(), fredstats(), barneystats(). I want to know if I can use multiple AGG FUNCs in an algebraic expression. This may seem a strange/simplistic question for seasoned SQL developers - however, the but the reason I ask is that so far, all AGG FUNCs examples I have seen are of the simplistic variety e.g. max(salary) < 100, rather than using the AGG FUNCs in an expression which involves using multiple AGG FUNCs in an expression (like agg_func1() agg_func2()). The information below should help clarify further. Given tables with the following schemas: CREATE TABLE item (id int, length float, weight float); CREATE TABLE item_info (item_id, name varchar(32)); # Is it legal (ANSI) SQL to write queries of this format ? SELECT id, name, foo, foobar, fredstats FROM A, B (SELECT id, foo(123) as foo, foobar('red') as foobar, fredstats('weight') as fredstats FROM item GROUP BY id HAVING [ALGEBRAIC EXPRESSION] ORDER BY id AS A), item_info AS B WHERE item.id = B.id Where: ALGEBRAIC EXPRESSION is the type of expression that can be used in a WHERE clause - for example: ((foo(x) < foobar(y)) AND foobar(y) IN (1,2,3)) OR (fredstats(x) <> 0)) I am using PostgreSQL as the db, but I would prefer to use ANSI SQL wherever possible. Assuming it is legal to include AGG FUNCS in the way I have done above, I'd like to know: Is there a more efficient way to write the above query ? Is there any way I can speed up the query in terms of a judicious choice of indexes on the tables item and item_info ? Is there a performance hit of using AGG FUNCs in an algebraic expression like I am (i.e. an expression involving the output of aggregate functions rather than constants? Can the expression also include 'scaled' AGG FUNC? (for example: 2*foo(123) < -3*foobar(456) ) - will scaling (i.e. multiplying an AGG FUNC by a number have an effect on performance?) How can I write the query above using INNER JOINS instead?

  Read the article

• Capture query results using SQL Server Profiler

  - by SDReyes

  It's possible to capture a query's result using SQL Server Profiler?

  Read the article

• Correct Way to Get Date Between Dates In SQL Server

  - by Chuck Haines

  I have a table in SQL server which has a DATETIME field called Date_Printed. I am trying to get all records in the table which lie between a specified date range. Currently I am using the following SQL DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SET @StartDate = '2010-01-01' SET @EndDate = '2010-06-18 12:59:59 PM' SELECT * FROM table WHERE Date_Printed BETWEEN @StartDate AND @EndDate I have an index on the Date_Printed column. I was wondering if this is the best way to get the rows in the table which lie between those date or if there is a faster way. The table has about 750,000 records in it right now and it will continue to grow. The query is pretty fast but I'd like to make it faster if possible.

  Read the article

• SQL Server Database In Single User Mode after Failover

  - by jlichauc

  Here is a weird situation we experienced with a SQL Server 2008 Database Mirroring Failover. We have a pair of mirrored databases running in high-availability mode and both the principal and mirror showed as synchronized. As part of some maintenance I triggered a manual failover of the principal to the mirror. However after the failover the principal was now in single-user mode instead of the expected "Principal/Synchronized" state we usually get. The database had been in multi-user mode on the previous principal before this had happened. We ended up stopping all applications, restarting the SQL Server instances, and executing "ALTER DATABASE ... SET MULTI_USER" to bring the database back to the expected "Principal/Synchronized" state in a multi-user mode. Question. Does anyone know where SQL Server stores information about whether a database should be in single-user mode or not? I'm wondering if there is some system database or table that has this setting recorded somewhere. In particular we had an incident once with the database on the original principal (the one I was failing over to) where when trying to detach the database it was put into single-user mode. I'm wondering if that setting is cached somewhere and is the reason that SQL Server put it back into single-user mode after a failover.

  Read the article

• connecting c# to sql-server

  - by every_answer_gets_a_point

  i am using this example to connect c# to sql server. can you please tell me what i have to include in order to be able to use sqlconnection? it must be something like: using Sqlconnection; ??? string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\SQL Server 2000 Sample Databases\NORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True"; SqlConnection sqlCon = new SqlConnection(connectionString); sqlCon.Open(); string commandString = "SELECT * FROM Customers"; SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon); SqlDataReader dataReader = sqlCmd.ExecuteReader(); while (dataReader.Read()) { Console.WriteLine(String.Format("{0} {1}", dataReader["CompanyName"], dataReader["ContactName"])); } dataReader.Close(); sqlCon.Close();

  Read the article

• A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement.

  - by Ronnie Chester Lynwood

  hello. im working on a online game. i got some problems with inserting new data to table. im getting 2010-4-8 2:14, *** 37000, 513, [Microsoft][ODBC SQL Server Driver][SQL Server]A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'KN_online', table 'ACCOUNT_CHAR', column 'strAccountID'., 261 NationSelect*** this error in logs. what does this means? how can i fix this? i've tried to delete RULE for my DB but I'm unable to delete RULE. im getting the rule 'dbo.unallowedchars' cannot be dropped because it is bounded to one or more clumn. I set all permissions right but its still not working.. thanks..

  Read the article

• Why won't this SQL CAST work?

  - by Kev

  I have a nvarchar(50) column in a SQL Server 2000 table defined as follows: TaskID nvarchar(50) NULL I need to fill this column with some random SQL Unique Identifiers (I am unable to change the column type to uniqueidentifier). I tried this: UPDATE TaskData SET TaskID = CAST(NEWID() AS nvarchar) but I got the following error: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type nvarchar. I also tried: UPDATE TaskData SET TaskID = CAST(NEWID() AS nvarchar(50)) but then got this error: Msg 8152, Level 16, State 6, Line 1 String or binary data would be truncated. I don't understand why this doesn't work but this does: DECLARE @TaskID nvarchar(50) SET @TaskID = CAST(NEW() AS nvarchar(50)) I also tried CONVERT(nvarchar, NEWID()) and CONVERT(nvarchar(50), NEWID()) but got the same errors.

  Read the article

• SQL Server 2008 - Search Query

  - by user208662

  Hello, I am not a SQL Expert. I’m trying to elegantly solve a query problem that others have had to have had. Surprisingly, Google is not returning anything that is helping. Basically, my application has a “search” box. This search field will allow a user to search for customers in the system. I have a table called “Customer” in my SQL Server 2008 database. This table is defined as follows: Customer UserName (nvarchar) FirstName (nvarchar) LastName (nvarchar) As you can imagine, my users will enter queries of varying cases and probably mis-spell the customer’s names regularly. How do I query my customer table and return the 25 results that are closest to their query? I have no idea how to do this ranking and consider the three fields listed in my table. Thank you!

  Read the article

• Why does SQL Server keep throwing exceptions?

  - by Augusto Càzares

  I have my project in .NET that uses a database in SQL Server. I'm using Linq-to-SQL, sometimes when the project throws me an exception (Constraint) in a part of the project this same error keeps showing in other part of the project when I do another thing with the database. Like when I do an insertion and I had before an exception on delete the insertion throws me the delete exception, and it remains this way until I close and open again the project. My major problem is when this happen in my online project, this error in my project causes me problems in the project I'm testing online (I use the same database). I don't know if this exception is on the memory or something but its have been causing me a lot of headaches.

  Read the article

• "Cleanly" Deploying an ASP.NET Application with LINQ to SQL Server

  - by Bob Kaufman

  In my development environment, my SQL Server is PHILIP\SQLEXPRESS. In testing, it's ANNIE, and the live environment will have a third name yet to be determined. I would have assumed that updating the following statement in web.config would have been enough: <add name="MyConnectionString"providerName="System.Data.SqlClient" connectionString="Data Source=PHILIP\SQLEXPRESS;Initial Catalog=MyDtabase;Integrated Security=True" /> When using SqlConnection, SqlCommand, SqlDataReader and friends, that's all it took. Using LINQ, it doesn't seem to work that nicely. I see the servername repeated in my .dbml file as well as in Settings.settings. After changing it in all of those places, I get it to work. However if I'm doing a few deployments per day during testing, I want to avoid this regimen. My question is: is there a programmatic solution for LINQ to SQL that will allow me to specify the connection string once, preferably in web.config, and get everybody else to refer to it?

  Read the article

• SQL Server 2008 log size management problems

  - by b0x0rz

  I'm trying to shrink the log of a database AND set the recovery to simple, but always there is an error, whatever i try. USE 4_o5; GO ALTER DATABASE 4_o5 SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (4_o5_log, 10); GO the output of sp_helpfile says that log file is located under (hosted solution): I:\dataroot\4_o5_log.LDF please help me perform this operation as the log file got large when importing a lot of data and now this info is no longer needed, have multiple (lots of) backups since then. the exact error message when performing the query above is: incorrect syntax near '4'. RECOVERY is not a recognized SET option. incorrect syntax near _5_log'. i am using visual studio 2010 (also have SQL Server Express installed locally, SQL Server 2008 proper installed at provider (shared)) thnx a lot

  Read the article

• @@TRANCOUNT and a current connection

  - by AspOnMyNet

  Assume I connect to SQL server 2008 via SQL Server Management Studio ( SSMS ) and open new window W1 by clicking on New Query tab and write the following inside W1: BEGIN TRANSACTION; If I execute this statement 5 times, and then write (inside W1) SELECT @@TRANCOUNT; , then the value returned will be 5. But if I open another window W2 ( inside the same SSMS instance and thus on the same connection ) and write inside W2 SELECT @@TRANCOUNT; then value returned will be 0. @@TRANCOUNT variable returns the number of active transactions for the current connection. Both W1 and W2 windows were opened on the same connection, so shouldn’t ( according to the above quote ) in both W1 and W2 variable @@TRANCOUNT hold the same value? thanx

  Read the article

• Storing DateTime (UTC) vs. storing DateTimeOffset

  - by Frederico

  I usually have an "interceptor" that right before reading/writing from/to the database does datetime conversion (from UTC to localtime, and from localtime to utc), so I can use DateTime.Now (derivations and comparisions) throughout the system without worrying about timezones. Regarding serialization and moving data between computers, there is no need to bother, as the datetime is always UTC. Should I continue storing my dates (SQL 2008 - datetime) in UTC format or should I instead store it using DateTimeOffset (SQL 2008 - datetimeoffset)? UTC Dates in the database (datetime type) have been working and known for so long, why change it? What are the advantages? I have already looked into articles like this one, but I'm not 100% convinced though. Any thoughts?

  Read the article

• How can I load a sql "dump" file into sql alchemy

  - by JudoWill

  I have a large sql dump file ... with multiple CREATE TABLE and INSERT INTO statements. Is there any way to load these all into a SQLAlchemy sqlite database at once. I plan to use the introspected ORM from sqlsoup after I've created the tables. However, when I use the engine.execute() method it complains: sqlite3.Warning: You can only execute one statement at a time. Is there a way to work around this issue. Perhaps splitting the file with a regexp or some kind of parser, but I don't know enough SQL to get all of the cases for the regexp. Any help would be greatly appreciated. Will EDIT: Since this seems important ... The dump file was created with a MySQL database and so it has quite a few commands/syntax that sqlite3 does not understand correctly.

  Read the article

• How to get the position of a record in a table (SQL Server)

  - by Peter Siegmann

  Following problem: I need to get the position of a record in the table. Let's say I have five record in the table: Name: john doe, ID: 1 Name: jane doe, ID: 2 Name: Frankie Boy, ID: 4 Name: Johnny, ID: 9 Now, "Frankie Boy" is in the third position in the table. But How to get this information from the SQL server? I could count IDs, but they are not reliable, Frankie has the ID 4, but is in the third position because the record with the ID '3' was deleted. Is there a way? I am aware of ROW_RANK but it would be costly, because I need to select basically the whole set first before I can rank row_rank them. I am using MS SQL Server 2008 R2.

  Read the article

• hash password in SQL Server (asp.net)

  - by ile

  Is this how hashed password stored in SQL Server should look like? This is function I use to hash password (I found it in some tutorial) public string EncryptPassword(string password) { //we use codepage 1252 because that is what sql server uses byte[] pwdBytes = Encoding.GetEncoding(1252).GetBytes(password); byte[] hashBytes = System.Security.Cryptography.MD5.Create().ComputeHash(pwdBytes); return Encoding.GetEncoding(1252).GetString(hashBytes); } EDIT I tried to use sha-1 and now strings seem to look like as they are suppose to: public string EncryptPassword(string password) { return FormsAuthentication.HashPasswordForStoringInConfigFile(password, "sha1"); } // example output: 39A43BDB7827112409EFED3473F804E9E01DB4A8 Result from the image above looks like broken string, but this sha-1 looks normal.... Will this be secure enough?

  Read the article

• What is the preferred sql server column definition to use for a LINQ to SQL Version property?

  - by Mike Two

  We are using the IsVersion property on the ColumnAttribute on a property in a LINQ to SQL class for optimistic concurrency checks. What should the column definition be in the database? Currently we are using version_number int NOT NULL IDENTITY (1, 1) Do we need Identity? Can we get LINQ to SQL to update the version number for us? The only issue with Identity is that every row has a different number. We'd like to see the number increment by 1 when the row is updated.

  Read the article

< Previous Page | 177 178 179 180 181 182 183 184 185 186 187 188  | Next Page >