I have the following table: Table A FNAME | LNAME james | Bond John | Brit raje | van I want to insert first letter from first column with full last name to create a new username column for the table: Table A USERNAME jbond jbrit rvan If this is not possible, I at least need to update the lastname from lname to newly created user...
I have a task which require the data manipulation to transpose the rows to columns. The data is stored in SQL Server, typical relational database model. I know it can be done with TSQL, but it is so complex that there are almost ten different row groups to be transposed to about 200 columns. Just wondering whether there is other better...
The SQL below returns any records with min number and it should work fine: SELECT Id, Number FROM TableA WHERE Number = (select Min(Number) from TableA) Is there way I can write a SQL without the sub Select statement, but still returns the same result? ...
I got a new project from my teacher to convert database to another. How can I convert a MS SQL database into MYSQL using JAVA ...
I want get the maximum value for this record. Please help me: SELECT rest.field1 FROM mastertable AS m INNER JOIN ( SELECT t1.field1 field1, t2.field2 FROM table1 AS T1 INNER JOIN table2 AS t2 ON t2.field = t1.field WHERE t1.field3=MAX(t1.field3) -- ...
Hello everyone, I am using SQL Server 2008 Enterprise. SQL Server 2008 always tries to eat as much as memory as it could. Any ways to configure per database or per instance or per server level max memory usage of SQL Server 2008? thanks in advance, George ...
Hi, I am using VB.net and SQL Server 2005. My Problem is that I want to give user a message if the delegate limit has reached for that course. See I have a course where I have MinDelegate and MaxDelegate limit. I want to stop inserting and give user message that "Max Delegate limit has reached can't have more delegates for this course...
Hi I have a query that joins two tables. The join is done on several columns, including a dateTime column. The problem that I have at the moment is that the one dateTime column stores milliseconds whereas the other table is populated from a file which does not contain milliseconds Because of this the join will never return results. Is t...
What would be the best approach to optimizing spatial index on geometry data? Specifically: How to choose values for grid hierarchy? How to choose value for Max cells per object rule? These are very much data related so I'm looking for a sensible approach to analyzing the data and then testing out the values. ...
Hello everyone, I am using SQL Server 2008 Enterprise. I want to know how many different ways or different reasons why database (not instance, not server, but database) is stopped/started. I am analyzing SQL Server logs. Currently as far as I know, two possible ways/reasons are -- taking database offline/online and stop/start instance/...
I have the following recursive table-valued function in MS SQL, in order to retrieve a hierarchy of objects from the database: WITH tmpField (ParentNum, ChildNum, FieldNum, FieldDescr, Iteration) AS ( SELECT Field.ParentNum, Field.ChildNum, Field.FieldNum, Field.FieldDescr, 1 FROM Field WHERE Field.ParentNum = @ParentNum UNION...
I'm trying to debug a fairly complex stored procedure that joins across many tabls (10-11). I'm seeing that for a part of the tree the estimated number of rows drasticly differs from the actual number of rows - at its worst SQL server estimates that 1 row will be returned, when in actuality 55,000 rows are returned! I'm trying to work o...
I had a requirement to create a query in MS SQL where the search condition would include/exclude a table based on user input. Say I have two tables’ TABLE_A and TABLE_B with columns KEYCOLUMN_A, COLUMN_A in TABLE_A and columns FKCOLUMN_B, COLUMN_B in TABLE_B And a query like SELECT TABLE_A.* FROM TABLE_A, TABLE_B WHERE TABLE_A.KEYCOLU...
I'm trying to diagnose a slow stored procedure (see this question) and I've noticed that for my auto-generated stats (the ones named things like _WA_Sys_0000000A_0D0FEE32) I cant view the detailed histogram. If I click on the "Details" tab I just get the message: No statistics information available. If I click on the details tab for a...
I'm tring to run a find and replace query on some sql data using Management Studio. I basically want to remove the word FREE from any content. I tried running this query; UPDATE Table_1 SET ContentDetails = REPLACE(ContentDetails, 'FREE', '') WHERE (ContentDetails LIKE '%FREE%') But I get an error saying that data...
I have two SQL Server connection strings, CX and CY. What I need to do is Ensure CY has no tables in it. Backup the database CX. Restore it to CY. Haven't found what I am looking for yet. I don't want a tool to do this, I need to do it in C# code at runtime because the action of adding a new Client needs to copy a master DB to an e...
I'm building an ASP.Net/MVC application using SQL 2008 Developer edition and a DB in Sql2005 compatibility mode. Using Entity Framework as DAL. My problem is that I have a table where I'm using the integer identity column in a like an Invoice Number, that is, it always has to be unique and never reused. So using the GUID column type w...
I have a table like this: ID CatID Filename 1 1 abc 2 2 abc 3 3 cat 4 2 dog 5 1 dog What I want to do is get just 1 ID per filename. I dont mind what ID i get i.e. the first or the last. How would I go about doing this? ...
I have a stored procedure which filters based on the result of the DATEADD function - My understanding is that this is similar to using user defined functions in that because SQL server cannot store statistics based on the output of that function it has trouble evaluating the cost of an execution plan. The query looks a little like this...
One of user name tried login many times and failed. Sql server 2005 locked account automatically. How can I unlock user account in SQL Server 2005 Management Studio? ...