0

I have a stored procedure which runs as a scheduled job on SQL Server 2008, and I am trying to migrate this to SQL Server 2014.

The procedure generates html and populates the html with values from a local db. The scheduled job runs this proc and saves the html file.

osql -U sa -P mypassword -Q "report.dbo.sp_mysp" -o c:\temp\web\myfile.html

The stored procedure runs fine on SQL Server 2014 in SSMS, and I see a print with html markup and values from the db.

My problem is that when I schedule the job using the OSQL command as above, the html file is generated and saved OK, but is missing the values from the db.

Is anyone aware of any changes in osql that could be the issue, or new restrictions on the sa user?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MarcKirby
  • 15
  • 5
  • Have you tried substituting SQLCMD for OSQL? The parameters should be the same. Are you saying the file is empty or is there an issue with the content? Note that OSQL has been deprecated since SQL 2000. – Dan Guzman Jul 13 '15 at 01:56
  • Thanks for the reply Dan - yes the file has the html markup only, without the required values from the db. Changing to SQLCMD had no effect. When I run the Stored Procedure directly I get both markup and values from db. – MarcKirby Jul 13 '15 at 02:34
  • Are you saying this is returning correct results when run in SSMS, but incorrect when run in OSQL/SQLCMD? – Greg Jul 13 '15 at 02:40
  • Correct Greg. If I run the procedure in SSMS it outputs fine using PRINT and will return the html and the values from the db. – MarcKirby Jul 13 '15 at 02:59
  • But in OSQL all I get is the html file being created. The html code generated by the procedure is present, but without the values from the query in the stored procedure. – MarcKirby Jul 13 '15 at 03:09
  • @MarcKirby, can you post an example of the proc that demonstrates the problem? – Dan Guzman Jul 13 '15 at 11:57
  • i will bet it is a permissions thing. When you log into SSMS using sa login, do you get same results? – Greg Jul 13 '15 at 13:34

1 Answers1

0

OK this one was a bug. I originally ported the sp's to the new server by using the built in "Create To.. File" function within SSMS, and then opened the file and ran on the new server to re-create.

When I created from scratch and simply copied and pasted the query, it all works fine.

MarcKirby
  • 15
  • 5