Skip to main content
Solved

SQLCreator issues

  • March 15, 2019
  • 13 replies
  • 75 views
D
Forum|alt.badge.img

@steveatsafe

Good morning,

I am having issues with writing to our Azure Database. We have a bunch of features that have correlating Ids. The objective is to find the latest and greatest feature(s) based on a uuid that is assigned to each feature and the features that relate to it.

The SQL query I wrote before worked up until now. Now I receive this error message

 

"com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting from a character string to uniqueidentifier."

 

Unsure why all of sudden this is happening.

 

DECLARE @aprt_id INT = $(Arpt_ID), @address_id UNIQUEIDENTIFIER;

;WITH idetails AS

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY venue_id ORDER BY record_id DESC) AS recency

FROM airport

WHERE ARPT_ID = @arpt_id

)

SELECT @address_id = address_id FROM idetails WHERE recency = 1;

SELECT *

FROM airport, dbo.[building] as building, dbo.[utility_pipe] as utility_pipe

WHERE airport.[address_id] = @address_id AND building.[address_id] = @address_id AND building.[arpt_id] = $(Arpt_ID) AND building.[building_id]=$(Bldg_ID) AND building.[id] = utility_pipe.[building_id]

 

Any assistance would be great!

Thanks!

David

 

Best answer by david_r

At first glance the error seems to be related to SQL Server only, not FME. My first guess would be some issue with the data where SQL Server is unable to cast address_id as a UUID.

What happens if you execute the same query in the MS SQL Server Management Studio and you scroll through the entire result set?

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

13 replies

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • Best Answer
  • March 15, 2019

At first glance the error seems to be related to SQL Server only, not FME. My first guess would be some issue with the data where SQL Server is unable to cast address_id as a UUID.

What happens if you execute the same query in the MS SQL Server Management Studio and you scroll through the entire result set?

D
Forum|alt.badge.img

At first glance the error seems to be related to SQL Server only, not FME. My first guess would be some issue with the data where SQL Server is unable to cast address_id as a UUID.

What happens if you execute the same query in the MS SQL Server Management Studio and you scroll through the entire result set?

Hi @david_r,

When I copy the query into sql server, I receive the same error message. What is really odd, I have the same query in other sql creators but with a slight difference at the end, and it works fine.

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • March 15, 2019

Hi @david_r,

When I copy the query into sql server, I receive the same error message. What is really odd, I have the same query in other sql creators but with a slight difference at the end, and it works fine.

Difficult (impossible) to tell without knowing your data, but my general recommendation would be to make sure that the exact same query runs successfully in SQL Server Studio first.

D
Forum|alt.badge.img

Hi @david_r,

When I copy the query into sql server, I receive the same error message. What is really odd, I have the same query in other sql creators but with a slight difference at the end, and it works fine.

Additionally, If I was to remove " AND building.[id] = utility_pipe.[building_id] " from the script, it returns values, but the values are related to the building table not the utility pipe.

D
Forum|alt.badge.img

Difficult (impossible) to tell without knowing your data, but my general recommendation would be to make sure that the exact same query runs successfully in SQL Server Studio first.

Agree, and I have tested it in SQL Server, the results were correct. But for some reason I am now getting this error. It's weird that it' s not working, when it was for 2 months.

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • March 15, 2019

Additionally, If I was to remove " AND building.[id] = utility_pipe.[building_id] " from the script, it returns values, but the values are related to the building table not the utility pipe.

If you change the meaning of the SQL enough to change the resulting data, then naturally that may have an impact on whether the error occurs or not.

I think it's fair to say that this is most probably an issue with the query and how it relates to the data, and not with FME.

D
Forum|alt.badge.img

If you change the meaning of the SQL enough to change the resulting data, then naturally that may have an impact on whether the error occurs or not.

I think it's fair to say that this is most probably an issue with the query and how it relates to the data, and not with FME.

Thanks for the help! I'll review the query.

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • March 15, 2019

Agree, and I have tested it in SQL Server, the results were correct. But for some reason I am now getting this error. It's weird that it' s not working, when it was for 2 months.

That's an interesting bit of information: if it suddenly stopped working, my first hunch would be an issue with the data.

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • March 15, 2019

Try validating all the uniqueidentifier type columns, e.g. using something like

SELECT *
FROM   airport
WHERE  TRY_CONVERT(UNIQUEIDENTIFIER, address_id) IS NULL;

The records returned will be those that contain invalid uniqueidentifier values.

D
Forum|alt.badge.img

Try validating all the uniqueidentifier type columns, e.g. using something like

SELECT *
FROM   airport
WHERE  TRY_CONVERT(UNIQUEIDENTIFIER, address_id) IS NULL;

The records returned will be those that contain invalid uniqueidentifier values.

Thanks. None returned. I've been testing the ids to determine if the ids are indeed UUIDs and they are all return as correct UUIDs.

D
Forum|alt.badge.img

To add to the information above, I receive these warning lines:

"Semicolons detected in SQL. May need to use FME_SQL_DELIMITER keyword to specify a delimiter. SQL is: DECLARE @aprt_id INT = $(Arpt_ID), @address_id UNIQUEIDENTIFIER;

;WITH idetails AS

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY venue_id ORDER BY record_id DESC) AS recency

FROM airport

WHERE ARPT_ID = @arpt_id

)

SELECT @address_id = address_id FROM idetails WHERE recency = 1;

SELECT *

FROM airport, dbo.[building] as building, dbo.[utility_pipe] as utility_pipe

WHERE airport.[address_id] = @address_id AND building.[address_id] = @address_id AND building.[arpt_id] = $(Arpt_ID) AND building.[building_id]=$(Bldg_ID) AND building.[id] = utility_pipe.[building_id]"

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • March 18, 2019

To add to the information above, I receive these warning lines:

"Semicolons detected in SQL. May need to use FME_SQL_DELIMITER keyword to specify a delimiter. SQL is: DECLARE @aprt_id INT = $(Arpt_ID), @address_id UNIQUEIDENTIFIER;

;WITH idetails AS

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY venue_id ORDER BY record_id DESC) AS recency

FROM airport

WHERE ARPT_ID = @arpt_id

)

SELECT @address_id = address_id FROM idetails WHERE recency = 1;

SELECT *

FROM airport, dbo.[building] as building, dbo.[utility_pipe] as utility_pipe

WHERE airport.[address_id] = @address_id AND building.[address_id] = @address_id AND building.[arpt_id] = $(Arpt_ID) AND building.[building_id]=$(Bldg_ID) AND building.[id] = utility_pipe.[building_id]"

You can safely ignore the warning here, it's not related to the error.

D
Forum|alt.badge.img

At first glance the error seems to be related to SQL Server only, not FME. My first guess would be some issue with the data where SQL Server is unable to cast address_id as a UUID.

What happens if you execute the same query in the MS SQL Server Management Studio and you scroll through the entire result set?

There were issues with the UUID values that were entering the database. Appreciate the assistance!

Community Stats

32,081
Posts
121,824
Replies
39,716
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.