ODBCOUTPUT Double Quotes in Code

[dfrench77]

ODBCOUTPUT command is not updating the SQL Sever table. I suspect it is because of the double quotes wrapped around the insert command generated in the asciiout file.

Below is my complete code from the data tab:

Code: Select all

## Push Data into the SNO table (StartingInventory)

vsModel = ''| Model|'' ;
ItemCode = ''| Items|'';
Location = ''| Locations|'' ;
TranType = ''| HoldType|'';
Quantity =  Value;
UserID = ''| V2|'';
CreateDate = ''| vsCreateTime|'';


asciioutput('temp.txt',Expand( 'INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( ''%vsModel%'', ''%ItemCode%'', ''%Location%'',''%TranType%'',''%Quantity%'',''%UserID%'', ''%CreateDate%'' )' ) );

ODBCOutput('ODS', Expand( 'INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( ''%vsModel%'', ''%ItemCode%'', ''%Location%'',''%TranType%'',''%Quantity%'',''%UserID%'', ''%CreateDate%'' )' ) );

Here is the temp.txt file output with double quotes.


"INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( 'Electro-Vinlys', '79', 'Plaquemine','silo',' 2000.000','Admin', '2012-04-09' )"

When I copy this code into SQL Server Management Studio and remove the double quotes from the statement it loads the data. Any help on how to remove the double quotes will be greatly appreciated.

Regards,

Desi

[Alan Kirk]

ODBCOUTPUT command is not updating the SQL Sever table. I suspect it is because of the double quotes wrapped around the insert command generated in the asciiout file.

...

When I copy this code into SQL Server Management Studio and remove the double quotes from the statement it loads the data. Any help on how to remove the double quotes will be greatly appreciated.

I can't recall whether this affects ODBCOutput as well, but the way to remove the double quotes from the ASCIIOutput is via the DatasourceASCIIQuoteCharacter variable. In the prolog you set it to an empty string ('').

[dfrench77]

I guess my original post (question) was confusing. The code that I posted previously is generating the following error.

"Electro-Vinlys","Admin","silo","Plaquemine","79","1","Inventory Qty","2000.",Data Source line (1) Error: Data procedure line (44): Error executing SQL query: "INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( 'Electro-Vinlys', '79', 'Plaquemine','silo',' 2000.000','Admin', '2012-04-09' )"

When I take this statement without the double quotes and execute in MS SQL Server it updates the StartingInventory table, however I believe it is failing because TM1 Turbo Integrator code is placing the double quotes around the statement. Is this correct? Is my code putting the double quotes?

Code: Select all

ODBCOutput('ODS', Expand( 'INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDat
e] ) VALUES ( ''%vsModel%'', ''%ItemCode%'', ''%Location%'',''%TranType%'',''%Quantity%'',''%UserID%'', ''%CreateDate%'' )' ) );

[Duncan P]

The double quotes round the statement in the file are put there by ASCIIOutput and will not be present in the statement being executed by ODBCOutput. If you want to remove them from the file then set DatasourceASCIIQuoteCharater as Alan suggested.

You have a set of lines which give the appearance of putting quote characters round your data variables. However all they do is add an empty string to both ends. In other words they don't do anything and should be removed.

I presume your [Quantity] variable is numeric, in which case it should not have quotes round it.

Code: Select all

...,''%Quantity%'',...

should be

Code: Select all

...,%Quantity%,...

Having said that I cannot see how your statement is executing in SQL Server Management Studio unless you are also removing the single quote characters round the 79 you have for quantity.

[rmackenzie]

I presume your [Quantity] variable is numeric, in which case it should not have quotes round it.

I think that's the problem as well.

Having said that I cannot see how your statement is executing in SQL Server Management Studio unless you are also removing the single quote characters round the 79 you have for quantity.

Maybe SSMS does the necessary type conversion but the ODBC driver on the OP's TM1 server won't do this automatically?

[dfrench77]

Thanks for you comments on the asciioutput. However, that is really not the issue. The issue is that the Insert command is not executing from the TI process. See error message below again. Please note the " " double quotes around the insert command.

"Electro-Vinlys","Admin","silo","Plaquemine","79","1","Inventory Qty","2000.",Data Source line (1) Error: Data procedure line (44): Error executing SQL query: "INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( 'Electro-Vinlys', '79', 'Plaquemine','silo',' 2000.000','Admin', '2012-04-10' )"

When I take this insert command without the double quotes and execute in MS SQL Server Management Studio the table is updated. Any insight on how to resolve this error would be greatly appreciated.

Regards,

Desi

[Duncan P]

