Consider a table with a column amount, Amount -1235.235 1356.45 -133.25 4565.50 5023 -8791.25 I want to my result pane to be like this, Debit Credit 0 -1235.235 1356.45 0 0 -133.25 Here is my stored procedure, USE [HotelBI_CustomDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_Ge...
I have a table with the following fields. My requirement is to delete the records from the table when 1. The Flag is set to 'No' 2. For a title, in one record if the flag is set to 'No' and in another record if it is set to 'Yes'. Delete both the records. For example TH-123, In 1st record the flag is set to 'No' and in 4th record flag i...
Hi, I was wondering if it was possible to determine from which column a coalesce value is drawn from? I have the following example data (actual years range from 1989 - 2010 in data, not shown for brevity) ID | 2000 | 2000 value | 2001 |2001 value | 2002 |2002 value | 2003 |2003 value | 2004 | 2004 value | 2005 | 2005 value...
Is there any thing from which we can faster get the no. of rows in a table instead of using count(1). Further there are two cases : a) when we want to get the no. of rows in a table. b) when we just want to know if there is at-least one row. Thanks in advance. ...
In table A I have 2 columns: ID (int, PK) MaxUsers (int) In table B I have 2 columns: ItemID (int) UserID (int) The number of records in table A with matching ItemID's cannot exceed the MaxUsers value. Is it possible to write a T-SQL Table Constraint so that it's not physically possible for this to ever happen? Cheers! Curt ...
I'm doing some recon work and having to dig through a few hundred SQL Server database tables to find columns. Is there a way to easily search for columns in the database and return just the table name that the column belongs to? I found this, but that also returns Stored procedures with that column name in it... ...
My configuration looks like this: <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" > <session-factory name="kvws.kist.suche.dbadapter.nhentities"> <!-- Driver --> <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property> <property name="dialect">NHibernate.Dialect.MsSql2005Dial...
I want to move my SQL Server 2005 database to Oracle. How can I do that? What are the step I want to follow? Are there any tools for this? ...
Hello, I have deployed a solution correctly on IIS 5.1 (hopefully) as it loads up and shows a "User not authorised page which I created myself". The fact this shows is a good sign but it also hides any errors as I think I should be authorised. To try an get an error message I ran a method on a controller which didnt require any author...
I have Following structure Col1 Col2 Col3 --------------- F P R1 F P R2 F P R3 F P R4 Col3 values can be any thing Now I want in following format only top 3 Col1 Col2 Res1 Res2 Res3 ------------------------------ F P R1 R2 R3 ...
When I run the profiler while running my application, it only seems to show SELECTs, not INSERTs or anything that changes the database. Yet my database is being updated, so those commands must be being executed. What do I have to do to get it to show updates? (I am using Entity Framework, btw, if that might make a difference.) ...
Hi there I am using SQL Server 2005 SSIS and we are using the Data Flow Task to move data from one table to another. This works well. Now we have another requirement to do data update from the same table using this approach. Is this possible to use the same approach for as follow: *) We have a dataset from Table A based on complex que...
Hello, I have a table that contains a list of tasks; TableName: Tasks. Fields: (ID Int, Description nvarchar) The tasks are completed daily and are logged in a table like follows; TableName TasksDone. Fields: (TaskID Int, TaskDate DateTime) I need to have a query that runs for a date range and shows the tasks that were NOT done (do ...
Hi, I'm trying to transform a table to an XML struture and I want one of the columns in my table to represent a parent node and the other column to represent a child node. I have got part of the way but I don't have the complete solution. I need the TABLE_NAME column to transform to a xml parent node and the COLUMN_NAME column to trans...
I am working with cursors and successfully executed in T-SQL and the database I use is Microsoft SQL Server 2005. My query is after I execute the cursor, the output is shown in message area. When I deallocate the cursor using deallocate <cursor name> it gets deallocated. Again I execute my cursor. Now in the message area I get this: ...
Hi Guys I have a fully functioning query, but need to do a little formatting. One of my fields is called a route name. An example of the data in that field is "PRN L5 L7 S LAM C" Now what I need to do is firstly remove the PRN, secondly split the route into seperate columns, so column 1 would have L5, column 2 would have L7 ect.... Now...
I have the following tables: Entry EntryID - int EntryDate - datetime Hour EntryID - int InHour - datetime OutHour - datetime For each registry in the Entry table, there should be at least one (could be many) registries on the Hour table, like so: Entry EntryID: 8 EntryDate: 9/9/2010 12:31:25 Hour EntryID: 8 InHour: 9/9/2010 1...
So I got a varbinary(max) column in SQL Server 2005 and it's full of XML. A few records somewhere have truncated XML so they're invalid. This means if I run a SELECT CAST(myVarbinaryColumn as XML) ... it blows chunks. How can I filter out/skip invalid xml ? When I've done similar with a varchar that supposedly has dates I could u...
My database contains the following strings along with others that are similar Old Type: New Type: IRP User: dls0835 Old Type: BASE PLATE New Type: IRP User: ter2344 I am trying to not return the first string type but to still return the second string type. Notice how there is no text after the "Old Type:" in the first string and tha...
Hi All, I have a scenario where I need to round and then remove the extra zeros from numbers. So if I have a number that I have rounded (12.456400000) I want the zeros to be removed. Is there a function I can use to remove those numbers? The round function appears to leave the zeros in place? As always greatly appreciate the input. ...