Is there a way to use one CASE statement and return 2 different values? Following query has 2 CASE statements with same conditions. select case when DA.value = 1 then da.Good else da.Bad end as Foo, case when DA.value = 1 then ot.Good else ot.Bad end as Bar, from someTable DA (nolock) join otherTable OT (nolock) on OT... wh...
I have a carsale project. It completely works on localhost. I have a "AddCar.aspx" page that inserts a car record with car's features. Car features are selected with checkboxes. If i don't check any checkbox, there is no problem. But if i check one of feature checkboxes, my page gives an error like this: "Subqueries are not allowed i...
I have a UDF in SQL 2005 that I would need to schemabind as it is used in a view that I need an index on. This UDF gets information from a table that is located in a different db (same server) then the one where the UDF is located. Since it is invalid to specify table as [DBName].dbo.[Tablename], is there a way I can get the info from ...
Preferably I would like to know how to do it using the SQL Server Management Studio interface but that isn't completely necessary. If you simply have a script to add one after a table is made that would be fine. ...
I want to update some data in a specified case, else these fields are not to be updated. What can i write code in a stored procedure for this? ...
I'm trying to retrieve db sizes of all databases in my SQL Server 2005 instance. I found this select filename, size, 8192.0E * size / 1048576.0E as result from master..sysaltfiles Is the result varibale in KB? How can get this to MB? I'm confused. ...
My table name is table_1, fields are: name dept location status Status is already 1, but I want to update the status into 2 based on a certain condition else not update. My code is UPDATE Table_1 SET model = @model, make = @make, [Asset Serial No / Service Tag] = @Asset, [IT Asset Tag] = @AssetTag, ...
How to round with no trailing zeros in SQL Server 2005? select round(100.5555, 2) ...yields 100.55**00**. How to get rid of the zeros? ...
I need to perform a restore of database from .NET using SMO.Prior to the restore I set the database into RESTRICTED_USER WITH ROLLBACK IMMEDIATE mode. This works fine during testing/debugging - all users are dropped. However, when this code is run as part of an automated process at night, sometimes I get the following exception: Micr...
Is there a way to do this if an unsaved tab gets accidentally closed? ...
Background I have a custom authentication back end for our django applications that refers to an LDAP server. As soon as I authenticate someone, I have a wealth of information that our network infrastructure guys put in the LDAP server about the user - their last names (which can change, for instance, if they marry), their e-mails (whi...
Hi, I have a table that looks like this: id, col1, col2 col3 0 "Goat" "10" "0" 1 "Cat" "11" "0" 2 "Goat" "12" "1" 3 "Mouse" "13" "0" 4 "Cat" "14" "2" I want be able to return the UNIQUE values in Col1 AND if there are two identical values in col1 then use col3 to decide which value to use i.e. if i...
Hi, I have a server (SQL Server 2005) with multiple archive databases (1 per quarter stretching back 8 years) that are all structurally identical. I frequently need to query back over a certain date range that spans n databases, usually n is small 1-3 but it's possible I need to query the whole set. Any thoughts n the most efficient w...
I tried two different variations on the same thing. The first version selects from freetexttable, the other insets into a temp table and selects from that. I've tried numerous variations on the first version (select several combinations, at both levels of scope, of group by, distinct, and casting [rank] to an integer. Regardless, the ...
I am creating this huge csv import, that uses the ms text driver, to read the csv file. And I am using ColdFusion to create the scheme.ini in each folder's location, where the file has been uploaded. Here is a sample one I am using: [some_filename.csv] Format=CSVDelimited ColNameHeader=True MaxScanRows=0 Col1=user_id Text width 80 Col...
My table TEST has the following rows: test | 1 test | 2 test | 3 How I query it to get the following result? test | 1 - 2 - 3 ...
How to create t-sql scalar value function that input string multivalued and that sould return string1 + string2 + string3. ...
I've managed to connect my SQL Server 2005 in Windows 64 bit server with ORACLE database. (Thanks to Mr. Jeyong Park :http://knol.google.com/k/jeyong-park/accessing-oracle-data-source-from-64bit/3vywlm4f31xae/12) The problem is : In SSIS when I used Oracle as a OLE DB Data Source and previewed the data, it works, however, when I run the...
Dear All in this SQL code DECLARE @n tinyint WHILE (@n > 0) BEGIN SELECT @n AS 'Number' ,CASE WHEN (@n % 2) = 1 THEN 'EVEN' ELSE 'ODD' END AS 'Type' SET @n = @n - 1 END How could I put union clause in this could to have the result shown in one result set? ...
If I pass in an xml parameter to a stored proc which looks like this: <ClientKeys> <ck>3052</ck> <ck>3051</ck> <ck>3050</ck> <ck>3049</ck> ... </ClientKeys> ...and then convert the XML to a temp table like this: CREATE TABLE #ClientKeys ( ClientKey varchar(36) ) INSERT INTO #ClientKeys (ClientKey) SELECT Param...