Transact SQL :: Getting Error When Trying To Union All Between Two Databases?
Nov 13, 2015
I am having an issue when trying to union all between two databases.
This is the error:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
I have updated the collation in both DB to Latin1_General_CI_AS and I am still getting the error when running the query.
The queries run separately ok. why I am still getting the collation error.
View 11 Replies
ADVERTISEMENT
Jun 10, 2008
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.
View 2 Replies
View Related
Oct 17, 2007
What is the standard way to union tables with exactly the same schema that are in different databases?
For example:
Code Block
USE db1 SELECT * FROM table1
UNION
USE db2 SELECT * FROM table2
this will return the following error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'use'.
View 3 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Aug 20, 2015
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
create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)
[Code] ....
View 3 Replies
View Related
May 19, 2015
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 ....
COL 1 COL2 COL3 COL4 etc. BEH BIG BPL etc.
************************************* INTAKT DEFEKT INTAKT DEFEKT INTAKT
************************************* B E B E B
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?
View 4 Replies
View Related
Jul 21, 2015
I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.
In my first SELECT statement, I have INSERT INTO @tblData.
Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?
DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table
[Code] ....
View 3 Replies
View Related
Nov 9, 2015
I'd like to find out who is accessing various databases and log the time they did so.
Is there a way to do this? I just need the account name and the time logged and then to write to a table so I can query it.
View 2 Replies
View Related
Sep 26, 2015
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
[code]....
View 7 Replies
View Related
Feb 25, 2007
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)
'||||| Add Parameters and set values.
odbcCommand_date.Parameters.Add("@P1", OdbcType.VarChar).Value = label_emp_id.Text.ToString
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = YearFr
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = MonthFr
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayFrom
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayFrom
odbcCommand_date.Parameters.Add("@P1", OdbcType.VarChar).Value = label_emp_id.Text.ToString
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = YearTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = MonthTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayTo
The error is in the UNION part of the query...
PLs help me.
Thanks!
Sheila
View 1 Replies
View Related
Feb 26, 2008
Error: The text data type cannot be selected as DISTINCT because it is not comparable.
SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp,
a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate,b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.EmployeeID) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND
(a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2',
'yyyVP1', 'yyyVP2', 'yyyVP3'))
union
SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp, a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate, b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.CustAtr) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND (a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2', 'yyyVP1', 'yyyVP2', 'yyyVP3'))
View 11 Replies
View Related
Feb 29, 2008
Error: The text data type cannot be selected as DISTINCT because it is not comparable.
I tried union all it returns duplicates records.
SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp,
a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate,b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.EmployeeID) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND
(a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2',
'yyyVP1', 'yyyVP2', 'yyyVP3'))
union
SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp, a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate, b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.CustAtr) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND (a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2', 'yyyVP1', 'yyyVP2', 'yyyVP3'))
View 1 Replies
View Related
Jun 12, 2014
SQL Server 2008 r2...
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]
[Code] .....
View 4 Replies
View Related
May 27, 2009
I would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?
View 21 Replies
View Related
May 20, 2015
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,
[Code] ....
How I could combine the results?
View 9 Replies
View Related
Aug 12, 2015
I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.
I'm looking for added columns in dbase 1.
Is there a system T-SQL script that can be used for this?
View 4 Replies
View Related
Aug 20, 2015
I need a script to kill existing sessions on [db1] and [db2] except sessions from [user1]. I have a script like this now:
USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('db1)
EXEC(@kill);
I just need to extend to run on db2 and don't kill sessions from user1
View 3 Replies
View Related
Nov 4, 2015
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?
View 13 Replies
View Related
Jun 1, 2006
Hello,
I want to order by a field that is a code, like 1,2,3,11,12,13, and i get the following error
"Only text pointers are allowed in work tables, never text, ntext, or image columns sql server"
Why do i get this, what can i do to overcome this?
Thank you
View 7 Replies
View Related
Mar 26, 2007
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.
Thanks for the look!
View 4 Replies
View Related
Jun 5, 2015
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
View 8 Replies
View Related
Sep 27, 2015
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.
View 3 Replies
View Related
Jul 23, 2013
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:
Use DatabaseName
GO
SELECT members.name MemberName, roles.name RoleName,roles.type_desc,members.type_desc
View 10 Replies
View Related
Aug 30, 2007
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.
please, help.
thanks in advance.
View 4 Replies
View Related
Jul 10, 2014
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)
);
----------------
Referenced View Columns:
"SEG_VIEW_PARENTS" ("SEG_NAME", "CODE", "NAME")
Referenced Table Columns:"UIP_SEGMENT_VALUES"
"SEG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_DESC" VARCHAR2(200 BYTE),
"SEG_TYPE" VARCHAR2(20 BYTE),
"SEG_COMPANY" VARCHAR2(20 BYTE)
View 1 Replies
View Related
Oct 14, 2015
I am familiar with the sp_MSForEachDBand the USE Parameter I did Google and found [URL]
View 3 Replies
View Related
Aug 27, 2015
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.
View 3 Replies
View Related
May 14, 2008
Has anyone else experienced similar problems? I've been trying to use "Row number" from sqlis.com site with "Union All" but no lucks.
[DTS.Pipeline] Error: component "Union All" (1840) failed the pre-execute phase and returned error code 0x80070057.
thanks
View 6 Replies
View Related
Apr 29, 2008
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
View 3 Replies
View Related
Jul 20, 2007
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:
View 2 Replies
View Related
Mar 7, 2006
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
View 1 Replies
View Related
Jul 24, 2015
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:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Nov 6, 2006
Hi all,
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.
Any idea why this could happen ?
View 18 Replies
View Related
