[SQL Server 2008 issues] Give priority to union statements

---

• Give priority to union statements
• Sql force me to include Row_Number() in group by
• Group by without soring
• Concat column values
• Union overwrite null values
• rebuild index for a table failed
• Can concurrent INSERTs mess up a UNION?
• Send multiple HTML tables with sp_send_dbmail
• Mapping inserted / source identity values
• Job Details
• Optimization with multiple Select and Update statements
• How to find a table

Give priority to union statements Posted: 14 Sep 2013 06:55 PM PDT Hi, if I have several SELECT statements with UNION how can I tell to sql to process which one first?Please take a look at this structure:[code="sql"]CREATE TABLE #table1 (ID INT, Title1 NVARCHAR(1), Title2 NVARCHAR(1));INSERT INTO #table1 VALUES(1,'A','B')INSERT INTO #table1 VALUES(2,'C','B')[/code]Now I wish to INSERT the values from #table1 to my CTE with this query:[code="sql"]WITH CTE (ID,Title) AS ( SELECT ID, Title1 FROM #Table1 --I expect this query run first UNION ALL SELECT ID, Title2 FROM #Table1 --I expect this query run after above query)SELECT * FROM CTE[/code]But result is something different CTE sort my table automatically and give me this output:ID,Title---------1,'A'1,'B'2,'B'2,'C'But my query should have this output:ID,Title---------1,'A'1,'B'2,'C'2,'B'How can I generate the output I want?Thank you for help

Sql force me to include Row_Number() in group by Posted: 14 Sep 2013 05:07 PM PDT Hi, this is my table structure:[code="sql"]CREATE TABLE #table1 (ID INT, Col1 NVARCHAR(1), Col2 INT);INSERT INTO #table1 VALUES(1,'B',100);INSERT INTO #table1 VALUES(2,'A',200);INSERT INTO #table1 VALUES(3,'B',300);INSERT INTO #table1 VALUES(4,'C',400);[/code]I want to have SUM(Col2) GROUPED BY(Col1) and a ROW_NUMBER() ORDERED BY (ID)Here is my query:[code="sql"]WITH CTE(Col1, Col2, Row_Num) AS ( SELECT Col1 ,SUM(Col2), ROW_NUMBER() OVER(ORDER BY (ID)) FROM #table1 GROUP BY Col1, ID -- here SQL force me to include ID and it generate incorrect output)SELECT * FROM CTE[/code]Here is my output:col1,col2,Row_Num-------------------B,100,1A,200,2B,300,3C,400,4But I want this output:col1,col2,Row_Num-------------------B,400,1A,200,2C,400,3Thank you for help

Group by without soring Posted: 13 Sep 2013 11:49 PM PDT Hi, this is initializations:[code="sql"]CREATE TABLE #table1(col1 nvarchar(1), col2 int);INSERT INTO #table1 VALUES('B','100');INSERT INTO #table1 VALUES('A','200');INSERT INTO #table1 VALUES('B','300');INSERT INTO #table1 VALUES('C','400');[/code]Here is my query:[code="sql"]WITH CTE (col1,col2) as ( SELECT col1, SUM(col2) FROM #table1 GROUP BY col1)SELECT * FROM cte[/code]Here is output:col1,col2----------------A,200B,400C,400Why GROUP BY statement sort my table automatically?I want to display my table as it is.Is it possible?Thank you very much for help.

Concat column values Posted: 14 Sep 2013 04:06 PM PDT I have following table[code="sql"]DepartmentID Employee------------- ---------1 John1 Michael2 Jenson2 Lily2 Lara[/code]I want the output in following format[code="sql"]DepartmentID Employees------------- ----------1 John,Michael2 Jenson,Lily,LaraPlease provide examples[/code]

Union overwrite null values Posted: 14 Sep 2013 04:00 AM PDT Is there a way using union to overwrite null values?for example:I hava two views with following columnsatt_date, student_id, AM_attendance code , PM_AttendanceCodethe union is:select '9/8/2013', 12345, 'EA', Nullunionselect '9/8/2013', 12345, Null, 'UA'I would like the result be one row instead of the two rows, the null values to be overwritten.'9/8/2013', 12345, 'EA', 'UA'

rebuild index for a table failed Posted: 14 Sep 2013 12:30 PM PDT We had found logical consistency errors in the database when we ran dbcc checkdb.The application vendor told us to rebuild an index for a particular table but we ended up with the following error.Any further way out:Executing the query "ALTER INDEX [PK__ERRORLOG] ON [dbo].[GSX_ERRORL..." failed with the following error: "SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x10001555). It occurred during a read of page (1:9381968) in database ID 7 at offset 0x000011e50a0000 in file 'P:\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\GSX_Prod_Data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can concurrent INSERTs mess up a UNION? Posted: 27 Jan 2013 04:13 PM PST Can SQL Server guarantee that if I issue a command like:[code="sql"]INSERT INTO T(...)SELECT t.a, t.b, 1FROM sometable tUNIONSELECT -t.a, t.b, 1FROM sometable t[/code], both "sides" of union will retrieve same table rows?

Send multiple HTML tables with sp_send_dbmail Posted: 29 Apr 2010 11:35 PM PDT How would someone send multiple html tables with sp_send_dbmail? In the @body paramater I can only send one table such as something like @tableHTML. I would like to be able to send more than one table in the email such as @tableHTML2 or something of the sort to display the extra table below the first one. Has anyone had experience in doing this and can advise?

