How to create a temp log in a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to create a temp log in a query

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-17 : 11:34:23

HI I have a situation where I would like to know where the sp is failing. I am not getting anywhere with the profiler tools or the existing log. it only tells me 'units not supplied' this is good but i need exactly what is the problem.

How can i create some log in the sp?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 11:43:47

Can you post

the sample execution of the call that's failing

The Actual Error message (cut and paste)

And the actual Stored procedure code

Thanks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-17 : 13:32:16

Put


PRINT 'At step 1'
...
PRINT 'At step 2'
...

in the code to see where it is getting to. These may be buffered, and not output, if the Sproc fails, in which case consider using


RAISERROR (N'At step 1', 10, 1) WITH NOWAIT

instead.

Bit tedious, but its a route. If you have a single-step debugger set up for SQL Sprocs that might be a better place to start

I run my test Sprocs within a transaction so that they won't actually change the data:


BEGIN TRANSACTION

EXEC MySproc ...

ROLLBACK

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 13:43:48

Do a find on

units not supplied

in your sproc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-17 : 14:22:20

the problem is this. the app is failing when calling this sp from one facility. From other facilities it is fine.

So i can't focus on the error cond from the app log, I would like to see the actual data being processed in the app.

If I do this for both that is the good facility and the bad i may see some data difference.

quote:
Originally posted by X002548

Do a find on

units not supplied

in your sproc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-17 : 14:28:33

here is the sp. I think there is an issue with the datatime. that some bad data is getting there. If i exec this sp with the parms here it is fine:
exec OGEN.DBD_GET_MEDICINE_DETAIL
@FACILITYKEY=N'ADLU',
@DATEFROM='2012-01-15 11:53:05.757',
@DATETHRU='2012-01-17 11:53:05.757',
@UNITSTR=N'',
@INCLUDEDISCH=0,
@PATNUMBER=-1,
@MEDNAME=N''
but if data time is this which it can be:
@DATEFROM='2012-01-15 11:53:05.757000',
@DATETHRU='2012-01-17 11:53:05.757000',

you get this error
Msg 8114, Level 16, State 1, Procedure DBD_GET_MEDICINE_DETAIL, Line 0
Error converting data type varchar to datetime.

the app log itself is this:
"01-17-2012 11:53:08 AM|ERROR: FileLog : System.Data.SqlClient.SqlException (0x80131904): Invalid length parameter passed to the LEFT or SUBSTRING function"

ALTER PROCEDURE [OGEN].[DBD_GET_MEDICINE_DETAIL]
(
@FACILITYKEY varchar(1000),
@DATEFROM DATETIME,
@DATETHRU DATETIME,
@UNITSTR VARCHAR(100),
@INCLUDEDISCH NUMERIC(1, 0),
@PATNUMBER NUMERIC(9, 0),
@MEDNAME VARCHAR(100)
)
AS
BEGIN

SELECT @UNITSTR = isnull(@UNITSTR,'')

-- need to code for the substrings in this sp. if there are nulls or data not found we must code so that it won't blow up the app
--- currently at some facilities this is causing to blow up the substrings,
SELECT AM.FACILITY_KEY FACILITY
, M.UNIT_CODE UNIT
, RTRIM(M.LAST_NAME) + CASE WHEN RTRIM(M.FIRST_NAME) <> '' THEN ', ' ELSE '' END + RTRIM(M.FIRST_NAME) PATIENT_NAME
, CASE WHEN M.NURSING_UNIT is not null THEN M.NURSING_UNIT ELSE '' END NURSING_UNIT
, CASE WHEN M.UNIT_CODE is not null THEN M.UNIT_CODE ELSE '' END UNIT_CODE
, CASE WHEN M.ROOM_BED is not null THEN SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ELSE '' END ROOM

-- ,CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' ' + SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7) ADMIN_TIME

, (COALESCE(CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' ','') + COALESCE(SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7),'')) ADMIN_TIME

, AM.ORDER_NAME
, AM.DOSAGE
, AM.DOSAGE_FORM
, CASE WHEN AL.ADMIN_RESULT = -9 THEN 'Y' ELSE '' END NOTE_ENTERED
, AM.ORDER_KEY

