This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(156.8, 45%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EO%3C/text%3E%3C/svg%3E)
Hello All,
I Have a SSRS report that pulls data from and embedded query in a dataset. The query receives a date parameter and runs okay in SSMS.
The query refreshes okay in and generates the fields. However when I click preview report it displays all columns with string datatype but not field with integer datatype.
My query script in the dataset contains a temp table and the integer column comes from the temp table in the dataset. I have deleted the rdl data files but no change.
Please advice.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(25.6, 73%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
We're going to need to see the details. Please provide the query you're getting the data with, a sample of the data being returned and the designer code you're using to render the data.
Hello Michael,
Please find below the code.
IF (1=0)
BEGIN
SELECT
'Procedurename' as [ProcedureName],
'RowCNT' as [RowCnt],
'MissingProc' as [MissingProc],
'MissingTbl' as [MissingTbl]
END
IF OBJECT_ID('tempdb..#tblchk') IS NOT NULL DROP TABLE #tblchk SELECT DISTINCT s.procedurename, s.RowCNT
INTO #tblchk
FROM GlossMetadata.LoadSPStatus s JOIN
(SELECT DISTINCT ProcedureName, min(s.CreateDT) maxCreateDT, MAX(s.BusinessDate) maxBusinessdate
/*INTO #TblChk */ FROM GlossMetadata.LoadSPStatus s
WHERE (ProcedureName IN
(SELECT DISTINCT SPECIFIC_CATALOG + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME
FROM cmdw.information_schema.routines
WHERE routine_type = 'PROCEDURE' AND SPECIFIC_SCHEMA = 'Transform' AND SPECIFIC_NAME LIKE '%Loadgloss%') OR
s.ProcedureName = 'SpLoadGlossFactTrade') AND BusinessDate = @COB
GROUP BY s.ProcedureName) ss ON s.ProcedureName = s.ProcedureName AND s.CreateDT = ss.maxCreateDT AND
s.BusinessDate = ss.maxBusinessdate
UPDATE #tblchk
SET RowCNT =
(SELECT COUNT(*)
FROM cmdw.gloss.DimTrade
WHERE DWInsertDT >= @COB)
WHERE ProcedureName = 'CMDW.Transform.SpLoadGlossDimTrade'
UPDATE #tblchk
SET ProcedureName = 'CMDW.Transform.SpLoadGlossFactTrade'
WHERE ProcedureName = 'SpLoadGlossFactTrade' BEGIN WITH cte AS
(SELECT DISTINCT SPECIFIC_CATALOG + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME AS procedurename
FROM cmdw.information_schema.routines
WHERE routine_type = 'PROCEDURE' AND SPECIFIC_SCHEMA = 'Transform' AND SPECIFIC_NAME LIKE '%Loadgloss%')
SELECT CASE WHEN (c.ProcedureName IS NOT NULL AND t .ProcedureName IS NULL)
/*OR (t.ProcedureName IS NOT null AND t.RowCNT=0)*/ THEN REPLACE(c.procedurename,
'CMDW.Transform.SpLoadGloss', '') + ' is not loaded' ELSE '' END MissingProc, REPLACE(c.procedurename,
'CMDW.Transform.SpLoadGloss', '') MissingTbl, t .RowCNT, c.ProcedureName
FROM cte c LEFT JOIN
#TblChk t ON t .ProcedureName = c.ProcedureName
WHERE c.procedurename NOT IN ('CMDW.Transform.SpLoadGlossTrade', 'CMDW.Transform.SpLoadGlossTradeExternalRef',
'CMDW.Transform.SpLoadGlossPrsimClientStartDate', 'CMDW.Transform.SpLoadGlossPartyApprovedDepartment',
Hi @Olutimi,
It seems there is something wrong with the code you comment, please have a double check, i can only see the code as below.
IF (1=0)
BEGIN
SELECT 'Procedurename' as [ProcedureName], 'RowCNT' as [RowCnt], 'MissingProc' as [MissingProc], 'MissingTbl' as [MissingTbl]
END
IF OBJECT_ID('tempdb..#tblchk') IS NOT NULL DROP TABLE #tblchk SELECT DISTINCT s.procedurename, s.RowCNT
Please look to the right.
SELECT
'Procedurename' as [ProcedureName],
'RowCNT' as [RowCnt],
'MissingProc' as [MissingProc],
'MissingTbl' as [MissingTbl]
END
IF OBJECT_ID('tempdb..#tblchk') IS NOT NULL DROP TABLE #tblchk SELECT DISTINCT s.procedurename, s.RowCNT
Copy
INTO #tblchk
FROM GlossMetadata.LoadSPStatus s JOIN
(SELECT DISTINCT ProcedureName, min(s.CreateDT) maxCreateDT, MAX(s.BusinessDate) maxBusinessdate
/*INTO #TblChk */ FROM GlossMetadata.LoadSPStatus s
WHERE (ProcedureName IN
(SELECT DISTINCT SPECIFIC_CATALOG + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME
FROM cmdw.information_schema.routines
WHERE routine_type = 'PROCEDURE' AND SPECIFIC_SCHEMA = 'Transform' AND SPECIFIC_NAME LIKE '%Loadgloss%') OR
s.ProcedureName = 'SpLoadGlossFactTrade') AND BusinessDate = @COB
GROUP BY s.ProcedureName) ss ON s.ProcedureName = s.ProcedureName AND s.CreateDT = ss.maxCreateDT AND
s.BusinessDate = ss.maxBusinessdate
UPDATE #tblchk
SET RowCNT =
(SELECT COUNT(*)
FROM cmdw.gloss.DimTrade
WHERE DWInsertDT >= @COB)
WHERE ProcedureName = 'CMDW.Transform.SpLoadGlossDimTrade'
UPDATE #tblchk
SET ProcedureName = 'CMDW.Transform.SpLoadGlossFactTrade'
WHERE ProcedureName = 'SpLoadGlossFactTrade' BEGIN WITH cte AS
(SELECT DISTINCT SPECIFIC_CATALOG + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME AS procedurename
FROM cmdw.information_schema.routines
WHERE routine_type = 'PROCEDURE' AND SPECIFIC_SCHEMA = 'Transform' AND SPECIFIC_NAME LIKE '%Loadgloss%')
SELECT CASE WHEN (c.ProcedureName IS NOT NULL AND t .ProcedureName IS NULL)
/*OR (t.ProcedureName IS NOT null AND t.RowCNT=0)*/ THEN REPLACE(c.procedurename,
'CMDW.Transform.SpLoadGloss', '') + ' is not loaded' ELSE '' END MissingProc, REPLACE(c.procedurename,
'CMDW.Transform.SpLoadGloss', '') MissingTbl, t .RowCNT, c.ProcedureName
FROM cte c LEFT JOIN
#TblChk t ON t .ProcedureName = c.ProcedureName
WHERE c.procedurename NOT IN ('CMDW.Transform.SpLoadGlossTrade', 'CMDW.Transform.SpLoadGlossTradeExternalRef',
'CMDW.Transform.SpLoadGlossPrsimClientStartDate', 'CMDW.Transform.SpLoadGlossPartyApprovedDepartment',
BEGIN
SELECT
'Procedurename' as [ProcedureName],
'RowCNT' as [RowCnt],
'MissingProc' as [MissingProc],
'MissingTbl' as [MissingTbl]
END
IF OBJECT_ID('tempdb..#tblchk') IS NOT NULL DROP TABLE #tblchk
SELECT DISTINCT s.procedurename,s.RowCNT INTO #tblchk FROM GlossMetadata.LoadSPStatus s JOIN(
SELECT distinct ProcedureName,min(s.CreateDT)maxCreateDT, MAX(s.BusinessDate)maxBusinessdate --INTO #TblChk
FROM GlossMetadata.LoadSPStatus s WHERE (ProcedureName IN
(
SELECT DISTINCT SPECIFIC_CATALOG+'.'+SPECIFIC_SCHEMA+'.'+SPECIFIC_NAME
from cmdw.information_schema.routines
where routine_type = 'PROCEDURE'
AND SPECIFIC_SCHEMA='Transform'
AND SPECIFIC_NAME LIKE '%Loadgloss%'
)OR s.ProcedureName='SpLoadGlossFactTrade' )
AND BusinessDate=@COB GROUP BY s.ProcedureName) ss ON
s.ProcedureName=s.ProcedureName AND s.CreateDT=ss.maxCreateDT AND s.BusinessDate=ss.maxBusinessdate
UPDATE #tblchk SET RowCNT=
(SELECT COUNT(*) FROM cmdw.gloss.DimTrade WHERE DWInsertDT>=@COB)
WHERE ProcedureName='CMDW.Transform.SpLoadGlossDimTrade'
UPDATE #tblchk SET ProcedureName='CMDW.Transform.SpLoadGlossFactTrade'
WHERE ProcedureName='SpLoadGlossFactTrade'
BEGIN
WITH cte as(
SELECT DISTINCT SPECIFIC_CATALOG+'.'+SPECIFIC_SCHEMA+'.'+SPECIFIC_NAME AS procedurename
from cmdw.information_schema.routines
where routine_type = 'PROCEDURE'
AND SPECIFIC_SCHEMA='Transform'
AND SPECIFIC_NAME LIKE '%Loadgloss%'
)
SELECT
CASE WHEN (c.ProcedureName IS NOT null AND t.ProcedureName IS null) --OR (t.ProcedureName IS NOT null AND t.RowCNT=0)
THEN REPLACE(c.procedurename,'CMDW.Transform.SpLoadGloss','')+' is not loaded' ELSE '' end
MissingProc, REPLACE(c.procedurename,'CMDW.Transform.SpLoadGloss','')MissingTbl,t.RowCNT , c.ProcedureName
FROM cte c LEFT JOIN #TblChk t ON t.ProcedureName=c.ProcedureName
WHERE c.procedurename NOT IN
(
'CMDW.Transform.SpLoadGlossTrade',
'CMDW.Transform.SpLoadGlossTradeExternalRef',
'CMDW.Transform.SpLoadGlossPrsimClientStartDate',
'CMDW.Transform.SpLoadGlossPartyApprovedDepartment',
'CMDW.Transform.SpLoadGlossTradePartyRef'
)
END
Hi @Olutimi,
Could you please copy the code and paste it into a text file and then upload to Q&A?
If possible, please also post CREATE TABLE statements for your tables together with INSERT statements with sample data.
Script attached.
Script attached.
IF (1=0)
BEGIN
SELECT
'Procedurename' as [ProcedureName],
'RowCNT' as [RowCnt],
'MissingProc' as [MissingProc],
'MissingTbl' as [MissingTbl]
END
IF OBJECT_ID('tempdb..#tblchk') IS NOT NULL DROP TABLE #tblchk
SELECT DISTINCT s.procedurename, s.RowCNT
INTO #tblchk
FROM GlossMetadata.LoadSPStatus s
JOIN (SELECT DISTINCT ProcedureName, min(s.CreateDT) maxCreateDT, MAX(s.BusinessDate) maxBusinessdate
FROM GlossMetadata.LoadSPStatus s
WHERE (ProcedureName IN
(SELECT DISTINCT SPECIFIC_CATALOG + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME
FROM cmdw.information_schema.routines
WHERE routine_type = 'PROCEDURE' AND SPECIFIC_SCHEMA = 'Transform' AND SPECIFIC_NAME LIKE '%Loadgloss%') OR
s.ProcedureName = 'SpLoadGlossFactTrade')
AND BusinessDate = @COB
GROUP BY s.ProcedureName) ss
ON s.ProcedureName = s.ProcedureName AND s.CreateDT = ss.maxCreateDT AND
s.BusinessDate = ss.maxBusinessdate
UPDATE #tblchk
SET RowCNT = (SELECT COUNT(*) FROM cmdw.gloss.DimTrade WHERE DWInsertDT >= @COB)
WHERE ProcedureName = 'CMDW.Transform.SpLoadGlossDimTrade'
UPDATE #tblchk
SET ProcedureName = 'CMDW.Transform.SpLoadGlossFactTrade'
WHERE ProcedureName = 'SpLoadGlossFactTrade'
BEGIN
WITH cte AS
(SELECT DISTINCT SPECIFIC_CATALOG + '.' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME AS procedurename
FROM cmdw.information_schema.routines
WHERE routine_type = 'PROCEDURE' AND SPECIFIC_SCHEMA = 'Transform' AND SPECIFIC_NAME LIKE '%Loadgloss%')
SELECT CASE WHEN (c.ProcedureName IS NOT NULL AND t .ProcedureName IS NULL)
THEN REPLACE(c.procedurename,'CMDW.Transform.SpLoadGloss', '') + ' is not loaded' ELSE '' END MissingProc,
REPLACE(c.procedurename,'CMDW.Transform.SpLoadGloss', '') MissingTbl,
t .RowCNT,
c.ProcedureName
FROM cte c LEFT JOIN #TblChk t
ON t .ProcedureName = c.ProcedureName
WHERE c.procedurename NOT IN ('CMDW.Transform.SpLoadGlossTrade', 'CMDW.Transform.SpLoadGlossTradeExternalRef',
'CMDW.Transform.SpLoadGlossPrsimClientStartDate', 'CMDW.Transform.SpLoadGlossPartyApprovedDepartment',
'CMDW.Transform.SpLoadGlossTradePartyRef')
END