I have a batch file I'm running from a Windows XP w/service pack 3 workstation which applies SQL changes to a database using sqlcmd.exe in SQL 2005. I've got a command-line entry for TortoiseSVN to automatically update the local copy of my repository like so: tortoiseproc /command:update /path:"C:/SVN/My Code/Dev/2009.07.23" /closeonen...
I'm currently running the sp_stored_procedures stored procedure to retrieve a listing of SP for a given data source. I will need to query this dataset by it's PROCEDURE_NAME and PROCEDURE_OWNER columns. However, PROCEDURE_NAME seems to have a strange return value. It seems to append a semicolon and a number to the end of the name. se...
Hi, I want to learn T-SQL but there are so much kinda useless documents around internet. I've been searching tutorials but either most of them are not for beginners or cut off the tutorial at the best part. I wanna learn it from start to advanced so could you guys please show me a website or something where I can learn it best. Thanks...
Running the following query in Management studio update table_name set is_active = 0 where id = 3 gives the following error, Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. Th...
Im wondering about the real advantage of performing dml commands (inserts, updates, deletes) in the database via stored procedures for simple CRUD applications. Whats the beneffit with that appoach over just using some generic procedure in the front-end that generates the dml commands? Thanks in advance. ...
I want to create a rotating logic in sql like consider there are 3 numbers 1,2,3 then first week 1,2 will be selected next 3,1 next 2,3 and so on..... if there are 4 numbers 1,2,3,4 then 1,2 next 3,4 next 1,2 so on... Like that i want to generate the numbers in sql server.Please help me. ...
Hi, how I can do this: a=a+1 in sql server query? Thanks! ...
Hi all, I want to check if there are any records in a table for a certain entry. I used COUNT(*) to check the number of records and got it to work. However, when the number of records for an entry is very high, my page loads slowly. I guess COUNT(*) is causing the problem, but how do I check if the records exist without using it? I onl...
Hi, how can I retrieve a string (like 'select 1') from a record and pass it to sp_executesql to make it eval it? Thanks ...
Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method does send a few more bytes either way but that might not be as important as the speed ...
Hi guys, simple problem, but perhaps no simple solution, at least I can't think of one of the top of my head but then I'm not the best at finding the best solutions. I have a stored proc, this stored proc does (in a basic form) a select on a table, envision this: SELECT * FROM myTable okay, simple enough, except the table name it ne...
As an example, I want to get the list of all items with certain tags applied to them. I could do either of the following: SELECT Item.ID, Item.Name FROM Item WHERE Item.ID IN ( SELECT ItemTag.ItemID FROM ItemTag WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55) Or SELECT Item.ID, Item.Name FROM Item LEFT JOIN ItemTag ON It...
I have a table in a database where one of the columns should have a value from 1 to 5. How can I write this limitation into the database? Do I use a constraint? What's the best practice say about this kind of thing? I am using SQL Server 2005 ...
I have a table that I process using a cursor. Lets say it's structure is like this: RID | School | Order | Text Now, I filter out the other schools(so only mine is shown) and then I ORDER BY order, so that I get the text arranged how I want. Now, my problem is, the order isn't straight incrementing(though all of them are unique per scho...
SQL 2008 I have a commission table that looks like this: Comm% | ProfitStartRange | ProfitEndRange 0.01 | 0.00 | 100.99 0.02 | 101 | 500.99 0.03 | 501 | 1000.99 etc... Basically I want create a query that returns the appropriate Comm% based on a value. I would like to do this inline and not in a user defined function if possible ...
Sorry, but I am new to TSQL so I don't even know basic things. I've been working on this function, but I've been running into a lot of syntax issues. If someone can assist me in writing this function, I would appreciate it. 1. I would like it to check for the existence of a table before attempting to drop the table 2. I would like ...
TSQL - how to find if a column has a space char(32)? select * from [sometable] where CHARINDEX(' ', [somecolumn]) > 0 doesn't work? Any ideas? ...
Why is ROW_NUMBER() not recognized as a function name in SQL Server 2008? I try this SELECT ROW_NUMBER() AS Row, Lname FROM MEN GO and I get this error: Msg 195, Level 15, State 10, Line 1 'ROW_NUMBER' is not a recognized function name. ...
I actually want to achieve the following description This is the table argument I want to pass to the server <items> <item category="cats">1</item> <item category="dogs">2</item> </items> SELECT * FROM Item WHERE Item.Category = <one of the items in the XML list> AND Item.ReferenceId = <the corresponding value of ...
Given a table Records with the columns id int, Type int and Name varchar(50), I can make search queries like so: SELECT id, Type, Name FROM Records WHERE Name LIKE '%Foo%' To tweak the performance a little, I'd like to give only a limited amount of results; currently 100 — simply by adding TOP 100 to the statement. This, however, can ...