Can not install stored procedure using sqlcmd

Question

Hi Experts,

I have few stored procedures that I am including in my setup and install them using sqlcmd.exe. I am using Sql server 2005 on sqlserver express database.

using the following cmd file to run the stored procedure:

Sqlcmd.exe -U <username> -P <passwrd> -S <server> -d <db> -o dbout.tmp -i dxCreateStatusHistoryLog.sql
type dbout.tmp >> dbout.log

I try to put my output in the dbout.log file but it is empty with no indication. stored procedure is as follows. If i try to run the stored procedure using sql server mgmt studio express, it installs the stored procedure on the database but not from the above batch file.

CREATE PROCEDURE dbo.dxCreateStatusHistoryLog
@UnitName nvarchar (50)=null,
@DeviceId nvarchar (50)=null,
@DevicePropId nvarchar(50)=null,
@Time datetime=null,
@Command nvarchar(50)=null
AS
set nocount on
BEGIN

 Declare @TimeSpan int 
 
 DECLARE @OldTime datetime  

 Select @OldTime = ActiveTime from dxLiveData Where (UnitId = @UnitName AND DeviceId = @DeviceId AND DevicePropId = @DevicePropId) order by ActiveTime
 
 Select @TimeSpan = DATEDIFF(ss,@OldTime,@Time)
 
 IF @TimeSpan > 0
 begin
  IF NOT EXISTS (Select * From dxHistoryData
   Where (UnitId = @UnitName AND DeviceId = @DeviceId AND DevicePropId = @DevicePropId
    AND ActiveTime = @OldTime AND DeactiveTime = @Time))
  BEGIN
   Insert into dxHistoryData (UnitId, DeviceId, DevicePropId, ActiveTime, DeactiveTime, TimeSpan, Value)
    Select UnitId, DeviceId, DevicePropId, ActiveTime, @Time, @TimeSpan, Value From dxEC4LiveData
    Where (UnitId = @UnitName AND ActiveTime = @OldTime AND DeviceId = @DeviceId AND DevicePropId = @DevicePropId) order by ActiveTime
  END
 END
 

 If @@ROWCOUNT > 0
  Delete From [dbo].dxLiveData
   Where (UnitId = @UnitName AND ActiveTime = @OldTime AND DeviceId = @DeviceId AND DevicePropId = @DevicePropId)
 

END
GO  

 

Any suggestions?

Thanks.

---

Maverick

Answer 1

Maverick,

Are you using the same user name and password in SQL Server Management Studio Express that you're using with sqlcmd? I'm assuming you are, but this would be important to verify. For example, if you're using windows authentication for SSMS but a user and password for sqlcmd, then the permissions could be completely different for each user.

Jason

Answer 2

Hi Jason,Thanks for your concern. I am using same. As in the cmd file I am installing around 10 stored procedures (above is just one example), out of which two are installed every time I run the cmd file but rest 8 stored procedures are not installed. I have no idea why :(

---

Maverick

Answer 3

Maverick,

If you're not seeing any errors, I would wonder if these procedures are getting installed in the database you expect. Is there any chance that you're specifying "USE [databasename]" in the script that is putting the procedures somewhere else? Or is there anything different about the naming convention of the procedures in the two that work and the two that don't (i.e. is the database name hardcoded in the fully qualified name possibly).

Also, you'll want to make sure that you'd actually *see* the errors if there were any. Why not put an obvious error in the script like select * from sdlkfjsdfkj (outside of the procedures themselves). See if you see that error in your output just to make sure.

Other than these ideas, it seems very strange that only two of the procedures would work.

Jason

Answer 4

Hi Jason, Well I am not using USE database in the script.

script is like this

@echo off
echo Creating database %3 on server %2
  
%1 -U %4 -P %5 -S %2 -d %3 -o dbout.tmp -i dxCreateStatusHistoryLog.sql
type dbout.tmp >> dbout.log

%1 -U %4 -P %5 -S %2 -d %3 -o dbout.tmp -i dxDeleteLogRead.sql
type dbout.tmp >> dbout.log

Last two stored procedures are created everytime and I get even error if the stored procedure is already available on the database.

But no response from other stored procedures.

The above script I am calling using this cmd file

MkDB "c:\Programme\Microsoft SQL Server\90\Tools\binn\sqlcmd.exe" "MYPC\MYSERVER" "MYDAB" "MYUSER" "duZdKKddS"

One of the procedure that works is:

CREATE PROCEDURE dxDeleteLogRead
@dName nvarchar (50)=null,
@dTime datetime=null,
@dCommandName nvarchar (50)=null
AS
 set nocount on 
 begin
  Delete From [dbo].dxLog Where (Name = @dName AND ActionDate = @dTime AND CommandName = @dCommandName)
  
 end
GO

......................

---

Maverick

Answer 5

OK, so each procedure is in it's own script?

I have a few ideas to try:

1. Put all of the procedures in a single script called MyMasterProcedureScript.sql (just as a test) and call that file once in your script:

%1 -U %4 -P %5 -S %2 -d %3 -o dbout.tmp -i MyMasterProcedureScript.sql
type dbout.tmp >> dbout.log

2. Try using start /wait for each call to sqlcmd. What if the log hasn't been written yet and you make the next call to sqlcmd? I'm not sure this would happen but it's worth a try. A different way of doing this would be to put a "pause" command between each call. Then you have a built-in delay that you control.

3. Put a PRINT 'STARTING SCRIPT [SCRIPTNAME]' line at the beginning of each script. Make sure you see this output in the log file that you're creating. If you don't, then that script didn't run.

Sorry I only have suggestions rather than answers, but I hope this helps!

Jason Roth

Answer 6

one more strange thing. If i add the non working stored procedure in my working stored procedure (copy paste in one file) then both stored procedure is created, but not in different single file.

---

Maverick

Answer 7

Hi Jason,

thanks for your reply. Well I created a single script and copied all stored procedure in it and it worked fine. Now all stored procedure are created without problem. now wondering whats problem with differnet scripts. Strange to me.

---

Maverick