Mapping inserted / source identity values Posted: 14 Sep 2013 05:16 AM PDT Hi,I have an interesting problem that i haven't been able to find a good solution for.What i want to do is to make an insert from table source into destination and find out the mapping between the source and destination table rows.The problem is that I cannot match the non-identity values on each other because they're no uniqueness between them. In real world, it's actually adress logging tables.Here are the sample script:[code="sql"]CREATE TABLE [source] (i INT identity PRIMARY KEY, some_value VARCHAR(30))CREATE TABLE [destination] (i INT identity PRIMARY KEY, some_value VARCHAR(30))CREATE TABLE [mapping] (i_old INT, i_new INT) -- i_old is source.i value, i_new is the inserted destination.i column-- Import some sample data...INSERT INTO [source] (some_value)SELECT TOP 30 nameFROM sysobjects-- Destination has some data alreadyINSERT INTO [destination] (some_value)SELECT TOP 30 nameFROM sysobjects--Here, i want to transfer everything from source into destination, but be able to keep a mapping on the two tables:-- I try to use OUTPUT clause, but i cannot refer to columns outside of the ones being inserted:INSERT INTO [destination] (some_value)--OUTPUT inserted.i, s.i INTO [mapping] (i_new, i_old) -- s.i doesn't work!!SELECT some_valueFROM [source] s[/code]I cannot change table definition, and i'd rather not mess with SET IDENTITY_INSERT ON either...Does someone have an idea?

Job Details Posted: 14 Sep 2013 12:38 AM PDT How to get job schedule details, I mean detailed schedule like, all the jobs on all 7 days. Sun Mon TueXXXXXX job - runs at 9 am no run runs at 9 am so what i require is list lal the jobs and its run/not run schedule for every 7 days. Does anyone have the query?

Optimization with multiple Select and Update statements Posted: 13 Sep 2013 11:38 PM PDT Hi,If I have multiple select and update statements in my stored procedure, will it be better to group up the select statements follow by the update statements like follows:[code="plain"]SELECT ...SELECT ...SELECT ...SELECT ...UPDATE ...UPDATE ...UPDATE ...UPDATE ...[/code]Or, will the performance be better if I will to group the SELECT and UPDATE as a pair like follows:[code="plain"]SELECT ...UPDATE ...SELECT ...UPDATE ...SELECT ...UPDATE ...SELECT ...UPDATE ...[/code]

How to find a table Posted: 13 Sep 2013 07:17 PM PDT I want to find the table name where there is a column 'WS_ID' .Can you please tell me how do find this table name ?

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[how to] How to update a table by count from another table?

---

• How to update a table by count from another table?
• Multiple values for unique id?
• Database with 3 columns or 20 columns?
• How do I query a table with many trees of data, for one tree?
• Is it possible to disable replication for a specific database in RDS?
• show innodb engine status tuning db server
• Restore Huge Mysql database file
• Getting distinct list of records based on the MAX of a column
• SQL server 2008 enterprise RESEED to 0 anormally
• database table schema help reservation to items to products
• How to optimize this query more?
• How to Restore Emails from Exchange Server 2007 [on hold]
• Drop an index in postgresql
• Table redirect / filter / trigger on select
• Modeling a database for easy counting / reporting
• Putting a Select statement in a transaction
• Replication randomly started failing
• Problem compiling view when it is referencing a table in an other view: insufficient privileges
• CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies)
• Connect Apache Http server with Oracle
• representation in ms-access
• PostgreSQL: DBLink weird permission/connection error
• How to setup SQL active/active cluster to achieve Blue / Green instance switching?
• Time series data for ad platform
• If an account has REQUIRE SUBJECT, does it still need a password?
• Install MariaDB without password prompt in ubuntu
• Converting dbo file from windows to linux
• How to change page size in an existing sybase database
• MySQL : Why are there "test" entries in mysql.db?

