error on any table of sql server 2017

I've tried profile , but it gives me always an error: conversion in varchar data in datetime generated a value not suitable (it's translated 'cuse I have an italian version)

  • I believe the correct translation for the error is "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

    If so, the problem sounds very similar to this one:

    https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in

    Other languages may expect a DD/MM/YYYY format. SQL Server generates this error when there is a mismatch between what is provided and what the SQL Server is expecting.

    The Free Tools are normally tested with US default settings and time zones. Given your location and language, try following the advice in the link and see if that resolves the issue.

    SQL Data Profiler has been tested with SQL Server 2017 and it does work, so this should not be the problem.

  • So, I must change my server date and time defaults to MM/DD/YYYY? I can't 'because all the other programs won't work properly. It' s a pity 'cause I just needed this tool ..

  • I think you misinterpreted my suggestion. I was merely trying to figure out what the actual issue was given your initial post. Let me explain a couple of things.

    SQL Data Profiler operates as follows:

    - it first gathers some count information on the table selected

    - it then uses the Information Schema to gather the table schema information

    - using the schema information it gather statistics on the columns and determines recommendations for improvements

    - finally it presents the results

    No where in the profiling step does it presume a US datetime format but instead using the ISO format.

    If the problem is related to data stored related date time, there could be a number of explanations. Such as, possible the datetime values being shortened to YYMMDD, in which case that could present problems handling them when comparing ranges using the ISO format. Just a guess.

    In an effort to understand your environment, can you provide the following information:

    - Windows OS Localization settings (Datetime, Region, Langauage)

    - SQL Server settings (Collation, Language)

    - Table schema sample and small data sample containing datetime values

    In addition could you provide application logs to help further diagnose the issue. See the following two articles:

    community.idera.com/.../kb-how-to-send-log-files-for-free-tools-to-idera
    https://community.idera.com/database-tools/free-tool-forums/f/forum/23191/kb-in-which-folder-is-user-application-data-stored (see "application logs: folder in Local for IDERA SQL Data Profiler")

    I believe the Idera resource that monitors the Send To address may be out of vacation. If you could send the logs to [email protected], I'd be happy to look them over.