Hi, I have an SQL (Microsoft SQL 2008) table with XML data in one of the columns. Each XML root node has an attribute which is a GUID. For example: <!--Row 1--> <example:root id="E0B1BCEA-C0E2-4d7c-BF67-FA9A7C3FBA73"> [...] </example:root> <!--Row 2--> <example:root id="13BB87F4-32A5-4de7-8CE9-E62AF002B958"> [...] </example:r...
Hi everyone! I am trying to put full text search working on SQL Server 2008, however the table i am trying to index is a table with a composite primary key, something like this: EXEC sp_fulltext_catalog 'My_Catalog', 'create' EXEC sp_fulltext_table 'Message', 'create', 'My_Catalog', 'PK__MESSAGES__C87C0C9C0EC32C7A' // PK__MESSAGES__C87...
Suppose I have 90 seconds. If I want to display the result in terms of minutes and second, I do it by using select Time= '0' + CAST( 90/60 as varchar(2)) + ':' + CAST( 90%60 as varchar(2)) The output is Time 01:30 I have appended 0(zero) because if you do a select getdate() the output will be yyyy-mm-dd hh:mm:ss:ms What is the...
Hello all, I need some help with a SQL query. Here is what I need to do. I'm lost on a few aspects as outlined below. I've four relevant tables: Table A has the price per unit for all resources. I can look up the price using a resource id. Table B has the funds available to a given user. Table C has the resource production info...
I have an MS SQL 2000 database that was backed up from a public server and restored at a test location for an upgrade test. The problem is that the user that had access permission on the public server does not exist on the testing server, and now all tables are prefixed with that username (which requires ALL queries against those tables ...
Here is the T-SQL code that I coded, which I imagined to work but didn't work: DECLARE @Local nvarchar(20) SET @Local = 'Yes' SELECT (CASE WHEN @Local = 'Yes' THEN ID ELSE COUNT(ID) END) FROM myTable Is there something I am doing wrong or is there any other way to do that? Thanks. ...
Given these tables table Channel -------------- ChannelID int IDENTITY <other irrelevant stuff> table Program -------------- ProgramID int IDENTITY ChannelID int AiringDate datetime <other irrelevant stuff> and this query SELECT C.ChannelID, t.AiringDate FROM Channel C LEFT JOIN ( SELECT distinct ChannelID FROM Pr...
Get week number from dates in T-SQL ...
I have a query returning 24 records for users with code A and 54 records for users with code B and sometimes it will return Users with code C, D....etc there can be a total of 15 different Codes. I want my query to only display the codes once, instead of returning retpeat users. If i do something like Select Count(user_code) from tb...
I have a system created that a user at one of our other locations and on their server inserts a record. That data is then replicated to a central server. Users working on the central server are allowed to edit that record which means I have to lock the editing capabilities at the location the record was created. However, i would like t...
How can I make identical output from a transformation go to two separate places e.g., an OLE DB destination and a DataReader destination? Background: I have an existing package that reads data from a text file, does some transformations, and loads the data into a SQL Server table. Now I'm trying to make the package be callable from ...
Hi all, I have an XLS file with the following rows: store name - idtype1 - idtype2 - idtype3 store 1 - 1a - 1b - 1c store 2 - 2a - 2b - 2c There are actually 5 ID types and over two hundred stores, there are also columns such as address, email etc. What I need to do is load this data into two tables,one bei...
I am having a problem with a FreeText search, currently running on a SQL 2000 server. In a table of approximately 1.3 million rows which contain company names, I am attempting to use a FreeText query. However since SQL Server strips out special characters and single characters when building its index, our code does the same when subm...
I have 2 tables as follows (sample data shown): TableName: A ID Type 1 Bug 2 Requirement 3 Task 4 Specification 5 Bug 6 Specification 7 Production Issue 8 Production Issue 9 Bug 10 Task Tablename: B ID RelatedID 1 2 1 7 5 8 5 4 9 6 9 10 I want to fetch all the bugs that have atleast one related p...
I am going on a job interview and have zero experience with MS SQL Server. However I have 1 year with Oracle. Is there such a huge difference between the two? What programming questions can I expect? ...
Can I write the T-SQL like below select * FROM (select * from TableA where FieldA = 1 ) where FieldB > 10 which means I want to query from the results of another query. ...
I have created a statistics in a table using SQL SERVER 2008 . In which system table this information is getting stored (Just like all the table info are stored in in sys.tables)...? ...
Hey guys! I have the following code in SQL (2005) which calculates the avarage user logins on a systm: with user_total as ( select COUNT(distinct ID) as counter FROM [dbo].[LOG] where [LOG].DESCRIPTION='Login success.' AND Convert(datetime,convert(char(10),[LOG].CREATED_ON,101)) BETWEEN '2009-01- 01...
I have a column with data in a special “format”. Example: L100000 L50 L5 S10 S15L10 S20 S90 S10 S10L5 S10L40 S10L5 The value consists of an “S” and/or an “L”, each with a number following the letter. I need to write a query, which will return two columns, “S” and “L”, which will have only the coresponding numeric value following the l...
Sometimes I need to treat the same table as two separate tables. What is the solution? ...