How to update a table by count from another table? Posted: 14 Sep 2013 07:55 PM PDT I have two tables as CREATE TABLE country_statistics ( id int(11) NOT NULL AUTO_INCREMENT us int(11), uk int(11), ca int(11), PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CREATE TABLE entries ( id int(11) NOT NULL AUTO_INCREMENT list varchar(255), PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT I want to search for the occurrence of each country in the list column. I use multiple subqueries to do so UPDATE country_statistics us = (SELECT COUNT(*) FROM entries WHERE list LIKE '%United States%', uk = (SELECT COUNT(*) FROM entries WHERE list LIKE '%United Kingdom%', ca = (SELECT COUNT(*) FROM entries WHERE list LIKE '%Canada%' WHERE id = 'xx' How can I reduce the number of subqueries, and count all values in one subquery, then updating all columns?

Multiple values for unique id? Posted: 14 Sep 2013 08:08 PM PDT I have two tables: customer and boxes in one-to-many relationship. The structure of the tables is in addition : Customer table : id#| name | date_purchased | address | 1 John 2012-9-10 N/A Boxes table: id#| type | quantity | 1 big 4 The id of the boxes table is foreign key to the customer table. What if John has purchased more than 4 big boxes ? For example: he has ordered 2small boxes as well. How to add this to the database design schema?

Database with 3 columns or 20 columns? Posted: 14 Sep 2013 05:49 PM PDT I have a database containing client informations. Every client is described by 20 fields A1, ... A20 containing a numeric value. Currently, the database has the following structure client name | field | value, where every client is described by 20 entries in the database, (one for each field A1...A20). This doesn't seem to be optimal. Ideally, I should reshape it in the following way client name | A1 | ... | A20. However, there is a problem : with the time, some fields A1...A20 will become obsolete and I will add new fields. Hence, in 3 years I may end up with 40 columns instead of 20. Is it still a wise idea to reshape the database with (at least now) 20 columns ?

How do I query a table with many trees of data, for one tree? Posted: 14 Sep 2013 03:16 PM PDT I have a the NAICS schema loaded and all is working well. However I'm confused as how I'd query it in Postgrseql? The table looks something like this, =========NAICS_2012========= naics | naics_parent | title ---------------------------- This is what I've wrote, WITH RECURSIVE search_graph(naics12, naics12_parent, title, depth, path_info) AS ( SELECT naics12, naics12_parent, title, 1, array[naics12] FROM naics.codes WHERE naics12_parent IS NULL UNION ALL SELECT c.naics12, c.naics12_parent, c.title, sg.depth+1, sg.path_info||c.naics12 FROM naics.codes AS c, search_graph AS sg WHERE c.naics12_parent = sg.naics12 ) SELECT * FROM search_graph ORDER BY path_info; This produces output like this (be sure to scroll to the end) naics12 | naics12_parent | title | depth | path_info ---------+----------------+------------------------------------------------------------------------------------------------------------------------+-------+---------------------------- 11 | | Agriculture, Forestry, Fishing and Hunting | 1 | {11} 111 | 11 | Crop Production | 2 | {11,111} 1111 | 111 | Oilseed and Grain Farming | 3 | {11,111,1111} 11111 | 1111 | Soybean Farming | 4 | {11,111,1111,11111} 111110 | 11111 | Soybean Farming | 5 | {11,111,1111,11111,111110} 11112 | 1111 | Oilseed (except Soybean) Farming | 4 | {11,111,1111,11112} 111120 | 11112 | Oilseed (except Soybean) Farming | 5 | {11,111,1111,11112,111120} 11113 | 1111 | Dry Pea and Bean Farming | 4 | {11,111,1111,11113} 111130 | 11113 | Dry Pea and Bean Farming | 5 | {11,111,1111,11113,111130} 11114 | 1111 | Wheat Farming | 4 | {11,111,1111,11114} 111140 | 11114 | Wheat Farming | 5 | {11,111,1111,11114,111140} 11115 | 1111 | Corn Farming | 4 | {11,111,1111,11115} 111150 | 11115 | Corn Farming | 5 | {11,111,1111,11115,111150} 11116 | 1111 | Rice Farming | 4 | {11,111,1111,11116} 111160 | 11116 | Rice Farming | 5 | {11,111,1111,11116,111160} 11119 | 1111 | Other Grain Farming | 4 | {11,111,1111,11119} 111191 | 11119 | Oilseed and Grain Combination Farming | 5 | {11,111,1111,11119,111191} 111199 | 11119 | All Other Grain Farming | 5 | {11,111,1111,11119,111199} 1112 | 111 | Vegetable and Melon Farming | 3 | {11,111,1112} 11121 | 1112 | Vegetable and Melon Farming | 4 | {11,111,1112,11121} 111211 | 11121 | Potato Farming | 5 | {11,111,1112,11121,111211} 111219 | 11121 | Other Vegetable (except Potato) and Melon Farming | 5 | {11,111,1112,11121,111219} 1113 | 111 | Fruit and Tree Nut Farming | 3 | {11,111,1113} 11131 | 1113 | Orange Groves | 4 | {11,111,1113,11131} 111310 | 11131 | Orange Groves | 5 | {11,111,1113,11131,111310} However, what I want is to see one tree, so I can write a query like, SELECT * FROM search_graph WHERE naics12 = <foo>; And, get a tableset back as if the table was only preloaded with the parents of the supplied NAICS12 code. Any idea on how on further manipulate the data to get this kind of result out?

Is it possible to disable replication for a specific database in RDS? Posted: 14 Sep 2013 10:48 AM PDT My application uses a mechanism of SQL patches as part of the upgrade to a new version. Before running an upgrade, I need to try to run the SQL patches on a copy of the database to ensure they complete without error, before running them on the actual db. I can do it on RDS, by creating a temporary DB, running the patches on it, and dropping it thereafter. But ideally, I'd want to do so without having the temporary database written to the binlog for backup and replication. Is it possible to mark a specific database as non-replicated in an RDS instance? My other option is to create a temporary RDS instance just for testing the upgrade, but that's a bit overkill and takes extra time to complete.

show innodb engine status tuning db server Posted: 14 Sep 2013 10:24 AM PDT I am new to dealing with the innodb engine status. I am not too sure with I/O section. As below is sample of my output. FILE I/O\n--------\n I/O thread 0 state: waiting for i/o request (insert buffer thread)\n I/O thread 1 state: waiting for i/o request (log thread)\n I/O thread 2 state: waiting for i/o request (read thread)\n I/O thread 3 state: waiting for i/o request (write thread)\n Pending normal aio reads: 0, aio writes: 0,\n ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0\n Pending flushes (fsync) log: 0; buffer pool: 0\n9705 OS file reads, 16082 OS file writes, 8524 OS fsyncs\n 0.00 reads/s, 0 avg bytes/read, 5.14 writes/s, 2.00 fsyncs/s\n-------------------------------------\n I also notice a number of TRANSACTION 0 6929906, not started, process no 1402, OS thread id 140010566186752\n MySQL thread id 28, query id 475745 192.168.100.11 user1\n--------\n Why is it having so many transaction not started? What else can I look from this show engine to tune my db server?

Restore Huge Mysql database file Posted: 14 Sep 2013 10:19 AM PDT i am trying to restore 18 G.B Mysql .sql database file in linux server what is the values should be in /etc/my.cnf in file so may increase the fast restoration

Getting distinct list of records based on the MAX of a column Posted: 14 Sep 2013 01:27 PM PDT I have a status table (tblTestActionStatus) which has three columns ID_TestAction: references some test action ID_Status: a look up table reference for the variety of possible statuses for the action StatusDateTime: a datetime field which logs the exact time a status update was made for the test action. So while a test action is happening, periodic status updates are made. I am interested to know the current status which would be the last status update of a test action as of the moment I run the query. For example, test action # 100 has gotten 4 status changes so far. The first was 1, then 2, then 3, and most recently 4. And test action # 101 has gotten 3 status updates so far. So I would like to write a query that returns for me all the columns in the table but for only the most recent StatusDateTimes. I attached a pic which shows the table contents and the rows I would like to see coming back from the query highlighted. I was able to find an answer with some more searching in the archive. http://stackoverflow.com/questions/1049702/create-a-sql-query-to-retrieve-most-recent-records So the SQL I created from that post which works in my case is as follows... SELECT tblTestActionStatus.ID_TestAction, StatusDateTime, ID_Status FROM tblTestActionStatus INNER JOIN ( Select MAX(StatusDateTime) as LatestDate, ID_TestAction FROM tblTestActionStatus Group By ID_TestAction ) SubMax on tblTestActionStatus.StatusDateTime = SubMax.LatestDate and tblTestActionStatus.ID_TestAction = SubMax.ID_TestAction

SQL server 2008 enterprise RESEED to 0 anormally Posted: 14 Sep 2013 09:29 AM PDT I am using EF 5.0, and sometimes no one can register on server by same error, PK PK_Invalid, and the PK is AUTO INCREMENT, this jumped 1000 to 10000 sometimes, but now this reseting to 0 and try subscribe a exists id. Why? How prevent? NOTE To fix I am using DBCC CHECKIDENT (User, reseed, 1080002).

database table schema help reservation to items to products Posted: 14 Sep 2013 09:23 AM PDT I'm trying to get a decent structure for a database for an app and want to ensure I'm using best practice and get the right relationships. I'm not sure if I have a one to many relationship or a many to many... I have a table 'reservations' that receives reservations. I have another table called 'Items' that stores products ordered that belong to the reservation and includes options, qty etc. So assumed the relationship between reservation and items is a one to many. I then have another table called Products which has specifics about the items available. I've assumed this has a one to one relationship with Items. Is this the best approach to use or are there better ways I should learn/study. Are my understanding of the relationships correct or is the 'items' table simply a link table for a many to many relationship between reservation and products advice on approach and best practice appreciated database is a mysql Thanks

How to optimize this query more? Posted: 14 Sep 2013 07:39 AM PDT First at all I am nood into SQL thing, Now I am working on a class project where I have some tables like Table user user_id | username | name 1 | nihan | Nihan Dip 2 | dip | Meaw ghew more | more | more Table Friend you | friend_id 1 | 2 1 | 27 2 | 9 more | more Table Follow user_id | follows 1 | 99 7 | 34 Table post post_id | user_id | type | content | post_time 1 | 1 | text | loren toren | timestamp 2 | 2 | text | ipsum | timestamp Now I want to get post by users friend and who he follows and offcourse his so I made this SQL SELECT username, name,content, post_time FROM post INNER JOIN user ON user.user_id = post.user_id WHERE post.user_id IN (SELECT friend_id FROM friend WHERE you = 1 union all SELECT follows FROM follow WHERE user_id = 1) OR post.user_id = 1 ORDER BY post_time DESC LIMIT 10 this query works just fine. I just wanted to know is there anymore optimization could be done? Then how?

How to Restore Emails from Exchange Server 2007 [on hold] Posted: 14 Sep 2013 07:42 AM PDT My Exchange server 2007 database get corrupt and now i am unable to read EDB files emails, Please suggest me solution. Thanks

Drop an index in postgresql Posted: 14 Sep 2013 07:33 PM PDT I had created an index wrongly and now im trying to drop that index. Since the datas are large for my table, dropping index is taking lot of time. Is there any other way to drop the index quickly? Thanks, Karthika

Table redirect / filter / trigger on select Posted: 14 Sep 2013 09:20 AM PDT Is there any way to redirect queries to different tables / views based on the package that's referencing the table? I.e. packages A and B both have "select grade from schema1.grd_tbl", but I want package A to get the percent grade that's stored in the table, and package B to get a letter grade that's calculated from the percent. I'd like to avoid modifying the (dozens of) packages that reference the table. I'd rather 'spoof' the table somehow if we can, replacing the percent in the grade column with a letter when called from those packages. (The column's varchar2; percents are coded as characters.) First thought was to create a synonym pointing to a view that massages the column based on the calling package, but the code fully qualifies the table name in most cases so that doesn't seem doable. I went looking for something equivalent to a trigger on select; the closest I've found is fine-grained audit, and it's not row-based. Is there magic somewhere that might let me do this? Any hints appreciated. Perry.

Modeling a database for easy counting / reporting Posted: 14 Sep 2013 08:20 PM PDT I have an app where user is known (user_id) and he can do several actions (action_id). Every time he makes an action I need to save the fact that he made it for reports/analytics. I guess it is similar to other analytic solutions and their db design. Once I have the data, provided with a time window (minutes resolution) I need to count for each user (all or some) the number of times he did actions and which actions he did. (sum all data grouped by action_id). Some assumptions: The number of users are ~1000. Action types are ~100. Actions can happen 24/7. The time windows can span from minutes to days and are random. A time window can't go back more than 30 days. I'm considering SQL, NoSQL and RRD to save the data. I put RRD here because it's easy to implement the insert of the data into statds+graphite. I'm concerned if I take this approach, the querying (although provided by graphite) will not be indexed and will probably have to count all the data whenever I ask for a window/user (no indexing). Another problem is that when querying all the data, all users info will be needed, resulting in reading all the files concurrently which I'm not sure is a good thing. SQL - Very easy implementation when inserting the data and querying. Easy to index, order and group by. However I'm not sure it's easy if I'm anticipating high traffic. Also, I'm not sure how effective is the count() of sql (haven't used SQL in the last few years) after group by. Can it offer parallel computation? NoSQL - Is there a solution out there that is the right fit for this type of scenario (perhaps a Map/Reduce algorithm to fast generation of counts in a time window?) Thanks for helping me model

Putting a Select statement in a transaction Posted: 14 Sep 2013 01:20 PM PDT What is the difference between these 2 queries: start transaction; select * From orders Where id=1; UPDATE orders SET username="John" Where id=1; commit; And without transaction: select * From orders Where id=1; UPDATE orders SET username="John" Where id=1; What is the effect of having a SELECT inside a transaction? If Delete From orders Where id=1 was called from another session right after the Select in both cases, when will it be processed?

Replication randomly started failing Posted: 14 Sep 2013 08:20 AM PDT I have replication set up between two servers. This morning I came in and it was failing. I was getting these errors: Error messages: The process could not execute 'sp_replcmds' on 'PSQL3'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help: Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517) The process could not execute 'sp_replcmds' on 'PSQL3'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

Problem compiling view when it is referencing a table in an other view: insufficient privileges Posted: 14 Sep 2013 11:20 AM PDT Oracle 11g R2 Logged on: SYS / AS SYSDBA When I try to compile or create a view that is referencing local schema tables. It works fine. Problem does occur when I try to compile the same view referencing a table in another schema like schema.table in my query. Oracle throws the exception ORA-01031: insufficient privileges. Remember I am using SYS account (sysdba).

CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies) Posted: 14 Sep 2013 05:20 AM PDT Below is my scenario and any help in this regard would be highly appreciated. I need to get the list of Managers and reporting persons to that manager with LEVEL at ID level. But the Fact table doesnt have hierarchial relationship with itself. Needs to get the id's from key's from 2 different dimension tables to generate the list. I am not familiar with CONNECT_BY and JOINS to get list. Fact table: Person_ky Mngr_ky 100 3 50 6 2000 9999 60 3 150 4 20 2 In DIM Person table Person_ky Pers_Id 100 x 50 y 2000 aaaa 60 a 150 m 20 q In DIM Manger table Mngr_ky Mngr_id 3 m 6 a 4 q 2 t 999 bbb In the above scenario, pers_ky 100 (Id -'x') is reporting to mngr_ky 3(Mngr_id - 'm') and person_ky 150 ('m') is reporting to mngr_ky 4 ('q') and person_ky 150 ('q') reporting to mngr_ky 2('t').. So Need to have for person x, his managers are 'm','q' and 't' in the result. pers_id mgr_id level x m 1 --> for pers_ky 100 x q 2 x t 3 y a 1 --> for per_ky 60 y m 2 y q 3 y t 4 aaaa bbb 1 --> for pers_ky 2000 a m 1 --> for pers_ky 60 a q 2 . .

Connect Apache Http server with Oracle Posted: 14 Sep 2013 06:20 AM PDT I have problem with connect ApacheHttpServer with Oracle. All work on OpenSuse 12.3 At first I install Oracle instal client 11.2. Set environment variables: ORACLE_HOME=/usr/local/instantclient_11_2/ LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH PATH=$ORACLE_HOME:$PATH TNS_ADMIN=$ORACLE_HOME/network/admin export ORACLE_HOME LD_LIBRARY_PATH PATH TNS_ADMIN After that I compile apache http : #apr ./configure --prefix=/usr/local/apache2/apr make make install #apr-util ./configure --prefix=/usr/local/apache2/apr-util --with-apr=/usr/local/apache2/apr/ make make install #httpd CPPFLAGS=-IORACLE_HOME/include LDFLAGS=-LORACLE_HOME ./configure --prefix=/usr/local/apache2/httpd --with-apr=/usr/local/apache2/apr/ --with-apr-util=/usr/local/apache2/apr-util/ make make install Unfortunately, the installation was not successful because mod_dbd_oracle.so didn't created. I think I was connected with that: #./configure output: checking oci.h usability... yes checking oci.h presence... yes checking for oci.h... yes checking for OCIEnvCreate in -lclntsh... no setting LIBS to "-lnnz11" checking for OCIEnvCreate in -lclntsh... no nulling LIBS setting LIBS to "-lnnz10" I could not find a way how to solve. So I decided to use Oracle with ODBC driver. I install unixODBC 2.2.12-219.1.1. After that I modife to odbc.ini and odbcinst.ini: #odbc.ini [xe] DRIVER = oraclevb DSN = xe ServerName = //192.168.56.101:1521/xe Application Attributes = T Attributes = W BatchAutocommitMode = IfAllSuccessful BindAsFLOAT = F CloseCursor = F DisableDPM = F DisableMTS = T EXECSchemaOpt = EXECSyntax = T Failover = T FailoverDelay = 10`enter code here` FailoverRetryCount = 10 FetchBufferSize = 64000 ForceWCHAR = F Lobs = T Longs = T MaxLargeData = 0 MetadataIdDefault = F QueryTimeout = T ResultSets = T SQLGetData extensions = F Translation DLL = Translation Option = 0 DisableRULEHint = T StatementCache=F CacheBufferSize=20 UseOCIDescribeAny=F #odbcinst.ini: [oraclevb] Description = Oracle ODBC Driver = /usr/local/instantclient_11_2/libsqora.so.11.1 Setup = FileUsage = CPTimeout = CPReuse = I chcek how it work with command: isql -v xe user passowrd All was OK. So I again compile Apache Http Source. Modify httpd.conf to use ODBC: LoadModule authn_dbd_module modules/mod_authn_dbd.so LoadModule authn_core_module modules/mod_authn_core.so LoadModule authz_host_module modules/mod_authz_host.so LoadModule authz_user_module modules/mod_authz_user.so LoadModule authz_dbd_module modules/mod_authz_dbd.so LoadModule authz_core_module modules/mod_authz_core.so LoadModule access_compat_module modules/mod_access_compat.so LoadModule auth_basic_module modules/mod_auth_basic.so LoadModule dbd_module modules/mod_dbd.so DBDriver odbc DBDParams "datasource=xe" <Directory "/sample"> AuthType Basic AuthName "private area" AuthBasicProvider dbd AuthDBDUserPWQuery "SELECT password FROM USERS WHERE name = %s" AuthzDBDQuery "SELECT group FROM GROUPS user_name = %s" Require dbd-group grup1 grup2 </Directory> Start Apache HTTTP Server and I found in error logs: [Sun Jul 14 15:19:07 2013] [dbd_odbc] SQLConnect returned SQL_ERROR (-1) at dbd/apr_dbd_odbc.c:1130 [unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient_11_2/libsqora.so.11.1' : file not found 01000 [Sun Jul 14 15:19:07.332394 2013] [dbd:error] [pid 13374:tid 140250220050240] (20014)Internal error: AH00629: Can't connect to odbc: [dbd_odbc] SQLConnect returned SQL_ERROR (-1) at dbd/apr_dbd_odbc.c:1130 [unixODBC][Driver Manager]Can't open lib '/usr/local/instantclient_11_2/libsqora.so.11.1' : file not found 01000 I don't know what I can do with that. Any suggestions would be very helpful. Maybe there is another way that conduct authorization and authentication using the Oracle database. Or other Apache module?

representation in ms-access Posted: 14 Sep 2013 07:20 PM PDT I have a database in microsoft access. I want to know how to look up a singular datum from a reference table giving a dynamic set of values. Here is a representation of what I mean: I have the following tables: Points for Pushups(m): Reps Age 17-21, Age 22-26, Age 27-31 1 6 7 8 2 7 9 9 3 9 11 12 Fitness Tests: Name Reps Test Date Bob 2 1 jan 2009 Jill 1 5 may 2010 People: Name DOB Bob 1 jan 1987 Jill 2 feb 1985 Sal 3 Mar 1991 I want the query to use People.DOB and the Test date to find the age the person was during the test. I then want the query to use this value to determine which column to look in, and the value from reps to determine which row to look in coming back with the singular value and naming it points. for example I want bob to show Query: Name DOB Age AtTest Reps Points Bob 1 Jan 1987 22 2 9 Does anyone know how to do the dynamic reference part? I know how to make the query and I know how to get age I just don't know how to use the values as columns in the reference table, I've seen it done, but long ago and never looked into it.

PostgreSQL: DBLink weird permission/connection error Posted: 14 Sep 2013 07:40 PM PDT The goal is to (1) create a connection, (2) return a view of another database, and (3) close the same connection all to be stored in a view (no stored procedure or embedded password). I came up with the following code, which uses a trust account and seems to work, until filtering on a boolean field. Searching for false (field=false) works, but searching for true (field=true) or IS NOT NULL prompts for the password. It's really odd that the data is being returned w/o needing the password, but when filtering, that is when the password is required. PostgreSQL: v8.4: There are two databases: db_remote and db_local There are two accounts: trusted_user (set up as trust in pg_hba.conf to both databases) md5_user (set up as md5 in pg_hba.conf to both databases) db_remote has a view (v_sessions), which contains two fields: a text field and a boolean field, which is created with a CASE statement. From within db_local (as md5_user): -- Creates okay: CREATE VIEW v_sessions AS SELECT * FROM ( select '1' query_type,'' as username, false as is_logged_in from dblink_connect_u('connection', 'host=development dbname=db_remote user=trusted_user') union select '2' query_type, username, is_logged_in from dblink('connection', 'select username, is_logged_in from v_sessions') as v_session(username text, is_logged_in boolean) union select '3' query_type,'',false from dblink_disconnect('connection') ) v_sessions WHERE query_type=2; -- Calling the view with filter: SELECT * FROM v_sessions WHERE is_logged_in; What works: removing the AND is_logged_in filtering on a text field AND username = 'some value' !! filtering on the boolean field for a false (AND is_logged_in = false) !! What doesn't work: filtering on the boolean field for a true value: AND is_logged_in IS NOT NULL, AND is_logged_in, or AND is_logged_in = true, gives the following error message: ERROR: password is required DETAIL: Non-superusers must provide a password in the connection string. Filtering inside the second query works, but it doesn't help when wanting to store this in a view. I suppose I'm looking for suggestions, but really trying to understand what is going on eg. why searching for false works and true does not - I first thought the connection was preemptively closing, but it's not.

How to setup SQL active/active cluster to achieve Blue / Green instance switching? Posted: 14 Sep 2013 05:20 PM PDT I am wondering if anyone has ever used a multi-instance cluster (nee 'Active/Active') to achieve blue/green (or A/B) deployment scenarios, and what the best way of configuring it is (using SQL 2012 / Windows 2008 R2)? To be specific, the scenario I want to achieve is to be able to switch between which cluster instance is being connected to by clients without either the clients or the SQL instances knowing (I stress I'm not talking about node failover here). I'm envisaging that the best way to achieve this is something like: Setup 2 node cluster, each of which has InstanceA and InstanceB instances Configure both InstanceA and InstanceB to listen as if they were the default instance on their cluster address (given each instance on a cluster has it's own unique IP) Use DNS to switch which virtual address clients actually connect to. This should hopefully enable me to do the following: Deploy database to instance A, and have clients connect to it via DNS alias as if default instance Deploy new version of database to instance B Vet new version of database (connecting explicitly to cluster\InstanceB) Redirect DNS alias to point to instance B's cluster name Clients now connect to InstanceB without realising anything's changed Both instances can still failover to the other node in a true outage Joining the dots, it seems like this should be possible: http://blogs.msdn.com/b/dbrowne/archive/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance.aspx http://stackoverflow.com/questions/9313358/connecting-to-a-multiple-instance-sql-server-without-specifying-instance ... but I've never seen a full example. Has anyone done it? Will what's proposed above work? What have I missed?

Time series data for ad platform Posted: 14 Sep 2013 12:20 PM PDT I am trying to figure out how to store time series data for an ad platform I am working on. Basically I want to know some strategies/solutions for storing billions of rows of data so that I can easily search it (about 6-8 indexes on the table) and get fast counts based on queries. I tried mySQL with the tokuDB engine and this seems to be very fast but is extremely slow when I try to do a COUNT query when the rows reached about 5-8 million. I was looking at some noSQL alternatives but since I want to be able to search this data this is probably not the best solution. I was using dynamoDB. I would have had to store the data is many places in order to account for all the searching on the data. What I am storing is a row in the database for each click on an AD that occurs. This table will grow very fast, especially when this site gets large. Another solution would be to separate this data per advertiser. This means each advertiser will have their own table where all their data goes into. This means it will be much smaller and the COUNT queries will be much faster. I can even split it up by advertiser and month. My goal is to give an advertiser the ability to search and display in a paginated way all their clicks. They should be able to get data between a time period and filter by about 5-8 other indexes if they want to.

If an account has REQUIRE SUBJECT, does it still need a password? Posted: 14 Sep 2013 06:20 PM PDT I'm in the process of setting up SSL-secured replication between two servers. Each server has its own public/private keypair, and the CA cert is just the concatenation of the two public certs, like this answer. Now I'm updating the replication account with REQUIRE SUBJECT "exact subject of the client" Is there any practical value to also having a password on the replication account (IDENTIFIED BY "secret")?

Install MariaDB without password prompt in ubuntu Posted: 14 Sep 2013 07:41 AM PDT I need to install mariadb server in ubuntu without passwod prompt. For this purpose, I execute the following commands, but it doesn't gave me any progress. It again shows password prompt. I used the following commands : export DEBIAN_FRONTEND=noninteractive echo mariadb-server-5.5 mariadb-server/root_password password mypass | debconf-set-selections echo mariadb-server-5.5 mariadb-server/root_password_again password mypass | debconf-set-selections sudo apt-get -y install mariadb-server I also check some links in stack overflow, but it doesnot worked: http://stackoverflow.com/questions/8138636/install-mysql-on-ubuntu-natty-without-password-prompt-and-using-shell-variable-a http://stackoverflow.com/questions/7739645/install-mysql-on-ubuntu-without-password-prompt Please help me on this regard. Have any wrong with my code?

Converting dbo file from windows to linux Posted: 14 Sep 2013 10:20 AM PDT I have a .dbo file which is created from windows. This file is succesfully reloaded into the mysql database in windows. I need to reload the dbo file into the mysql/mariadb database in linux. How I convert the file that was created from windows to linux?

How to change page size in an existing sybase database Posted: 14 Sep 2013 07:20 AM PDT I have an existing database with 1 kb page size i want to increase that to 16kb and increase cache to take advantage of my memory for best performance. sybase documentations said you can't change page size of database but you can create new empty database with preferred page size, so how can i move my data to from old db to new database with big page size ?

MySQL : Why are there "test" entries in mysql.db? Posted: 14 Sep 2013 05:05 PM PDT Recently, I posted an answer to a question about mysql.db. Then, I got to thinking I should ask everyone this question: I have noticed for years that upon installation of MySQL 5.0+, mysql.db is populated with two entries that allow test databases to be accessed by anonymous users. You can see it by running this query: mysql> select * from mysql.db where SUBSTR(db,1,4) = 'test'\G *************************** 1. row *************************** Host: % Db: test User: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: N Execute_priv: N *************************** 2. row *************************** Host: % Db: test\_% User: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: N Execute_priv: N 2 rows in set (0.00 sec) Are these entries in the mysql.db a security risk, and if so, why are they added by default to a new install? UPDATE 2013-06-14 10:13 EDT This morning someone downvoted my question, which I truly don't understand. In light of this event, here is why I took the time to make a rebuttal: I installed MySQL 5.6.12 for a client this week in their Staging Cluster. I decided to check to see if this was still an on-going problem: mysql> select version(); +------------+ | version() | +------------+ | 5.6.12-log | +------------+ 1 row in set (0.00 sec) mysql> select db,user,host from mysql.db where LEFT(db,4)='test'; +---------+------+------+ | db | user | host | +---------+------+------+ | test | | % | | test\_% | | % | +---------+------+------+ 2 rows in set (0.10 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2013-06-14 10:10:13 | +---------------------+ 1 row in set (0.00 sec) mysql> Guess what? This is still a problem even to this day !!! MORAL OF THE STORY : Please check your mysql.db immediately after installation and remove anonymous logins and erase these test entries from mysql.db without delay.

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[MS SQL Server] MAXDOP question

---

MAXDOP question Posted: 13 Sep 2013 06:13 AM PDT The computer that i run queries on has 2 processors. Shouldn't query1 execute faster than query2 ? Surprisingly, both executed taking exact time. Also, on OLTP system, should MAXDOP be disabled or should I leave it as default setting?Query1:SELECT * FROM tableUNIONSELECT * FROM table1Query2:SELECT * FROM tableUNIONSELECT * FROM table1OPTION (MAXDOP 1)Query1 was using parallelism in the execution plan.

Job failed - [298] SQLServer Error: 15404 Posted: 20 Mar 2012 07:01 PM PDT [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'domain\domain user', error code 0xffff0002. [SQLSTATE 42000] (ConnIsLoginSysAdmin)This is the error log. Please help me if you have the same issue.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL 2012] Encrypting a Coulmn in SQL Server 2012

---

• Encrypting a Coulmn in SQL Server 2012
• checking things after database restore
• Rounding a number up to the nearest 5
• Using FTP task in SIS 2012
• SQL 2012 SAN lvl Replication for DR - Issues and Thoughts

Encrypting a Coulmn in SQL Server 2012 Posted: 14 Sep 2013 12:20 AM PDT Hi,Need help on how I could encrypt a field (For Example a Password Field) in a Table in SQL Server.Table contains about 50 rows of data which includes username and password only.Thanks in advance.

checking things after database restore Posted: 13 Sep 2013 07:04 AM PDT Hi,I have restored a backup from server A to server B.After restoring I found that broker enabled and trustworthy was set to true on server A and not on server B.I manually set them to true.I also checked CLR.Are there any additional things that we need check that will not be done with restoring?Thanks.

Rounding a number up to the nearest 5 Posted: 13 Sep 2013 07:08 AM PDT So I am attempting to mimic some legacy code on an old server at my work. It has a calculation to determine cost. What I need to do is round the number up to the nearest 5th.For example:1.0 -> 5 (everything less than 5 gets rounded to give)5-> 55.75->58->1011->1586.5->90so any number that has a 5 in it before the decimal - will stay rounded to 5. Example - 75.65 -> 75. I have the following code:declare @mon numeric(10,2)set @mon = 86.5 select case when (round(@mon/5,0)*5) < 5 then 5 else (round(@mon/5,0)*5) endHowever - when I run it - 86.5 gets rounded to 85. I need it to round to 90.Any suggestions?

Using FTP task in SIS 2012 Posted: 13 Sep 2013 08:00 AM PDT I have an SIS package in 2012 that I'm trying to setup the FTP connection to pull a file from an FTP server.I can setup the FTP connection and test with no problem.I go to the FTP task and set up everything to receive filesI have the local Path set as to the folder it is supposed to go toOperation is to Receive filesClick on the Remote Path and I can see the file I want to pull down and I can select itSaveWhen I try to execute the task though I get the below errorI have an identical package written in SIS 2008 that is giving the same error and using the same steps outlined above.Am I missing something.Thanks in advance FTP Task Error:Unable to connect to FTP server using FTP Connection Manager

SQL 2012 SAN lvl Replication for DR - Issues and Thoughts Posted: 13 Sep 2013 06:52 AM PDT I will try to break up the picture as much as i can so i could get some feedback / thoughts from experts. [color=#000000][b][i]---------- Current Setup ----------[/i][/b][/color]I will just talk about Test for now1) 2 Node cluster2) 2 instances (08r2 & 2012)3) Binaries / data (not talking abt mdfs lfds) files are not on SAN. they are local. [color=#0000FF][b][i]---------- Trying to Achieve ----------[/i][/b][/color]1) set up a DR to a different location2) Since step 2 (see above) cannot be replicated via SAN we decided to replicate the Mdfs, Ldfs, Temp (data/log)3) Install a new sql 2 cluster node and installed only SQL 2012 instance (it can support 08/r2/2012)5) Successfully brought all databases online in testing. [color=#FF0000][b][i]---------- Questions I have ----------[/i][/b][/color]1) Are there any Pros & Cons that have been faced when having SQL binaries / data on SAN and using SAN lvl replication only to bring SQL up and running on Dr site?2) Would there be a need to involve any other HA process with SAN replication?3) How hard ([i]will this require a new installation?[/i]) will it be to re-do test (2 node cluster) / qa (2 node cluster) / prod(4 node cluster) and get binaries / data on SAN. I would appreciate if i can be pointed in the correct direction or if the experts have any thoughts, exp to share. Something to kick start.[b][i][u]Noli Timere[/u][/i][/b]

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] return results where value exists in another table

---

• return results where value exists in another table
• Problem with update syntax
• Finding gaps within date ranges
• use results of view in where statement
• Extract string between to characters

return results where value exists in another table Posted: 13 Sep 2013 10:54 PM PDT Hi, I would like to get results from the two tables below where the ITM$Con_Note value is in both tables.I would also like to calculate the WeightDif field which is the difference between the two weights.Also in reality these tables are identical tables in separate identical databases on two PC's using SQL Express 2008 R2Thanks,DavidITM$Con_Note ITM$Machine ITM$Date_Time ITM$Weight ITM$Machine ITM$Date_Time ITM$Weight WeightDifABC456 ADL01 2013-09-14 20:52:39.087 1.200 ADL02 2013-09-14 20:52:39.090 1.500 0.31234567890 ADL01 2013-09-14 20:52:39.087 35.6 ADL02 2013-09-14 20:52:39.090 35.75 0.25drop table #item1drop table #itemCREATE TABLE #Item( [ITM$Con_Note] [varchar](50) NULL, [ITM$Machine] [varchar](10) NULL, [ITM$Date_Time] [datetime] NULL, [ITM$Weight] [numeric](18, 3) NULL, )Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC123','ADL01',getdate(),10.5)Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC456','ADL01',getdate(),1.2)Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC789','ADL01',getdate(),4.5)Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('1234567890','ADL01',getdate(),35.6)CREATE TABLE #Item1( [ITM$Con_Note] [varchar](50) NULL, [ITM$Machine] [varchar](10) NULL, [ITM$Date_Time] [datetime] NULL, [ITM$Weight] [numeric](18, 3) NULL, )Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC1231','ADL02',getdate(),10.75)Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC456','ADL02',getdate(),1.5)Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC7890','ADL02',getdate(),4.55)Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('1234567890','ADL02',getdate(),35.75)select * from #Item AS oneLEFT OUTER JOIN #Item1 AS twoON one.ITM$Con_Note = two.ITM$Con_NoteWHERE one.ITM$Con_Note = two.ITM$Con_Note Topic Next Topic

Problem with update syntax Posted: 13 Sep 2013 07:36 AM PDT Hi all - I am preparing create tables with test data, but I thought this might be so simple and generic that it might not require them. I'm probably missing somthing simple here.After a table load I'm trying to set one column's data using the first char of another column. This is what I tried, but it seems to be updating the entire column to the same value - [code="other"]UPDATE DIM.DocControlProfile SET DocType = SUBSTRING(STG.CurrDocNumber,1,1)FROM STG.Staging AS STGINNER JOIN DIM.DocControlProfile AS DCON STG.DocContProfileID = DC.DocControlID[/code]Any generic syntax advice is appreciated but if needed I will finish the create table and add data script.Thanks in advance.

Finding gaps within date ranges Posted: 13 Sep 2013 02:00 AM PDT Hi, I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset. Example dataset SQL below:[code="sql"]CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME) INSERT INTO #test SELECT 1, '20130428', '20130523' UNION SELECT 1, '20130526', '20130823' UNION SELECT 1, '20130728', '20130728' UNION SELECT 1, '20130826', '20130830' UNION SELECT 2, '20130501', '20130515' UNION SELECT 2, '20130525', '20130830' select * from #test [/code]I would expect a dataset to be returned consisting of: 1, 24/05/2013, 25/05/20131, 24/08/2013, 25/08/20132, 16/05/2013, 24/05/2013Does anyone have a good way of doing this? I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns... Thanks!

use results of view in where statement Posted: 13 Sep 2013 07:28 AM PDT I have a sql server 2008 stored procedure where I want to use the results of a view in a where statement. I want to check to see if the results from the view is not null.The sql looks like the following currently:select top 5 from cust_table where view value is not null.Would you show me the t-sql that I would need to use to see if the results from the view is null?

Extract string between to characters Posted: 13 Sep 2013 03:09 AM PDT I've a string in following format:<1|458|test|q><2|789|test2|s><5|567|test3|p>First I've to extract the strings between < and >like: 1|458|test|q 2|789|test2|s 5|567|test3|pafter that I've to split the the pipes: 1 458 test q 2789 test2 s 5 567 test3 pThe occurrences of <> is not set to a specific number. I want to present the result in one row in different columns.I've tried with different functions (charindex, instring, substring) but no success the way I need the data.Any suggestions? Thanks in advance!

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610