MSSQL 2014 altered procedure is not executed just altered

Question

This is my stored procedure that works fine in MSSQL 2005 express edition but in MSSQl 2014 express edition where I want to migrate it is not executed, ie files are there but with 0 bytes! Code in cmd file that call procedure is:

 cd C:\NI_SQLscripts
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE" -S ATLOCATION-P-VM -E -d BazneStanice -b -Q Svi_NI -o Rezultat.txt

and the procedure is:

USE [BazneStanice]
GO
/****** Object:  StoredProcedure [dbo].[Svi_NI]    Script Date: 5/11/2017 4:32:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Svi_NI]
as


Declare @xmlsvi xml;

Declare @strsvi nvarchar(4000);
Declare @cmdsvi nvarchar(4000);
Declare @dan nvarchar(20);
Declare @sat nvarchar(20);

truncate table Upiti_NI_XML;

Set @xmlsvi=
(select 
LOKime as Name,
sifra0 as Location_Code,
adresa as Address,
Gsirina as Coordinate_WGS84_Latitude,
Gduzina as Coordinate_WGS84_Longitude,
opstina as Municipality,
odrzava0 as Maintance,
grozd as cluster,
dolazak as Access_info_and_contact,
kolokacija as colocation,
vlasnik as Owner,
druginazivi as Alias,
zemlja as Country,
vaznostbs as PriorityBS,
vaznostpr as PriorityTransport,
napomena0 as Description
from lokacije where stanje='koriscenje'
FOR XML PATH ('Location'), ROOT ('Locations'), ELEMENTS);

Set @dan=(SELECT convert(varchar, getdate(), 112));
Set @sat=(select replace(Convert (varchar(8),GetDate(), 108),':',''));

insert into Upiti_NI_XML (upit, ime_upita, dan, sat) values (@xmlsvi, 'locations_NI', @dan, @sat );
Set @strsvi= 'select upit from [BazneStanice].[dbo].Upiti_NI_XML where ime_upita=' + '''locations_NI''';
Set @cmdsvi='bcp "' + @strsvi + '"' + ' queryout "C:\NI_Location_Data\locations.xml" -T  -c  -S "ATLOCATION-P-VM"';
exec xp_cmdshell @cmdsvi;

When I execute "manually" on SQL server stored procedure Svi_NI without that part "alter procedure" everything is ok. I have renamed bcp.exe in Sybase client that I have installed on same serverWhat I am missing here? Any help will be apperciated!

What are you executing? What does `ALTER` have to do with it? Execute means that you write `EXEC [dbo].[Svi_NI]`. That was the same with SQL Server 2005. What you posted is the *definition* of a stored procedure, not an execution script. — Panagiotis Kanavos, May 12 '17 at 12:23
As for what may be wrong, that stored procedure is doing a lot of very ugly things like creatingi statements with concatenation and running `cmd` statements. It's *very* easy to get something wrong, eg miss a quote or slash. `cmd` is disabled by default too, for security reasons. — Panagiotis Kanavos, May 12 '17 at 12:27
yes, I know that part is a bit ugly, but it works on old server and I just copied it to the new server, so missing quote can't be the problem. Svi_NI is stored procedure and when I have to modify procedure I just execute it. — Bozur Vujicic, May 12 '17 at 12:42
Of course it can. What changed is the server and the accounts. Possibly the data too. The language hasn't changed. Debug the stored procedure in SSMS to see what happens at each statement and what the final variable is. Does it make sense? — Panagiotis Kanavos, May 12 '17 at 12:43
Also note that `@strvi` is created from concatenating two *constants*. Why? DId you really want to write ` 'select upit from [BazneStanice].[dbo].Upiti_NI_XML where ime_upita=''locations_NI'''`? Why didn't you write an `INSERT INTO ... SELECT FROM ... XML PATH..` then ? The entire bcp command line could be just a single `.cmd` batch file so you wouldn't have to perform so many string concatenations — Panagiotis Kanavos, May 12 '17 at 12:48
Wait - what does ` I have renamed bcp.exe in Sybase client that I have installed` mean? What does Sybase have to do with SQL Server???? — Panagiotis Kanavos, May 12 '17 at 12:49
When I select code without "alter procedure" file is filled with data and transferred. So there is no mistake in code. But when cmd file calls procedure it is not executed ie file is empty. In SQL 2005 file is filled with same command and same script. Maybe I shoud create procedure without that part "alter procedure" but it is helpful when you need to change procedure — Bozur Vujicic, May 12 '17 at 12:50
You can debug the stored procedure in SSMS to see what is actually going on - hint: you aren't checking the return value of `xp_cmdshell`. Or you can simplify this, even convert it to a single batch. — Panagiotis Kanavos, May 12 '17 at 12:52
When you have installed Sybase client you have one additional bcp.exe file. So when you call bcp there may be a problem ... I found that here: http://stackoverflow.com/questions/42470891/sybase-bcp-vs-ms-sql-bcp — Bozur Vujicic, May 12 '17 at 12:53
Your problem is in executing xp_cmdshell, not "my proc does not execute", right? I mean, if you add print 'test' in your proc just before exec cmdshell, it will bi print? Try to exec xp_cmdshell 'whoami' in the query window and try to execute xp_cmdshell 'whoami' from your procedure and compare the account returned — sepupic, May 12 '17 at 12:56
This is not a programming question and hence out of scope for StackOverflow. You should probably ask at `dba.stackexchange.com` but be prepared to receive the same answer - don't use unrelated tools — Panagiotis Kanavos, May 12 '17 at 12:57
no, when I execute script without that "alter procedure" xp_cmdshell sends file so code is correct. I think the main question is why procedure can't be trigerred automatically. I will create new procedure without that part.and tell you what is the result — Bozur Vujicic, May 12 '17 at 13:11
the problem was I didn't confirmed "run with highest privileges" :( thank you for your support! — Bozur Vujicic, May 15 '17 at 11:24

score 0 · Answer 1 · answered May 12 '17 at 13:54

You can first create (or alter) a procedure.
Then after this is done, you can EXECUTE it without having to put the whole code again just the procedure name.

Solution

Setup

CREATE PROCEDURE [dbo].[Svi_NI]
...

Modify your setup

ALTER PROCEDURE [dbo].[Svi_NI]
...

Execution

EXEC [dbo].[Svi_NI]

References

CREATE PROCEDURE
ALTER PROCEDURE
EXECUTE