November 14, 2013 at 10:04 am
I NEED YOUR HELP IN THIS. I AM NOT ABLE TO FIGURE IT OUT WHY I AM GETTING THIS ERROR. WHEN I RUN THIS PROCEDURE IN QUERY WINDOWS IT WORKS FINE NO ERRORS BUT WHEN IT RUNS BY SQL SERVER AGENT IT FAILED TO COUPLE OF STEPS ... CAN IT BE A AUTHORIZATION PROBLEM AS WELL AS I AM TRYING TO GET THE DATA FROM DIFFERENT SERVERS AND TWO OF THEM GIVING ME "ERROR 1" AND LAST SERVER GIVING ME "ERROR 2" MENTIONED BELOW...
Error 1. Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000] (Error 536). The step failed.
Error 2. Conversion failed when converting datetime from character string. [SQLSTATE 22007] (Error 241). The step failed.
ALTER proc [dbo].[usp_MU_Format2]
as
--Create temp table with PK ID Field, and CSV field.
CREATE TABLE #t
(
P_ID int PRIMARY KEY IDENTITY,
RPTBORES_CSV VARCHAR(MAX) NOT NULL
)
--Insert CSV field into temp table.
INSERT INTO #t
SELECT RPTBORES_CSV
FROM DBO.MU
WHERE RPTBORES_CSV LIKE ('%#%')
AND RPTBORES_CSV NOT LIKE ('%CQM%')
--Declare variables.
declare @n varchar(8000)
declare @id int
declare @1 int
declare @2 int
declare @eof numeric
declare @Report varchar(1000)
declare @period varchar(1000)
declare @loc varchar(1000)
declare @v1 varchar(1000)
declare @v2 varchar(1000)
declare @v3 varchar(1000)
declare @Prov varchar(1000)
declare @S varchar(30)
declare @E varchar(30)
declare @measure varchar(30)
declare @name varchar (100)
DECLARE @Tmp DATETIME
set @id = (select max(p_id) from #t)
while @id >= (select min(p_id) from #t)
begin
set @n = (select [rptbores_csv] from #t where p_id = @id)
delete from #t where p_id = @id
set @id = @id - 1
IF CHARINDEX('NUMERATOR B', @n) != 0
BEGIN
set @eof = len(@n)
set @Report = substring(@n,2,(select charindex('Reporting',@n)-5))
set @measure = substring(@report,charindex('-',@report,1)+2,8)
set @name = substring(@report,1,charindex('-',@report,1)-2)
set @1 = 1
set @1 = charindex('"',@n,@1+1)
set @1 = charindex('"',@n,@1+1)
set @2 = @1+1
set @1 = charindex('"',@n,@1+1)
set @Period = substring(@n,@2,@1-@2)
set @S = substring(@period,18,11)
set @E = substring(@period,32,11)
set @1 = charindex('"LOCATION"',@n,@1)
while @1 < @eof - 1
begin
set @1 = charindex('"',@n,@1) +1
set @2 = charindex('"',@n,@1+1)
SET @1 = CHARINDEX('"',@n,@2+1) +1
SET @2 = CHARINDEX('"',@n,@1+1)
set @prov = SUBSTRING(@n, @1, @2-@1)
set @1 = charindex('"',@n,@2+1) +1
set @2 = charindex('"',@n,@1+1)
set @v1 = substring(@n,@1,@2-@1)
set @1 = charindex('"',@n,@2+1) +1
set @2 = charindex('"',@n,@1+1)
--Skips den. B.
set @1 = charindex('"',@n,@2+1) +1
set @2 = charindex('"',@n,@1+1)
set @v2 = substring(@n,@1,@2-@1)
set @1 = charindex('"',@n,@2+1) +1
set @2 = charindex('"',@n,@1+1)
set @v3 = substring(@n,@1,@2-@1)
SET @1 = CHARINDEX('"CAREGIVER"',@n,@1)
IF @1 = 0 SET @1 = @eof + 1
INSERT INTO dbo.MU_New
SELECT @n as data,@report as Report,@period as Period,@loc as Location
, @prov as Provider,@v1 as Numerator,@v2 as Denominator,@v3 as [Percent]
, @S as Start, @e as [End] ,@measure as measure, @name as [name]
, left(@measure,4) as measure2
, RIGHT(REPLACE(REPLACE(RTRIM(@measure),'#','0'),' ','0'),2) AS [NUMBER]
END
END
ELSE
BEGIN
set @eof = len(@n)
set @Report = substring(@n,2,(select charindex('Reporting',@n)-5))
set @measure = substring(@report,charindex('-',@report,1)+2,8)
set @name = substring(@report,1,charindex('-',@report,1)-2)
set @1 = 1
set @1 = charindex('"',@n,@1+1)
set @1 = charindex('"',@n,@1+1)
set @2 = @1+1
set @1 = charindex('"',@n,@1+1)
set @Period = substring(@n,@2,@1-@2)
set @S = substring(@period,18,11)
set @E = substring(@period,32,11)
set @1 = charindex('"percentage"',@n,@1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
while @1 < @eof - 1
begin
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @prov = substring(@n,@2+1,@1-@2-1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @v1 = substring(@n,@2+1,@1-@2-1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @v2 = substring(@n,@2+1,@1-@2-1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @2 = @1
set @1 = charindex('"',@n,@1+1)
set @v3 = substring(@n,@2+1,@1-@2-1)
INSERT INTO dbo.MU_New
SELECT @n as data,@report as Report,@period as Period,@loc as Location
, @prov as Provider,@v1 as Numerator,@v2 as Denominator,@v3 as [Percent]
, @S as Start, @e as [End] ,@measure as measure, @name as [name]
, left(@measure,4) as measure2
, RIGHT(REPLACE(REPLACE(RTRIM(@measure),'#','0'),' ','0'),2) AS [NUMBER]
END
END
END
/*******************************************************************/
UPDATE DBO.MU_New
set Name = substring(Provider, 0, charindex(',',Provider))
WHERE Provider LIKE '%,%'
/*******************************************************************/
--Create #mu table by joining mu_measures with MU_new
SELECT '00000000' as docID, ' ' as [Status],*
INTO #mu
FROM dbo.mu_measures M1 inner join dbo.MU_new M2
ON m1.[type] = m2.measure2 and m1.[order] = m2.[number]
ORDER BY PROVIDER
--Load new Doctors into MU_tbl_Doctors
insert into dbo.MU_tbl_Doctors
select distinct null as period,null as start,null as [end],#mu.provider as doctor,1 as link, #mu.name as name2, NULL as [use]
from #mu left join mu_tbl_doctors D
on D.doctor = #mu.provider and #mu.name = D.name2
where d.name2 is null
--Update DocID in #mu table.
update #mu
set #mu.docid = D.id
from #mu left join mu_tbl_doctors D
on D.doctor = #mu.provider and #mu.name = D.name2
--Update QMeasures in #mu table.
UPDATE #mu
SET #mu.qmeasure = m1.qmeasure
FROM dbo.mu_measures M1 inner join #mu M2
ON m1.[type] = m2.measure2 and m1.[order] = m2.[number]
--Create #Max table from the max start and end of each DocID in #mu.
select max([start]) as PStart, max([END]) as PEnd, Docid
into #Max
from #mu
group by docid
--Update start and end dates in MU_tbl_Doctors from #max.
Update dbo.MU_tbl_Doctors
Set [start] = convert(varchar,#max.[pstart],101)
, [end] = convert(varchar,#max.[pend],101)
from dbo.MU_tbl_Doctors D inner join #max
on d.id = #max.docid
--Update #mu table.
Insert into #mu
select d.id,
'',
M.[type],
M.[order],
M.id,
M.threshhold,
M.expr,
M.note,
M.link,
M.qmeasure,
M.exclude,
M.attestationpass,
'',
'',
d.period,
'',
d.doctor,0,0,0,d.start,d.[end],m.[type]+' #'+m.[order],d.name2,m.[type],m.[order]
from dbo.MU_tbl_Doctors D inner join dbo.MU_Measures M
on M.link = D.link inner join #MU
on #MU.provider = D.doctor
--Update Percentage
update #mu
Set [Percent] = convert(numeric,numerator)/convert(numeric,denominator)
where denominator >0
update #mu
Set [Percent] = 0
where denominator = 0
--Update Status
update #mu
Set [Status] = Case
when >= 'a'
Then
else (case
when [percent] > [Threshhold]
Then 'PASS'
else 'FAIL'
end)
end
--Trim Measures
Update #mu
Set [measure] = [type]+' #'+[number]+' - '+qmeasure
--Update attestation1 -Exclude
Update #MU
Set [Status] = 'Excluded'
from dbo.mu_measures M1 inner join #mu M2
on m1.[type] = m2.measure2 and m1.[order] = m2.[number]
where denominator < M1.exclude
--Update attestation2 - Attestation - Yes
Update #MU
Set [Status] = 'Attestation - Yes'
from dbo.mu_measures M1 inner join #mu M2
on m1.[type] = m2.measure2 and m1.[order] = m2.[number]
where M1.AttestationPass = 'Y'
--Format Start and End Dates
UPDATE #MU
SET Start = CONVERT(DATETIME,START,110)
, [End] = CONVERT(DATETIME,[END],110)
--Create #Max2
select max([start]) as PStart, max([END]) as PEnd, Docid
into #Max2
from #mu
group by docid
--Max_Period
select #mu.docid, max(#mu.period) as period
into #p
from #mu inner join
#max2 on #mu.docid = #max2.docid and #mu.[end] = #max2.pend
group by #mu.docid
--Update End
Update #mu
Set #mu.[end] = #max2.pend
from #mu inner join
#max2 on #mu.docid = #max2.docid
--Update Period
Update #mu
Set period = #p.period
from #mu inner join
#p on #mu.docid = #p.docid
--Update No Data
Update #mu
Set Numerator = null, Denominator = null, [Percent] = null, threshhold = null
from dbo.mu_measures M1 inner join
#mu M2 on m1.[type] = m2.measure2 and m1.[order] = m2.[number]
where M1.link = 1
--ExportMU
Insert into ExportMU
Select [Use], docid as [id],measure,#mu.period,#mu.start
,#mu.[end],[percent] as Percentage,doctor,Numerator,Denominator
,#mu.number as Order2,[Type],Qmeasure,Threshhold,Expr,Note,[Status],docid,name2
from #mu inner join mu_tbl_doctors D
on D.start = #mu.start and D.[END] = #mu.[end] and D.id = #mu.docid
where d.[use] is null
Insert Into dbo.MU_RPT2
Select distinct [Measure]
,#mu.[Period]
,#mu.[Start]
,#mu.[End]
,[Percent] as [Percentage]
,[Doctor]
,[Numerator]
,[Denominator]
,#mu.number as [Order2]
,[Type]
,[Qmeasure]
,''
,[Threshhold]
,[Expr]
,[Note]
,[Status]
,[DocID]
,[Name2]
from #mu inner join mu_tbl_doctors D
on D.start = #mu.start and D.[END] = #mu.[end] and D.id = #mu.docid
where d.[use] is null
I NEED YOUR HELP IN THIS. I AM NOT ABLE TO FIGURE IT OUT WHY I AM GETTING THIS ERROR. WHEN I RUN THIS PROCEDURE IN QUERY WINDOWS IT WORKS FINE NO ERRORS BUT WHEN IT RUNS BY SQL SERVER AGENT IT FAILED TO COUPLE OF STEPS ... CAN IT BE A AUTHORIZATION PROBLEM AS WELL AS I AM TRYING TO GET THE DATA FROM DIFFERENT SERVERS AND TWO OF THEM GIVING ME "ERROR 1" AND LAST SERVER GIVING ME "ERROR 2" MENTIONED ABOVE...
November 14, 2013 at 10:35 am
You've posted as single stored procedure but you refer to SQL Server Agent failing in a couple of steps. Please clarify.
What are the exact steps being called in your SQL Server job? Do you call the sproc or are the single steps of the proc executed as separate job steps?
As a side note: There's plenty of room to tune the sproc and get rid of the c.u.r.s.o.r *cough*. But that's a different story...
November 14, 2013 at 10:55 am
yes its a single stored procedure.
well when i execute this procedure through query window it didnt give me error but when i run it through agent it gives error.
In sql server agent job this procedure runs 69 steps and every step is separate, getting different information from each server as it define in the agent job steps.
FYI:i didn't wrote this procedure, i just joined the company and this is first assignment.
November 14, 2013 at 11:25 am
fawad.majid (11/14/2013)
...In sql server agent job this procedure runs 69 steps and every step is separate,
Do you have a job step similar to EXECUTE [dbo].[usp_MU_Format2] or are there separate job steps with single commands in it?
If the latter: what would be the output of a job step calling the sproc itself and not a few code snippets?
Can you post the job definition (including all job steps)?
November 14, 2013 at 11:36 am
yes they are all similar steps in this job.
here is the steps. ( so 69 steps have the same code )
Insert into dbo.MU_New_bak select * from dbo.MU_New
go
Truncate table dbo.MU_New
go
Truncate table dbo.MU
go
Insert Into dbo.MU SELECT [coloumn_name], [column_name2] From [server].[schema_name].[HPSITE].[RPTBO_RESULT]
GO
exec usp_MU_Format2
go
even the step failed the procedure move to next step. this error is on step 48 and 66. Both these steps getting information from two different servers.
November 14, 2013 at 12:29 pm
Did you try to run the excat code on the server in question?
My guess would be the code will also fai if run from the query window connected to the server that faild.
The error message indicate theres something wrong with the SUBSTRING function.
Looking at the code there's a good chance you're dealing with data that do not contain the string "Reporting" (or any other character used in your various CHARINDEX() functions)
Example:
set @Report = substring(@n,2,(select charindex('Reporting',@n)-5))
For @n ='test' you'd receive a similar error message since charindex('Reporting',@n) returns 0 and substring(@n,2,-5) is not supported.
You might need to change your WHERE clause of the initial query to populate the temp table to check for all search strings being present that are used in substring(charindex()-x).
November 14, 2013 at 12:51 pm
ok Let me do that and ill get back to you
Thank you
November 15, 2013 at 10:17 am
LutzM (11/14/2013)
Did you try to run the excat code on the server in question?My guess would be the code will also fai if run from the query window connected to the server that faild.
The error message indicate theres something wrong with the SUBSTRING function.
Looking at the code there's a good chance you're dealing with data that do not contain the string "Reporting" (or any other character used in your various CHARINDEX() functions)
Example:
set @Report = substring(@n,2,(select charindex('Reporting',@n)-5))
For @n ='test' you'd receive a similar error message since charindex('Reporting',@n) returns 0 and substring(@n,2,-5) is not supported.
You might need to change your WHERE clause of the initial query to populate the temp table to check for all search strings being present that are used in substring(charindex()-x).
Lutz is right about your error being caused by functions returning values that are not positive integers as the <length> argument of the SUBSTRING function. There are so many instances of the SUBSTRING function being called like this that it is impossible to know which one is causing the error. I suggest you add a TRY . . . CATCH block to your code and in the CATCH block, write the values of ALL the variables to a log table before you return the error message. Then you can look at the values and see which might be causing the problems.
Another question, though - are you by chance using this code to parse delimited text data? If so, there are MUCH easier and more reliable ways to do that and folks around here would be glad to help you get started.
Jason Wolfkill
November 15, 2013 at 11:49 am
You need to check set options for your session in SSMS. They may be different than the SQL Agent's set options.
November 15, 2013 at 12:21 pm
Hi Jason..
I haven't check the issue with try...catch block.. ill do that ...
Let me add one more thing in this... This procedure/script run through the 69 steps and from 69 it getting the same information from 68 database servers, in which every table is identical to each other in all 68 steps.. i don't get it why it only have a problem with 3 servers (error mentioned above) have the problem, if it was a null problem that the return value is null then i can use Nullif function for that but everything in every database server is same. so why only these 3 servers have the problem. could it be the authorization issue to ??? not sure but just wanna ask that could it be ...
Let me know and i will try to work with TRY ...CATCH thing
Thank you
November 15, 2013 at 12:44 pm
fawad.majid (11/15/2013)
Hi Jason..I haven't check the issue with try...catch block.. ill do that ...
Let me add one more thing in this... This procedure/script run through the 69 steps and from 69 it getting the same information from 68 database servers, in which every table is identical to each other in all 68 steps.. i don't get it why it only have a problem with 3 servers (error mentioned above) have the problem, if it was a null problem that the return value is null then i can use Nullif function for that but everything in every database server is same. so why only these 3 servers have the problem. could it be the authorization issue to ??? not sure but just wanna ask that could it be ...
Let me know and i will try to work with TRY ...CATCH thing
Thank you
No, the error messages you are getting come from the execution engine, so permissions don't seem to be the problem - SQL Server is trying to run the code but just can't complete the process because of the errors.
These errors arise from the actual data values SQL Server is trying to process. While the schema may be the same on all 68 servers, surely the data must be different? The databases that encounter the errors must have data that cause errors in the expressions serving as the arguments to the SUBSTRING function and string values that can't be converted to datetime values. That's why catching the actual data values that cause the errors will be helpful.
One easy way to manage this is to create a single table with columns for whatever identifying information you need (run datetime, server name, whatever) to figure out where and when the errors occurred and an xml column to hold the variable values. Then, in the CATCH block, you do something like this:
CATCH
INSERT INTO myLogTable (runDatetime, serverName, instanceName, databaseName, variablesXML)
SELECT GETDATE(),
SERVERPROPERTY('MachineName'),
SERVERPROPERTY('InstanceName'),
DB_Name(DB_ID()),
(SELECT @var1 AS var1,
@var2 AS var2,
@var3 AS var3
FOR XML PATH ('Variables'), TYPE)
END CATCH
Then, when you discover that the proc failed, you can go to this table and see exactly what values it was using when it encountered the errors.
Jason Wolfkill
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply