Report problem

[bd]

Not really a report problem, more of a database version problem I think.

I pull virus dat version from the registry on all computers, then I have this query that returns all computers that are greater than 30 days old.

This query works with an SQL2K5 database:

Select Top 1000000 tblAssets.AssetName,
Convert(datetime,Left(Right(tblRegistry.Value, 12), 8)) As DatDate,
tblAssets.Username,
tblAssets.Lastseen,
tblRegistry.AssetID
From tblRegistry
Inner Join tblAssets On tblRegistry.AssetID = tblAssets.AssetID
Where tblRegistry.Valuename = 'NAVCORP_70' And
DateDiff(dd, Convert(datetime,Left(Right(tblRegistry.Value, 12), 8)),
GetDate()) > 30

When I try to save this report on a new server I am building out it gives me the following error: There was an error parsing the query. [Token line number =1,Token line offset=58,Token in error = Left ]

The only difference between servers is that the new server is running SQL Compact, the server I'm copying reports from is 2K5.

[bd]

That does the trick, thank you!

[RCorbeil · Accepted solution]

Substring() syntax

Try this:

Substring( Substring( NAVCORP_70,LEN(NAVCORP_70)-11, 12 ),1,8 )

The inner Substring() performs the equivalent of a Right(), then the outer Substring() performs the equivalent of a Left().

[bd]

Thanks for the reply. Is there a workaround that you could suggest?

The actual field that I'm parsing up ('NAVCORP_70') looks like this:

C:\PROGRA~2\Symantec\DEFINI~1\VIRUSD~1\20131009.004

I really hate to download and install a new version of SQL, I've already got alot of work into this new server.

[RCorbeil]

From the TechNet reference, it appears that the shortcoming is in SQL Server Compact. From that reference, the Left() and Right() functions are not among those supported.