September 23, 2015 at 10:26 am
Dear Experts,
I am running below TSQL from SSIS package and its failing but runs successfully when ran from SSMS.
Can anyone please help me to figure out the problem.
IF(select count(*) from msdb..backupset) > 0
BEGIN
select @@servername as Instance,b.database_name as Database_name, ma:@b.backup_finish_date) as Backup_Date
from msdb..backupset as b with (nolock)
join master.[sys].[databases] as d with (nolock)
on (b.database_name = d.name)
group by b.database_name, b.[type], d.recovery_model_desc order by b.database_name
END
ELSE
BEGIN
select @@servername as Instance, 'ALL' as Database_name, '2000-01-01' as Backup_Date
END
Hresult: 0x80004005 Description: "The metadata could not be determined because the statement 'select @@servername as Instance, 'ALL' as Database_name, '2000-01-01' as Backup_Date' is not compatible with the statement 'select @@servername as Instance,
b.database_name as Database_name, ma:@b.backup_finish_date) as Bac'.".
September 23, 2015 at 10:40 am
Try by casting the literals in your second select statement. Another good idea is to use EXISTS for your condition.
IF EXISTS(select 1 from msdb..backupset)
BEGIN
select @@servername as Instance,
b.database_name as Database_name,
MAX( b.backup_finish_date) as Backup_Date
from msdb..backupset as b with (nolock)
join master.[sys].[databases] as d with (nolock)
on (b.database_name = d.name)
group by b.database_name, b.[type], d.recovery_model_desc order by b.database_name
END
ELSE
BEGIN
select @@servername as Instance, CAST( 'ALL' AS nvarchar(256)) as Database_name, CAST( '2000-01-01' AS datetime) as Backup_Date
END
September 23, 2015 at 10:57 am
Kummu (9/23/2015)
Dear Experts,I am running below TSQL from SSIS package and its failing but runs successfully when ran from SSMS.
Can anyone please help me to figure out the problem.
IF(select count(*) from msdb..backupset) > 0
BEGIN
select @@servername as Instance,b.database_name as Database_name, ma:@b.backup_finish_date) as Backup_Date
from msdb..backupset as b with (nolock)
join master.[sys].[databases] as d with (nolock)
on (b.database_name = d.name)
group by b.database_name, b.[type], d.recovery_model_desc order by b.database_name
END
ELSE
BEGIN
select @@servername as Instance, 'ALL' as Database_name, '2000-01-01' as Backup_Date
END
Hresult: 0x80004005 Description: "The metadata could not be determined because the statement 'select @@servername as Instance, 'ALL' as Database_name, '2000-01-01' as Backup_Date' is not compatible with the statement 'select @@servername as Instance,
b.database_name as Database_name, ma:@b.backup_finish_date) as Bac'.".
There is no such thing as implicit type cast in SSIS, you must explicitly cast the return values in one part of the statement to match the datatypes in the other. When the packet is build, the "active" part of the statement is used to construct the data flow pipeline (check describe_first_resultset). If the inactive part has different datatypes then the packet/task will error. All object names (sysname) are of type NVARCHAR(128), probably changing ,'ALL' as Database_name to ,N'ALL' as Database_name will do the trick but still the Backup_Date could also be a problem.
😎
Quick thought, why are you using the NOLOCK hint in the select? Do you expect backup sets and databases to be added hundred/thousand times per second while you are running this code? Blanket application of dirty read hints is not a good idea and in general (this is almost the only generalization I'll ever make) hints should only be used to address a specific problem when all other means of resolving the problems are exhausted.
September 23, 2015 at 7:06 pm
No luck, its failing with the same error. After changing to cast and using exists
September 23, 2015 at 10:23 pm
Kummu (9/23/2015)
No luck, its failing with the same error. After changing to cast and using exists
Quick though, can you post your code, there is a type in your initial code, a redundant closing parenthesis.
😎
ma:@b.backup_finish_date ) as Backup_Date
September 24, 2015 at 2:47 am
Here is the final query
IF ((select count(*) from msdb..backupset) > 0)
BEGIN
select @@servername as Instance,
b.database_name as Database_name, max(b.backup_finish_date) as Backup_Date,
Backup_Type =
CASE b.[type]
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'FileDiff'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'PartialDiff'
ELSE 'NotValid'
END,
d.recovery_model_desc as Recovery_Model,
'SQL' as DB_Type,
'PROD' as DB_ENV
from msdb..backupset as b with (nolock)
join master.[sys].[databases] as d with (nolock)
on (b.database_name = d.name)
group by b.database_name, b.[type], d.recovery_model_desc order by b.database_name
END
else
begin
select @@servername as instance,CAST('ALL' AS nvarchar(128)) as Database_name,CAST('2000-01-01' AS datetime) as Backup_Date,cast ('F' as char(1)) as Backup_Type,
CAST( 'ALL' AS nvarchar(60)) as Recovery_Model,'SQL' as DB_Type,'PROD' as DB_ENV
end
September 24, 2015 at 6:47 am
Here's a different idea. Remove the IF
SELECT @@servername AS Instance,
b.database_name AS Database_name,
MAX(b.backup_finish_date) AS Backup_Date,
CASE b.type
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'FileDiff'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'PartialDiff'
ELSE 'NotValid'
END AS Backup_Type,
d.recovery_model_desc AS Recovery_Model--,
--Remove this from the query, assign them to variables
--'SQL' AS DB_Type,
--'PROD' AS DB_ENV
FROM msdb..backupset AS b
JOIN master.sys.databases AS d ON b.database_name = d.name
GROUP BY b.database_name,
b.type,
d.recovery_model_desc
UNION ALL
SELECT @@servername AS instance,
'ALL' AS Database_name,
'2000-01-01' AS Backup_Date,
'F' AS Backup_Type,
'ALL' AS Recovery_Model--,
--Remove this from the query, assign them to variables
--'SQL' AS DB_Type,
--'PROD' AS DB_ENV
WHERE NOT EXISTS(
SELECT 1
FROM msdb..backupset )
ORDER BY Database_name;
Even better would be to create a view and use it as your source.
September 24, 2015 at 8:57 am
Thanks,I have it as work around but could you please help me with what is the problem with my query have been trying to figure it out.
Thanks
Kowsalya
September 24, 2015 at 9:04 am
The problem is that DB_Type and DB_ENV don't have a data type assigned. Every column in your queries needs a data type defined.
September 24, 2015 at 6:24 pm
Is this something new in SQL 2014? This was running fine from very long time on all older versions of SQL even on 2012.
Thanks
Kowsalya
September 24, 2015 at 6:33 pm
I tried cast('SQL' as nvarchar(5)) as DB_Type and cast('PROD' as nvarchar(5)) as DB_Env in both IF & ELSE clause but no luck.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply