We had a divestiture within our company. Now what used to be contained in one database in now split into two databases. One showing all history and one being all current data as of 6/1/2008. Is there an easy way to Union or Join these? Right now I'm currently doing a simple UNION ALL, but can't group the two select statements:
SELECT Year,Location,QtySold FROM historydb
UNION ALL
SELECT Year,Location,QtySold FROM currentdb
Can't do a subset and group both of these selects. How would some of you pro's do this? Right now I can put this in a simple view and then create a SP off of this view that would do this grouping, but it seems like I should be able to do it all in one query. Thanks.
I attempted to create a view in SQL Server 2000 that Unions twoqueries. The first part of the query gets data from the local server,the second part gets info from a linked server. (The query works finein Query Analyzer.)I received this error when I tried to save the query:ODBC error: [Microsoft][ODBC SQL Server Driver] The operation couldnot be performed because the OLE DB provider 'SQLOLEDB' was unable tobegin a distributed transaction.[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB providerreturned message: New transaction cannot enlist in the specifiedtransaction coordinator.]After a little reading I discovered the "Database limitation":"A view can be created on a table only in the database the viewcreator is accessing".That's my problem... is there a simple solution or alternative tocreating a view?Thanks,Matt
I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code. Each block is the same except for the date. How could I rewrite these 30 union statements into a more concise query?
Each client (people_id) has a distinct agency_id_number. So the code below totals the number of distinct clients/day and groups them by programs and facilities.
Sample data that you can use to test this report:
IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census
I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....
I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?
After cloning our production SQL 2012 server to make DEV environment, there is a bunch of databases in "Suspect" mode. This is because the DB's and Logs have got out of sync.I can clean up each DB with this script:
ALTER DATABASE DB_NAME SET EMERGENCY GO DBCC CHECKDB (DB_NAME) GO ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS) GO ALTER DATABASE DB_NAME SET MULTI_USER GO
but this takes a long time replacing DB_NAME and executing for each DB.I tried putting it all into a script with variables, but I have done something wrong and it does not work. Also is there a way to just apply it to the suspect DB's?
USE master Go DECLARE @dbname sysname, @cmd varchar(1000), @cmd1 varchar(1000), @cmd2 varchar(1000), @cmd3 varchar(1000), @cmd4 varchar(1000) DECLARE db_recovery_cursor CURSOR FOR SELECT name from sysdatabases OPEN db_recovery_cursor
hi! What do you think is the error in this sql squery? What I'm trying to do is to get all the records having dates from 2007/01/21 to 2007/02/20 where YearFr =2007 MonthFr =01 DayFrom =21 YearTo =2007 MonthTo =02 DayTo =20 By the way, this is part of my code: '||||| Open connection MyConn.Open()
'||||| Create Command Object for filtering time ins and time outs base from Year and Month Dim odbcCommand_date As OdbcCommand = New OdbcCommand("Select TT0001.Year, TT0001.Month,TT0001.Day, TT0001.in_hh, TT0001.in_mi,TT0001.out_hh,TT0001.out_mi,TM1001.kbn_name from TT0001 inner join TM1001 on TT0001.kintai_status = TM1001.kbn where TT0001.syain_id =? AND TT0001.Year = ? and TT0001.Month = ? and TT0001.Day = ? or TT0001.Day > ? ORDER BY TT0001.Year,TT0001.Month,TT0001.Day UNION Select TT0001.Year, TT0001.Month,TT0001.Day, TT0001.in_hh, TT0001.in_mi,TT0001.out_hh,TT0001.out_mi,TM1001.kbn_name from TT0001 inner join TM1001 on TT0001.kintai_status = TM1001.kbn where TT0001.syain_id =? AND TT0001.Year = ? and TT0001.Month = ? and TT0001.Day = ? or TT0001.Day < ? ORDER BY TT0001.Year,TT0001.Month,TT0001.Day", MyConn)
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.
(SELECT DISTINCT idname, name, address, address2, awardedtoname, suppno FROM contract INNER JOIN house ON contract.idname = house.idname) JOIN (SELECT DISTINCT tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Union All" (1100) failed the pre-execute phase and returned error code 0x80070057.
I have a flat file source, OLE DB Source, and a Script component, all with output columns defined and loaded. The flat file source has a connection manager defined, as does the OLE DB Source. The Script is just a counter.
I'd like to put the value from each, into a row, then into an OLE DB Destination table. It seems harder than it appears.
Script only displays the result for 'Master' database.
What changes are required for script to display result for all databases on the instance?
SELECT DB_Name() AS DatabaseName, OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count >1000 ORDER BY indexstats.avg_fragmentation_in_percent DESC
I have 5 databases that the user will chose which ones to restore. I was thinking the variable with the 5 database names separated by commas. I was thinking about using the CONTAIN function but two of the databases have the same name except for a few letters at the end.
I want to get all databases with it's owner Name ,I think I need to join following script result with sys.databases or sys.sysdatabases.I want a result set like this:
DatabaseName db_Owner
master sa pubs valia pubs eli pubs maryam AdentureWork eli
this script get all owners of the database that is in use:
when i try to run this following query, i get an error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
The ntext data type cannot be selected as DISTINCT because it is not comparable.
/resultados_termo.asp, line 176
the query:
select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade like '%fiat%' or ano like '%fiat%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade like '%brava%' or ano like '%brava%' ) union select * from view_veiculos where ativo='1' and ( nome_marc like '%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%' or ano like '%2004%' )
when i use UNION ALL, i get repeated rows. i need the select distinct on it.
I’m receiving the following message when attempting to run the SQL statement below.
Error report: SQL Command: force view "UIP_SOC"."SEG_VIEW_EWO_2" Failed: Warning: execution completed with warning
----------------------- CREATE OR REPLACE FORCE VIEW "UIP_SOC"."SEG_VIEW_EWO_2" ("CODE", "NAME", "EWO4", "EWO6") AS SELECT DISTINCT code, name FROM ( SELECT seg_value AS code, seg_desc AS name, SUBSTR(seg_value,5,4) AS EWO4, SUBSTR(seg_value,5,6) AS EWO6 FROM UIP_SEGMENT_VALUES WHERE seg_name = 'EWO' AND seg_value IN (SELECT ewo FROM stage_budget_v) UNION SELECT CODE,NAME FROM SEG_VIEW_PARENTS WHERE SEG_NAME = 'EWO' AND NOT (CODE IS NULL) ); ----------------
I am doing some administrative tasks and need to collect some principals information from multiple instances and user databases.
I have table "dbo.instances" with list of instances. I have databases from "sys.databases".
How can I execute the query to get principals information from "sys.database_principals" on each remote instance and database. I know that can use cursor, but not sure how to do this with multiple servers and databases.
Here is an example of what I am exactly doing!! CREATE VIEW NamesAS SELECT ID, fName, lName FROM Table1 UNION ALL SELECT id, FirstName, LastName FROM TABLE2. On Executing this...I'm getting the following error.
The Query Designer does not support the UNION SQL construct:
Hi all,I am getting this error when insert values from one table to another inthe first table the values are varchar (10). In the second they aredatetime. The format of the data is mm/dd/yyyy to be easily convertedto dates. The conversion in this case is implicit as indicated in SQLServer documentation. Here is my query:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionUNIONSELECT program_id + 100000, program_name, start_date, end_date,campaign_id AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.programsUNIONSELECT execution_id + 200000, execution_name, start_date,end_date, program_id + 100000 AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.executionsUNIONSELECT wave_id + 300000, wave_name, start_date, end_date,mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL ASListIDFROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executionsWHERE mysqlfactiva.dbo.waves.execution_id =mysqlfactiva.dbo.executions.execution_idI am referencing programs table two times. If I just select this all Iget all data I need. When doing insert I get a message:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. The statement has been terminated.If I execute just first part of the query before first union, I insertdata fine:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionAs soon as I use union I get the above error. This is very strangesince even when I execute the query using first union where the datescome from the same table 'programs' I get the error. Why I can insertfrom programs first time and can's second time?Any help will be appreciated.Thanks,Stan
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
