I have a simple SQL query that updates some date fields in a record, on a SQL Express instance. I'd like to have it run automatically every month. What is the best way of accomplishing this in SQL Server 2005 Express using the Management Studio? ...
Getting this error: Each GROUP BY expression must contain at least one column that is not an outer reference. RowId ErrorDatetime ErrorNum ErrorMessage 824 2010-09-24 08:01:42.000 9001 Account 55 not found 823 2010-09-24 08:00:56.000 9001 Account 22222222222 not found 822 2010-09-24 05:06:27.000 ...
In SQL Server 2005, given the following resultset ID | InstanceNumber | IsArchived 5000 | 1 | True 8347 | 2 | True 9343 | 3 | False 11048 | 4 | False What I would like to return is this: ID | InstanceNumber | IsArchiv...
I was trying to achieve this in SQL, lets say i have a table like below ID DATE 7 2009-12-06 7 2009-01-06 7 2009-12-19 7 2009-12-09 7 2009-20-06 9 2009-07-06 9 2009-11-06 10 2009-01-06 10 2010-12-06 10 2009-04-06 11 2009-08-06 11 2009-10-16 11 2009-11-19 12 2009-12-26 13 2009-04-16 13 2009-09-06 ...
I have to query for total amount of a column using an aggregate function. The column data type is NVARCHAR(MAX). How can I convert it to Integer? I have tried this: SELECT SUM(CAST(amount AS INT)), branch FROM tblproducts WHERE id = 4 GROUP BY branch ...but I'm getting: Conversion failed when converting the ...
Hi all I am sure these has been asked before but cannot find clear instruction how to create a batch file lets call it "Update Database" this batch file should Execute sql scripts located in different folders Execute another 3 bat files. Any quick examples how to do it?Never done it before thanks a lot EDITED Can I do this? :On ...
Hello, I have a table: Task, and another TaskHistory 1 Task -> Many Task Histories TaskHistory has a field named 'Comment'. I would like to from SQL return a string of all of the Task History Comments for whatever TaskId I pass in. Example: GetTaskHistory(@TaskId) Returns: 'Comment: some comment \r\n Comment: another comment \r\n\ ...
I'm experiencing performance issues on my asp.net application. Sometimes it would take the client 30-40 seconds to execute a command, where as in sometimes it would take 3-4 seconds. I tried SQL Profiler and I don't see any problems. I was not able to replicate the issue on my side, under the same scenario when the client was trying. I...
Why does INDEX creation statement have UNIQUE argument? As I understand, the non-clustered index contains a bookmark, a pointer to a row, which should be unique to distinguish even non-unique rows, so insuring non-clustered index to be unique ? Correct? So, do I understand that no-unique index can be only on clustered table? since ...
I am looking for an efficient way to store users' information in a SQL Server 2008 table. In the design all users data will be stored in a series of columns in a single table but one of the columns requires to store a list of values, for example: 'column1' will store the username, 'column2' will store the userID and 'column3' will store...
[1] states: "When data is deleted from a heap, the data on the page is not compressed (reclaimed). And should all of the rows of a heap page are deleted, often the entire page cannot be reclaimed" "The ALTER INDEX rebuild and reorganize options cannot be used to defragment and reclaim space in a heap (but they can used to defragmen...
Hi, Using Tsql, how can I find out when MS SQL server was installed? Thanks in advance ...
Hi, I'm reading an article from this website, but when i run the code that article provided, i get the error: Msg 102, Level 15, State 1, Line 16 Incorrect syntax near '.'. I double checked the syntax and can't find the cause of this error. The code is as follows, formatted by SQL Prompt. Anybody can help me with it? Great thanks. ...
Subquestion to my question [1]: All definitions of (MS SQL Server) index (that I could find) are ambiguous and all explanations, based on it, narrate something using undefined or ambiguously defined terms. What is the definition of index? For ex., the most common definition of index from wiki (http://en.wikipedia.org/wiki/Index_(d...
I'm actually planning on the best way to merge xml data from within the SQL database. I have a table that contains an xml datatype. Inside it are xml's with similar schemas. I wanted to merge, let's say, two xmls and they both contain a <Custom></Custom> root on both ends. What is the best way to do this? First xml: <Custom> <Dat...
I have some legacy code similar to: ... '<field1>' + case when field1 is null then '' else cast( field1 as varchar ) end + '</field1>' + ... Which generates the following XML for empty elements: .... <field1></field1> ... And I'm replacing the query with FOR XML: SELECT field1, ... FOR XML RAW, ELEMENTS Now, ...
I have two columns, senderUserID and recieverUserID in Messages table. I select senderUserID and recieverUserID where current receiver sent a message to current sender in the past. I select 10 rows only each time but sometimes in this table senderUserID appears more than once when i need that only senderUserID will be unique, while recie...
I have a field like： SELECT * FROM ( SELECT 'A9t' AS sortField UNION ALL SELECT 'A10t' UNION ALL SELECT 'A11t' UNION ALL SELECT 'AB9F' UNION ALL SELECT 'AB10t' UNION ALL SELECT 'AB11t' ) t ORDER BY sortField and the result is: sortField --------- A10t A11t A9t AB10t AB11t AB9F Actually I need ...
Hi, this is my first post so if you need clarificatrion on anything then just let me know. My server details are as follows: - Windows 2008 Datacentre edition SQL 2008 standard edition (10.0.1600) 12GB Ram Quad core single processor machine The problem I have a stored procedure that runs and when I have just started SQL up, it takes ...
How can I simplify multiple 'not in' queries? Is it efficient to use multiple subqueries: Not in (...) and Not in (..) and Not in (..) I am using count (sorry forgot about that) Select count (VisitorID) from Company where VisitorID not in (select VisitorID from UserLog where ActionID = 2 ) and VisitorID not in (select VisitorID...