Mystery data type conversion

Question

Mystery data type conversion

PHXHoward

SSCrazy Eights

Points: 8389
More actions
March 4, 2016 at 10:05 am

#309172

This one is so weird. I'd love to have some help figuring it out.
A query works fine when I run it. Here is the relevant part of it:
DECLARE @MaxUpLoc AS varchar(10);
DECLARE @TSQL AS varchar(MAX);
-- Find last updated date
SELECT @MaxUpLoc = MAX(sys_updated_on) FROM dbo.sn_task;
-- Get newest data
SET @TSQL = 'SELECT *
INTO sn_task
FROM OPENQUERY( LNKSRV_V7 , ' + CHAR(39) + '
SELECT
column1,
column2,
column3,
<etc..>
from task
WHERE sys_updated_on >= ' + CHAR(39) + CHAR(39) + @MaxUpLoc + CHAR(39) + CHAR(39) + CHAR(39) + ')';
exec (@TSQL);
GO
When it is made into a stored procedure and scheduled to run at night through SQL Agent, it fails.
@MaxUpLoc when I run it = 2016-03-04
but
@MaxUpoLoc when run in the stored proc though SQL Agent is Mar 04 2016
Why would it be that the scheduled job is formatting the date in a different way when doing conversion from datetime to varchar(10)?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i would recommend adding an explicit formatting to the date to guarantee it's converted to YYYY--MM-DD format.

you could potentially be getting dd/mm/yyyy,mm/dd/yyyy or event ""Mar 3 2016" if you don't explicitly say the format, depending on local environment settings, and that would result int he error you see,

--SELECT CONVERT(varchar,getdate(),120) = 2016-03-04 12:13:22

--SELECT CONVERT(varchar(10),getdate(),120) = 2016-03-04

SELECT @MaxUpLoc = CONVERT(varchar(10),MAX(sys_updated_on),120) FROM dbo.sn_task;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

What is the data type for:

sys_updated_on

?

If it's smalldatetime/datetime/date/datetime2, then you should compare it to format YYYYMMDD, because that format is 100% universal across any/all SQL instances. The convert code for that is 112, therefore:

...

DECLARE @MaxUpLoc AS varchar(8);

...

SELECT @MaxUpLoc = CONVERT(varchar(8), MAX(sys_updated_on), 112) FROM dbo.sn_task;

...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 3

The sys_updated_on column is datetime2(7)

I think that the suggestions to explicitly format the data that is going into the varchar(10) column will solve the problem. It is really a mystery to me why the scheduled job is generating output different than when I run the same query.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

PHXHoward (3/4/2016)
The sys_updated_on column is datetime2(7)
I think that the suggestions to explicitly format the data that is going into the varchar(10) column will solve the problem. It is really a mystery to me why the scheduled job is generating output different than when I run the same query.

Because it's actually a different application.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 5

So I think the reason it acts differently when run from the agent job is because the job history is showing what was returned by the remote server which is not SQL Server and is handling the query differently.

"OLE DB provider "MSDASQL" for linked server "LNKSRV_V7" returned message "[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Error getting the literal value of right operand.[10169]..."

When I would run it myself by changing the EXEC (@TSQL) to PRINT (@TSQL), it displays the query in the way that SQL Server would handle it.

It shows the point that you guys made that different systems handle things differently and so it is important to specifically define the format.