Page 1 of 1
ODBCOUTPUT Double Quotes in Code
Posted: Mon Apr 09, 2012 6:58 pm
by 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
Re: ODBCOUTPUT Double Quotes in Code
Posted: Mon Apr 09, 2012 7:44 pm
by Alan Kirk
dfrench77 wrote: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 ('').
Re: ODBCOUTPUT Double Quotes in Code
Posted: Mon Apr 09, 2012 8:25 pm
by 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%'' )' ) );
Re: ODBCOUTPUT Double Quotes in Code
Posted: Mon Apr 09, 2012 11:26 pm
by 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.
should be
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.
Re: ODBCOUTPUT Double Quotes in Code
Posted: Tue Apr 10, 2012 10:28 am
by rmackenzie
Duncan P wrote: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.
Duncan P wrote: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?
Re: ODBCOUTPUT Double Quotes in Code
Posted: Tue Apr 10, 2012 12:23 pm
by 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
Re: ODBCOUTPUT Double Quotes in Code
Posted: Tue Apr 10, 2012 12:39 pm
by 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.
Re: ODBCOUTPUT Double Quotes in Code
Posted: Tue Apr 10, 2012 1:00 pm
by 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
Re: ODBCOUTPUT Double Quotes in Code
Posted: Tue Apr 10, 2012 1:12 pm
by 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.
Re: ODBCOUTPUT Double Quotes in Code
Posted: Tue Apr 10, 2012 1:26 pm
by 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.
Regards,
Desi
Re: ODBCOUTPUT Double Quotes in Code
Posted: Tue Apr 10, 2012 2:30 pm
by 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.
Re: ODBCOUTPUT Double Quotes in Code
Posted: Tue Apr 10, 2012 7:57 pm
by dfrench77
Duncan,
I tried your the code without the '' '' around %Quantity% and I still get the TI "Error executing SQL query".
Regards,
Desi
Re: ODBCOUTPUT Double Quotes in Code
Posted: Mon Apr 16, 2012 2:05 pm
by 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
Re: ODBCOUTPUT Double Quotes in Code
Posted: Mon Apr 16, 2012 2:19 pm
by tomok
dfrench77 wrote: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.
dfrench77 wrote: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.
Re: ODBCOUTPUT Double Quotes in Code
Posted: Mon Apr 16, 2012 2:24 pm
by Michel Zijlema
tomok wrote:dfrench77 wrote: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.
dfrench77 wrote: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
Re: ODBCOUTPUT Double Quotes in Code
Posted: Mon Apr 16, 2012 3:26 pm
by 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