The double-quote characters in the error log are put there by the error logging procedure to delimit the SQL statement that has the error. They are not part of the statement executed by ODBCOutput.

To confirm this assign the result of the Expand function into a variable and execute the CODE function to get the ASCII code of the first character, e.g.

Code: Select all

sql_stmnt = EXPAND( .... );
initial_char_code = CODE( sql_stmnt, 1 );

If you are still not sure then open a profile in SQL Server Management Studio and log PrepareSQL. You will then see exactly what is getting through.

When you are convinced that double-quotes are not your problem try re-reading the previous posts.

[dfrench77]

Duncan,

The attached file illustrates that the single quotes are not causing the issue. The attached file is the generic Insert command in MS SQL Server.

Here is the insert command generated from TI and executed in SQL Server

Code: Select all

INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) 
VALUES ( 'Electro-Vinlys', '79', 'Plaquemine','railcar','  -10.000','Admin', '2012-04-10' )

There is no issue executing this in MS SQL Server. I don't understand why TI is generating an error if the code works in SQL Server.

Regards,

Desi

[Duncan P]

Hi Desi,

I understand that in SQL Server Management Studio the statement is executing fine. However it would be interesting to know whether you have tried the modification to your code that I suggested. If you have and the error is still there then I suggest that you have two options available to you. You can trace within SQL Server http://msdn.microsoft.com/en-us/library/ms181091.aspx or you can trace the ODBC http://support.microsoft.com/kb/274551.

Let us know what results you get. Furthermore it would be interesting to see the structure of the target table and particularly the datatypes of the fields.

Thanks,
Duncan.

[dfrench77]

Duncan,

I have tried to implement your suggested code, but have not been successful to this point. Here is the code I have so far:

Code: Select all

sql_stmnt = Expand( 'INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( ''%vsModel%'', ''%ItemCode%'', ''%Location%'',''%TranType%'',''%Quantity%'',''%UserID%'', ''%CreateDate%'' )' ) ;
initial_char_code = CODE( sql_stmnt, 1 );

asciioutput('temp.txt', Expand( 'INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( ''%vsModel%'', ''%ItemCode%'', ''%Location%'',''%TranType%'',''%Quantity%'',''%UserID%'', ''%CreateDate%'' )' ) );

Furthermore it would be interesting to see the structure of the target table and particularly the datatypes of the fields.

I have attached a file with the structure of the target table and particularly the datatypes of the fields.

SQL Server Insert Command.docx

(36.18 KiB) Downloaded 846 times

Regards,

Desi

[Duncan P]

Try this. Note that the %Quantity% does not have '' at each end.

Code: Select all

ODBCOutput('ODS', Expand( 'INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( ''%vsModel%'', ''%ItemCode%'', ''%Location%'',''%TranType%'',%Quantity%,''%UserID%'', ''%CreateDate%'' )' ) );

Duncan.

[dfrench77]

Duncan,

I tried your the code without the '' '' around %Quantity% and I still get the TI "Error executing SQL query".

Regards,

Desi

[dfrench77]

Duncan,

My apologies

I tried your the code without the '' '' around %Quantity% and I still get the TI "Error executing SQL query".

.

The statement is working. Here is the code that I used.

Code: Select all

ODBCOutput('ODS', Expand( 'INSERT INTO [SNO].[dbo].[StartingInventory] ( [Model], [ItemCode], [Location], [TranType], [Quantity], [UserID], [CreateDate] ) VALUES ( ''%vsModel%'', ''%ItemCode%'', ''%Location%'',''%TranType%'',%Quantity%,''%UserID%'', ''%CreateDate%'' )' ) );

I was receiving the error because I was trying to write a duplicate record to the table.

Thanks for your help.

Regards,

Desi

[tomok]

I was receiving the error because I was trying to write a duplicate record to the table.

Well, if that's the case then this statement can not possibly be true.

When I take this insert command without the double quotes and execute in MS SQL Server Management Studio the table is updated. Any insight on how to resolve this error would be greatly appreciated.

[Michel Zijlema]

I was receiving the error because I was trying to write a duplicate record to the table.

Well, if that's the case then this statement can not possibly be true.

When I take this insert command without the double quotes and execute in MS SQL Server Management Studio the table is updated. Any insight on how to resolve this error would be greatly appreciated.

Well... the duplicate record could be caused by the running the test and then trying again to insert in TM1

Michel

[dfrench77]

Well... the duplicate record could be caused by the running the test and then trying again to insert in TM1

This is correct. I was running the test without truncating or deleting the records from table. My apologies again.

Regards,

Desi