0

I have a SQL script that works fine in Microsoft SQL Sever Management Studio. It looks something like this:

DECLARE @CONST_STARTDATE DATETIME
DECLARE @CONST_ENDDATE DATETIME
SET @CONST_STARTDATE  = '2017-01-01';
SET @CONST_ENDDATE    = '2050-05-05';

WITH abc AS ( SELECT * FROM tablename WHERE StartDate BETWEEN @CONST_STARTDATE AND @CONST_ENDDATE )

However, it does not seem to work when I run this in MATLAB as follows:

conn    = database('db','user','pw');
qfile   = 'path\to\file.sql'
results = runsqlscript(conn,qfile);

It returns the following errors:

Query Batch 1: No ResultSet was produced 

Query Batch 2: [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@CONST_ENDDATE". 

Query Batch 3: [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@CONST_STARTDATE".

What is the reason for this error in MATLAB, whereas it works fine on the Sever Studio?

WJA
  • 6,676
  • 16
  • 85
  • 152
  • Might be of interst https://stackoverflow.com/questions/21363415/must-declare-the-scalar-variable – marsei Oct 03 '17 at 12:06
  • Not seeing how it explains my problem. – WJA Oct 03 '17 at 12:12
  • Note [tag:batch-file] doesn't mean processing many files. –  Oct 03 '17 at 12:18
  • Check this URL https://www.mathworks.com/help/database/ug/runstoredprocedure.html – Dmitrij Kultasev Oct 03 '17 at 12:20
  • It seems that matlab sends your code to SQL Server NOT as one batch but at least as 3 batches, so any row strating from 3d has no idea about your declarations – sepupic Oct 03 '17 at 12:42
  • Any idea how to put it as one batch? – WJA Oct 03 '17 at 12:44
  • Try to put everything on one line or search for "batch separator" in your "matlab" – sepupic Oct 03 '17 at 12:45
  • Ok it seems that the ; separetes batches. However, when I remove the ; after @CONST_FREQ, the SQL command becomes invalid. I.e. I receive the error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. – WJA Oct 03 '17 at 12:47
  • @DmitrijKultasev That does not work for an ODBC driver. I tried exec (recommended for my driver), but it did not work: Got the error: Incorrect syntax near '\'. – WJA Oct 03 '17 at 12:48
  • Also, I understand now that it indeed runs individual batches. However, I do not understand why it works in Microsoft Server Management Studio. What do they do differently? How do they call the script? – WJA Oct 03 '17 at 12:53
  • @JohnAndrews it can separate batches with GO statement. In that case all the variables dissappear. Will it work if you remove declarations and hardcode the values? – Dmitrij Kultasev Oct 03 '17 at 13:31
  • @DmitrijKultasev not really, I am really looking to make it dynamic. – WJA Oct 03 '17 at 14:07
  • Documentation states that it supports variables. Try to 1) remove variables from file, then execute runsqlscript(conn,qfile,'CONST_STARTDATE','2017-01-01','CONST_ENDDAT','2050-05-05'); – Dmitrij Kultasev Oct 03 '17 at 15:33
  • 2) If the previous comment did not help. Then try to create the function with parameters and in the script file do select * from function(@param1, @param2) and pass the parameters in the same way as from previous comment – Dmitrij Kultasev Oct 03 '17 at 15:34
  • @JohnAndrews why did you changed from SELECT statement to CTE? Try to do simple select (not CTE) and remove all semicolons (;) from the query. smf like DECLARE var1 =..., var2 = ... [new_line_without ;] select * FROM ... WHERE var1... – Dmitrij Kultasev Oct 03 '17 at 17:31

0 Answers0