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...
Consider this query: SELECT F1,F2 FROM TABLE GROUP BY F1 Selecting F1 is valid, but F2 seems to be incorrect (after all it can change from row to row). However SQL Server does not check any logic involved here -- for example F2 could be dependent of F1 (because of the JOIN clause, for example). I know the workarounds, but my questio...
Hi, I have 2 columns with "name" and "surname" I want to return a result with the two concatenated. but I have a problem, the surname column accept null values and when it's the case the concatenation is null.. I would like in this case just to have the NAME here is code: SELECT c.ID_CONT, c.ID_TYPE_CONTACT, c.ID_PARAM_CE...
What is the caret (^) doing in the following SQL Server query? SELECT 1^2, 1^3; which gives the results: 3 2 I came across this before I found the SQUARE() function. ...
Is there a way to retrieve all the data from an IN clause? Let's assume my table got (ID,Name): 0 Banana 1 Mango 2 Papaya 3 Lemon and my query: SELECT * FROM Fruits WHERE Name IN (Banana,Mango,Orange) I Want 'Orange' to return, with an empty ID (since there's no register). How to do this? ...
When a sproc is executed on one of our ASP.NET pages, it times out on the SQL Server with the exception Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. When I execute the same sproc in SSMS, it returns relatively quickly. The SQL Server and IIS are on the same box. I logg...
I have the following table I have inserted Product B to it and it gives me an ID of 15 Then I have the definition table which is as follows. I want to select the ProductDefinition rows where ProdID = 14 and replicate the same and insert it for ProdID = 15 like the following How to achieve this using SQL code? ...
In SQL Server, I have a new column on a table: ALTER TABLE t_tableName ADD newColumn NOT NULL This fails because I specify NOT NULL without specifying a default constraint. The table should not have a default constraint. To get around this, I could create the table with the default constraint and then remove it. However, there...
Are you able to use COUNT in a query with a HAVING clause so that the COUNT returns the number of rows? When I try, Im getting the count of the number of times the ID shows up in the table. Here is the query: SELECT col_appid, min(col_payment_issued_date) as PayDate FROM tbl_ui_paymentstubs WHERE isnull(col_payment_amount,0) > 0...
I've been using SQL Server profiler quite frequently to check for redundant or badly performing queries. But is there an event (among the giant list) that allows you to log the total amount of time it takes to transfer the data from the data base to the application? This would be a very good indicator for queries that return way more d...
Hi fellow stackers, I have a question about how to “pivot/total” (for want of a better word) some data around in SQL server. The data is basically staff shifts and then hours lost from those shifts. So for example I have a record in one table Staff_ID Shift_start Shift_end 37 09:00 17:30 And then we would give ...
declare @hdoc int, @xmlchar nvarchar(max) set @xmlchar = '<root> <row _Fld394=" 61640200" /></root>' exec sp_xml_preparedocument @hdoc out, @xmlchar select _Fld394 from openxml(@hdoc, '/root/row') with (_Fld394 nvarchar(9) '@_Fld394') exec sp_xml_removedocument @hdoc //result = '61640200' //must be = ' 61640200' If you look at _Fld39...
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: ...
All, I am inserting as xml datatype in a table, an XSLT document. Somewhere in my XSLT document I am using the character < for a reason. However when inserting to the table, the SQL Server replaces the < with < Any ideas how to store it without having SQL Server to change it? Thanks, M ...
Hi everyonoe, I have installed Oracle Client 10g 32 bit and ODAC 11g R2 on my Windows 7 machine, but I cannot see any Oracle Providers in MS ODBC Administration or when I try to created a linked server to Oralce in SQL Server Management Studio or in Visual Studio 2010. Can anyone please help me out as what to do? I can connect to Oracle...
I have a WCF Service running under IIS 7.0. The app pool identity is set to a user account lets call it "MyDomain\MyAcc." I have given "MyDomain\MyAcc" login permissions to the SQL 2005 Server, and the two DBs that it uses on that server. When I try to invoke one of the WCF methods I get the following in my logs: "Login failed for use...
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...