Problem with dot and comma - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Problem with dot and comma

Author

Topic

ante74
Starting Member

2 Posts

Posted - 2014-11-12 : 09:50:24

Hello,

I have a problem with a stored procedure who I think is the problem.
The stored procedure loops threw a recipe table(60 columns) with only two datatypes "nvarchar(50)" and "real" then I use the UNPIVOT function to put them into another table. When the data arrives to the destination table it is dot instead of comma? I have only commas in my recipe table in the "real" datatype fields. By reading some forum I understand that to use the UNPIVOT function I have to convert all datatypes to be the same(in the script) so in this case it would be nvarchar(50). The destination table and the column that the data is comming to has datatype nvarchar(50).
Does anybody have any tip to solve this problem?
Here is my stored procedure:

USE [KNUT]
GO
/****** Object: StoredProcedure [TransApp].[SkickaData_215_Ugn1_excel] Script Date: 2014-11-12 14:19:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: TransApp.SkickaData_215_Ugn1_excel.sql|0|0|MSSQL::/SESSF98G3/KNUT/admin/SqlProcedure/TransApp.SkickaData_215_Ugn1_excel.sql
-- Batch submitted through debugger: SQLQuery1.sql|7|0|C:\Users\bepak\AppData\Local\Temp\~vsB32C.sql
-- Batch submitted through debugger: SQLQuery1.sql|7|0|C:\Users\bepak\AppData\Local\Temp\~vs699F.sql

--ALTER procedure [TransApp].[SkickaData_215_excel](@CostCenter NVARCHAR(10),@MachineNo NVARCHAR(10),@Recipe NVARCHAR(200))

ALTER procedure [TransApp].[SkickaData_215_Ugn1_excel](@CostCenter NVARCHAR(10),@MachineNo NVARCHAR(10),@Recipe NVARCHAR(200))
as
begin
set nocount on

begin try
-- Deklarera variabler
declare @imessagebody int,
@messageXPath varchar(50),
@locError int,
@locMessage_name varchar(200),
@locMessage_id varchar(200),
@locSend_date datetime,
@inSystemId varchar(30),

@ProductionUnit varchar(20),

@Columns NVARCHAR(MAX)='',
@Query NVARCHAR(MAX)='',
@CastColumns NVARCHAR(MAX)='',
@SearchRecipe NVARCHAR(200),

/* Ändras här beroende på vilken tabell man vill ändra */
@TableName NVARCHAR(MAX)='Recept_215_Ugn1',
@ColumnName NVARCHAR(MAX)='tube_info'

/* Skapa svarsmeddelande */
set @locMessage_name = 'KNUT_PLC';
set @locSend_date = getdate()
set @locMessage_id = @locMessage_name + '-' + @CostCenter + '-' + @MachineNo + '-' + convert(varchar(32), @locSend_date, 121);

/* Hämta Recept */
SELECT @SearchRecipe=tube_info
FROM Recept_215_Ugn1 where tube_info=@Recipe /* Ändras här beroende på vilken tabell man vill ändra */

/* Kolla om vi hittat rätt Receptnummer */
IF @SearchRecipe=@Recipe
Begin

/* Hämta kolumnnamnen för Recept tabellen */

/*SELECT @Columns = @Columns + QUOTENAME(COLUMN_NAME) + ',', @CastColumns = @CastColumns+CASE WHEN data_type = 'real' THEN
'CAST('+QUOTENAME(COLUMN_NAME)+' AS NVARCHAR(50)) AS '+QUOTENAME(COLUMN_NAME) ELSE
QUOTENAME(COLUMN_NAME) END+','
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION */

SELECT @Columns = @Columns + QUOTENAME(COLUMN_NAME) + ',', @CastColumns = @CastColumns+CASE WHEN data_type = 'real' THEN
'CAST('+QUOTENAME(COLUMN_NAME)+' AS NVARCHAR(50)) AS '+QUOTENAME(COLUMN_NAME) ELSE
QUOTENAME(COLUMN_NAME) END+','

FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION

SET @Columns = LEFT(@Columns,LEN(@Columns)-1)
SET @CastColumns = LEFT(@CastColumns,LEN(@CastColumns)-1)

/* Hämta data som vi kör en UNPIVOT på..... */
SET @Query = 'SELECT '''+@locMessage_id+''' TransactionId, PropertyId, '''+@CostCenter+''' CostCenter, '''+@MachineNo+''' MachineNo, '''+@locMessage_name+''' TransactionName, PropertyValue, getdate() TimeStamp
FROM (SELECT '+@CastColumns+' FROM '+@TableName+' where '+ @ColumnName +' ='''+@SearchRecipe+''') AS P
UNPIVOT(PropertyValue FOR PropertyId IN ('+@Columns+')) AS UC
JOIN (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@TableName+''') CO
ON PropertyId = CO.COLUMN_NAME'

/* Stoppa in data i Tag_In */

INSERT INTO Tag_In
EXEC sp_executesql @Query

end

else

/* Hittar ej receptnummret... returnera -9999*/
insert into TransApp.Tag_In (TransactionId, PropertyId, CostCenter, MachineNo, TransactionName, PropertyValue, TimeStamp)
Values ( @locMessage_id, 'tube_info', @Costcenter, @MachineNo, @locMessage_name, -9999, @locSend_date)

/* FELHANTERING */

end try

begin catch
declare @locErrorMessage nvarchar(4000),
@locErrorSeverity int,
@locErrorState int

select
@locErrorMessage = ERROR_MESSAGE(),
@locErrorSeverity = ERROR_SEVERITY(),
@locErrorState = ERROR_STATE()

raiserror (@locErrorMessage,
@locErrorSeverity,
@locErrorState
)

return ERROR_NUMBER()
end catch

return 0;
end

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-12 : 10:06:29

When you cast a real as an nvarchar, SQL will automatically insert a comma for every thousand and a period as a decimal point. If you want it the European way (periods for thousands separators and commas for decimal portion) you'll need to replace them after conversion. e.g.


select replace(replace(replace(CONVERT(varchar, CAST(12345678.91 AS money), -1),',','/'),'.',','),'/','.')

ante74
Starting Member

2 Posts

Posted - 2014-11-13 : 01:58:20

Hi, I really appreciate your help. Unfortunately I did not manage to solve the problem anyway. Can you be a little more precise in your tips regarding where I should put my "replace" in this code snippet:

SELECT @Columns = @Columns + QUOTENAME(COLUMN_NAME) + ',', @CastColumns = @CastColumns+CASE WHEN data_type = 'real' THEN
'CAST('+QUOTENAME(COLUMN_NAME)+' AS NVARCHAR(50)) AS '+QUOTENAME(COLUMN_NAME) ELSE
QUOTENAME(COLUMN_NAME) END+','
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION

Regards
Andreas

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 08:41:50

I can't quite see where you would do that since you haven't posted any results from this query. It looks like it might be here:


'CAST('+QUOTENAME(COLUMN_NAME)+' AS NVARCHAR(50))

which would become:


'replace(replace(replace(CONVERT(varchar,' + QUOTENAME(COLUMN_NAME) + ', 1),'''','''',''''/''''),''''.'''','''',''''),''''/'''',''''.'''')'

but you'll have to check the quotation marks (apostrophes) to be sure you have the right number and that they are balanced!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources