→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
bd
Engaged Sweeper III
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.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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().

View solution in original post

4 REPLIES 4
bd
Engaged Sweeper III
That does the trick, thank you!
RCorbeil
Honored Sweeper II
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
Engaged Sweeper III
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
Honored Sweeper II
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.