FROM (SELECT TOP(DATEDIFF(DD, @DATEFROM, @DATETHRU) + 1) OGEN.DATEONLY(@DATEFROM) + ROW_NUMBER() OVER (ORDER BY DX_KEY) - 1 DATE_ORDER, ROW_NUMBER() OVER(ORDER BY DX_KEY) DAY_ORDER FROM OGEN.GEN_C_DX ORDER BY DX_KEY) DTR
INNER JOIN OPTC.ORD_M_ADMIN AM
ON DTR.DATE_ORDER + ( AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME) ) BETWEEN AM.START_DATE AND AM.END_DATE
AND (AM.START_DATE BETWEEN @DATEFROM AND @DATETHRU OR AM.END_DATE BETWEEN @DATEFROM AND @DATETHRU OR (AM.START_DATE < @DATEFROM AND AM.END_DATE > @DATETHRU))
AND AM.FACILITY_KEY IN (SELECT VALUE FROM [OGEN].[COMMA_TO_TABLE](@FACILITYKEY))
AND ( @PATNUMBER = -1 OR AM.PAT_NUMBER = @PATNUMBER )
AND ( @MEDNAME IS NULL OR @MEDNAME = '' OR AM.ORDER_NAME LIKE '%' + @MEDNAME + '%' )
AND AM.PRN = 0
INNER JOIN OGEN.GEN_M_PATIENT_MAST M
ON AM.PAT_NUMBER = M.PAT_NUMBER
AND AM.FACILITY_KEY = M.FACILITY_KEY
AND M.FACILITY_KEY IN (SELECT VALUE FROM [OGEN].[COMMA_TO_TABLE](@FACILITYKEY))
AND ( @UNITSTR IS NULL OR @UNITSTR = '' /*OR CHARINDEX(M.UNIT_CODE, @UNITSTR) % 2 = 1 */)
LEFT OUTER JOIN OPTC.ORD_D_ADMIN_LOG AL
ON AM.ORDER_KEY = AL.ORDER_KEY
AND OGEN.DATEONLY(AL.ADMIN_TIME) = DTR.DATE_ORDER
AND AL.ADMIN_TIME - OGEN.DATEONLY(AL.ADMIN_TIME) = AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME)
WHERE OPTC.ORD_IS_ADMIN(AM.START_DATE, DTR.DATE_ORDER, AM.DAYS_REQ) = 1
AND NOT EXISTS ( SELECT AL1.ORDER_KEY
FROM OPTC.ORD_D_ADMIN_LOG AL1
WHERE AL1.ORDER_KEY = AM.ORDER_KEY
AND OGEN.DATEONLY(AL1.ADMIN_TIME) = DTR.DATE_ORDER
AND AL1.ADMIN_TIME - OGEN.DATEONLY(AL1.ADMIN_TIME) = AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME)
AND CHARINDEX(CAST(ABS(AL1.ADMIN_RESULT) AS VARCHAR(2)), '01234') > 0 )
AND NOT EXISTS ( SELECT OM.ORDER_KEY
FROM OPTC.ORD_M_ORDER OM
WHERE OM.ORDER_STATUS = 0
AND OM.HOLD_ON_FROM = AM.ORDER_KEY
AND DTR.DATE_ORDER BETWEEN OM.START_DATE
AND OM.END_DATE )
AND ( @INCLUDEDISCH = 0
OR 1 = CASE WHEN @INCLUDEDISCH = 1 AND M.DISCHARGE_DATE IS NULL THEN 1
WHEN @INCLUDEDISCH = 2 AND M.DISCHARGE_DATE IS NOT NULL THEN 1
ELSE 0 END )
ORDER BY PATIENT_NAME, M.PAT_NUMBER, DATE_ORDER, AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME)
END

quote:
Originally posted by X002548

Can you post

the sample execution of the call that's failing

The Actual Error message (cut and paste)

And the actual Stored procedure code

Thanks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-17 : 14:58:55

Based on your latest posting, you have couple of different options:

1. Make sure that the client formats the date strings correctly. Even better, if is a .Net client, it should use the SqlDbType.DateTime for the parameter type.

2. If it is not possible to change the client, you will need to change the stored procedure in one of the following ways:
a) make the parameter type DATETIME2 instead of DATETIME. This will work only in SQL 2008 or higher. The string that you have shown in your example will convert correctly to DATETIME2, but not to DATETIME.

b) make the parameter type varchar(32) instead of DATETIME. Then convert it to time in the stored proc after stripping off the extra zeros at the end.

If the issue is with the DATETIME parameter, the exception is generated even before the stored procedure code starts, so print statements and TRY..CATCH may not help you. However, you should be able to use profiler or DBCC INPUTBUFFER to see what gets sent to the server.

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-17 : 15:07:20

I tried the datatime2 but get this error:
Parameter or variable '@DATEFROM' has an invalid data type.

Looks like we are this version of 2008 r2
9.00.3042.00

i think this datetime2 applies to 10 and over. what a shame.

