Is it true that better concurrency can be achieved in Oracle databases than in MS SQL Server databases? In particular in an OLTP scenario, such as an ERP system? I've overheard an SAP consultant making this claim, referring to Oracle locking techniques like row locking and multi-version read consistency and the redo log. ...
I have two tables order [ order_id ] order_line [ order_id, product_id, cat_id ] I want to select every order & associated order_line, but I want row per order, and i only want to select cat_id from order_line so i want a result like this.. results: [0] order_id, cat_id1 , cat_id2, cat_id3 [1] order_id, cat_id Possible? ...
I have a inline select statement to calculate the product of the set of values. Since SQL Server 2005 doesn't have a built in Product aggregate function, I am using LOG/EXP to get it. My select statement is: (select exp(sum(log(value))) from table where value > 0) Unfortunately I keep getting the following error: Msg 3623, Level 16...
I've got a simple table and want to store its content into a xml on the harddrive. There should be one root element for the whole table, one element per table row and one child element per table column. What possibilities do I have? Thanks a lot Tomas ...
I have very simple problem that I can't solve. I need to do something like this: select distinct * from (1, 1, 1, 2, 5, 1, 6). Anybody can help?? ...
hi there I'm tryin to use SQL to build a comma separated list of cat_id's the code is: declare @output varchar(max) set @output = null; select @output = COALESCE(@output + ', ', '') + convert(varchar(max),cat_id) edit: changed '' to null, STILL same. but the output im getting is like so: , 66 , 23 the leading comma sh...
Hello, I need to find out the version of an assembly deployed to an SQL Server database. I need to do it via script or other programmatic way to know if i need to redeploy the assembly. Any ideas? Thanks. ...
I want to create a DB , where each table's PK will be GUID and which will be unique across the DB, Example: my DB name is 'LOCATION'. And I have 3 table as 'CITY' , 'STATE' and 'COUNTRY'. I want that all the 3 tables have same PK field as GUID ,and that value will be unique across DB. How to do this in SQL Server, any idea? I have n...
In SQL Server 7.0, I need to find out which users have write access to which tables in a particular database. I know that I can do this in Enterprise Manager by going through each table in the database and looking at the access granted to those tables - but there are a few hundred tables in the database. As I'm only concerned with a ha...
Using SQL Sever 2005 From the below Query Am getting total worked time means Outtime - Intime, Suppose when I minus the Next day time means showing wrong time Query Select ID, Normal_Intime, Normal_Outtime, Date, Intime, outtime, CONVERT(char(8), CASE WHEN InTime < Outtime THEN CASE WHEN OutTime > Normal_Outtime THEN CAST(Normal_Out...
I have the same question to ask, but I'm using both SQL 2000 Server. When the table1 in SQL Server 2000 gets updated/inserted/deleted, I have to update another table in SQL Server 2000. How is it possible to achieve that ? Thanks in advance ...
I and trying to create a method to run a .sql file on an SQL Server database. The code i have is: SqlConnection dbCon = new SqlConnection(connstr); FileInfo file = new FileInfo(Server.MapPath("~/Installer/JobTraksDB.sql")); StreamReader fileRead = file.OpenText(); string script = fileRead.ReadToEnd(); fileRead.Close(); SqlCommand comm...
Hi, Having a table with this structure... Table_files id_file (PK) file_name file_path ... can I have a constraint that allows me to not duplicate the pair "file_name"+"file_path" (but allows me to duplicate the "file_name" and "file_path" individually), where the only Primary Key is the field "id_file"? Thanks ...
Hello, I am new to SQL and need to write a complex query. Can you please help? I have two tables. One is called PATIENTS and the other one is called CASES. PATIENTS has a "patient number" and a date entered. CASES has "patient number," "case no." and "date modified." The two tables are connected with the "patient number." There are mul...
I am using SQL Server 2005 Express. While I am executed the query I am getting wrong values select City, City2 from sample.dbo.NameAddress where FirstName like 'rama%' and LastName like 'suresh%' Getting same values for the both columns but actually they are different in DB, When I see entire table ...
Hi Two table are tied with each other because of FK constraint. I am trying to update these tables by disabling ALL Trigger but still getting the following error :- The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_TEST_REFERRING_REFPHYSI". The conflict occurred in database "ccdb", table "dbo.RefPhysician", column 'R...
I have a query that uses for xml explit to return XML result. select ... from ... order by [BatchType!1!TypeName], [FormType!2!TypeName], Tag, Parent for xml explicit, root('ClientImages') But the name of resultant column name is something as cryptic as Is there a way to change the column name? [ANSWER] I had a several nested ...
Hi, i have the following select statement to get the last login from the user table. this works very well under sqlite, now im porting the database and have Compact Edition from Microsoft. SELECT LOGIN FROM USERS WHERE LASTLOGIN = (SELECT MAX(LASTLOGIN) FROM USERS) The lastlogin column is datetime. This doesn't seems to work, w...
Hi I dont have any idea why am i getting this error :- Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. I was trying to run this query:- ALTER TABLE Test1 NOCHECK CONSTRAINT ALL ALTER TABLE Test2 ...
I want to display Alias name based on the value of some other column name in the query in SQL Server. For e.g. SELECT P.Amount AS (CASE P.Type WHEN 'Individual' THEN 'Salary' ELSE 'Profit' END) FROM Person P I know the above is not right, but something like this will help. ...