quote:
Originally posted by sunitabeck

Based on your latest posting, you have couple of different options:

1. Make sure that the client formats the date strings correctly. Even better, if is a .Net client, it should use the SqlDbType.DateTime for the parameter type.

2. If it is not possible to change the client, you will need to change the stored procedure in one of the following ways:
a) make the parameter type DATETIME2 instead of DATETIME. This will work only in SQL 2008 or higher. The string that you have shown in your example will convert correctly to DATETIME2, but not to DATETIME.

b) make the parameter type varchar(32) instead of DATETIME. Then convert it to time in the stored proc after stripping off the extra zeros at the end.

If the issue is with the DATETIME parameter, the exception is generated even before the stored procedure code starts, so print statements and TRY..CATCH may not help you. However, you should be able to use profiler or DBCC INPUTBUFFER to see what gets sent to the server.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-17 : 15:18:54

What version of SQL are you using? DateTime2 is only available in SQL 2008 and SQL 2008 R2.

--
Gail Shaw
SQL Server MVP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-17 : 15:23:42

we are 2008 r2 but see this thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144274

quote:
Originally posted by GilaMonster

What version of SQL are you using? DateTime2 is only available in SQL 2008 and SQL 2008 R2.

--
Gail Shaw
SQL Server MVP

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-17 : 17:29:29

I am pretty sure now the problem is with the @UNITSTR.

When I run the exec sp for the facility with the failing app, it gives the same error message when @UNITSTR = ''
but null or any value I throw in there, it runs not returning any data but not failing.

The crucial question is how can i ensure in the sp that @UNITSTR is always set to at least null, to not pass blanks over to the app?
can it be done on the parameter area?
USE [ADLPRO2]
GO
/****** Object: StoredProcedure [OGEN].[DBD_GET_MEDICINE_DETAIL] Script Date: 01/17/2012 16:19:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [OGEN].[DBD_GET_MEDICINE_DETAIL]
(
@FACILITYKEY varchar(1000),
@DATEFROM datetime,
@DATETHRU datetime,
@UNITSTR VARCHAR(100),
@INCLUDEDISCH NUMERIC(1, 0),
@PATNUMBER NUMERIC(9, 0),
@MEDNAME VARCHAR(100)
)
AS
BEGIN

Kristen
Test

22859 Posts

Posted - 2012-01-18 : 03:37:54

"but if data time is this which it can be:
@DATEFROM='2012-01-15 11:53:05.757000',
@DATETHRU='2012-01-17 11:53:05.757000',

you get this error
Msg 8114, Level 16, State 1, Procedure DBD_GET_MEDICINE_DETAIL, Line 0
Error converting data type varchar to datetime."

Change the date/time "string" parameters to use the format 'yyyymmdd hh:mm:ss.sss' - i.e. NO hyphens. The format you are using, including hyphens, is ambiguous and will be parsed by SQL using whatever current settings it has, including server settings and the Language and Country setting for the currently logged-on user.

If you need to be able to pass microseconds in the parameter you can change the parameter to be DATETIME2, or to varchar(26) and then CONVERT the parameter to a DATETIME in the Sproc using LEFT() to get the date&time parts excluding the microseconds

"Invalid length parameter passed to the LEFT or SUBSTRING function"

only two places you are using SUBSTRING (and a third that is commented out)


, (COALESCE(CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' ','') + COALESCE(SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7),'')) ADMIN_TIME

I recommend specifying the size of the varchar and not using "CONVERT(varchar" without any size - you are relying on the default size which may, or may not, be large enough for what you want. It would be better to send Date / Time to the application in Native format and then have the application format it.


, CASE WHEN M.ROOM_BED is not null THEN SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ELSE '' END ROOM

The SUBSTRING() will raise error if there is NO space in M.ROOM_BED. You could fix that using:


CASE WHEN M.ROOM_BED LIKE '% %' THEN SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ELSE '' END ROOM

Presumably there is supposed to be a space in that column's values? Thus I suggest you check how many such "invalid" rows you have


SELECT COUNT(*)
FROM MyTable
WHERE     M.ROOM_BED IS NOT NULL
      AND M.ROOM_BED NOT LIKE '% %'

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-18 : 09:15:05

THANK YOU KIRSTEN!!! This is the best birthday present I could have wanted! (it's my birthday on my Skype account for some reason)

It was the room bed issue. I still want to change the datetime part as well.

Kristen
Test

22859 Posts

Posted - 2012-01-18 : 13:00:48

If the ROOM_BED column is, in effect, storing two (or more) pieces of data it would be better to either refactor that into two columns, or a separate "child" table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources