Executing SP From a Batch file

I was wondering how I can create a batch file that call a store procedure an
d
put the output on a file.Silver wrote:
> I was wondering how I can create a batch file that call a store
> procedure and put the output on a file.
Try osql.exe - the command-line options are documented in Books Online or by
typing osql /? at the command line.
John.|||Hi,
Call OSQL inside the command prompt
Entries inside the batch file will be
OSQL -Usa -Ppassword -S Servername -d Dbname -Qprocedurename -oc:\output.log
Thanks
Hari
SQL Server MVP
"Silver" <Silver@.discussions.microsoft.com> wrote in message
news:341FE12A-19E6-4A8B-8003-558797A637BF@.microsoft.com...
>I was wondering how I can create a batch file that call a store procedure
>and
> put the output on a file.

Executing a store procedure

Hi,

I Have the next problem:

I have a store procedure in my db which is called from an aplication (developed in Java). That sp contains a cursor which updates regs from a table. After calling from the java application I notice that some regs of the cursor have not entered in it, i mean if the sp executes 200 iterations in the cursor only 150 have worked. But all the iterations enter when i call the sp from the query analyzer!!! and it takes more time too.

Does anybody know something about it???Hi,

I Have the next problem:

I have a store procedure in my db which is called from an aplication (developed in Java). That sp contains a cursor which updates regs from a table. After calling from the java application I notice that some regs of the cursor have not entered in it, i mean if the sp executes 200 iterations in the cursor only 150 have worked. But all the iterations enter when i call the sp from the query analyzer!!! and it takes more time too.

Does anybody know something about it???

I've never heard of Java having this effect on anything and find it rather hard to believe. Have you tried running profiler and seeing what calls are being made. You should do this and compare the results of one being ran in QA and one being ran with your Java Application.

Also, you should really get rid of the cursor. Look on SQLTeam.com (search the forums) for cursors. You will find many set-based and while loop methods to avoid using a cursor.

Execute xp_cmdshell from store procedure (called from aspx)

Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:
"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.
MSSQLServer and SQLServerAgent Services
Act as part of the Operating System.
Increase Quotas.
Replace a process level token.
Log on as a batch job."

The above quote was from the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264155

The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:
------------------
EXEC master..xp_cmdshell 'dir d:\BT_import\Data\sales_option_price_report.csv'

Thanks for your help
ehx5The "rights" refer to the user account that is running the extended stored proc, namely the user account for teh MSSQLServer service and the SQLServerAgent service. Once you know who they are you can go to the security policy editor and grant them the said rights. However, this is *VERY* dangerous and I really would recommend that you do NOT take this path. If someone gets onto to DB the xp_xmdShell 'Format c'. You get the idea. Much, much better to let some Business logic component sort this out, or even your own extended proc.

Execute Script from File?

how to execute a script from a file in a store procedure?

example:

-- (* UPD: 08/07/2003 *)
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'pr_ExecScriptFF' AND type = 'P')
DROP PROCEDURE pr_ExecScriptFF
GO
--Ejecutar script desde un archivo.
CREATE PROCEDURE pr_ExecScriptFF @.File varchar(500)
AS
BEGIN
EXEC ??sp_executesql?? @.File
END
/* EJEMPLO
DECLARE @.archivo varchar(500)
Set @.archivo='c:\MyData\HO_ACT200307080924.sql'
EXECUTE pr_ExecScriptFF @.archivo
*/

Note:
HO_ACT200307080924.sql:
--Inserts para la Tabla: HO_Energia
INSERT HO_Energia VALUES(171,'HO_1','25/06/2003','HO_2',1,1081783,1085080,1085875,1087590,108 8445,1089820,NULL,1650,6387)
INSERT HO_Energia VALUES(172,'HO_1','14/06/2003','HO_1',1,993418,993418,993418,993418,993418, 1000952,NULL,0,7534)
--Inserts para la Tabla: HO_RegHorario
INSERT HO_RegHorario VALUES(7683,'HO_1','8','25/06/2003 19:00:00',NULL)
INSERT HO_RegHorario VALUES(7684,'HO_2','8','25/06/2003 19:00:00',NULL)

etc...

:> I look in MSDN library for a sp_ but nothing...
*.sql may have 10000 lines.

tnx 4 help me.try using osql or isql for running the script file. this can be invoked by using master..xp_cmdshell|||esto puede ejecutar el script de un archivo de texto,
el ejemplo corre desde VB

cnn.Execute "exec master..xp_cmdshell 'osql -r -U sa -P -S " & PvStrServidor & " -d " & PvStrNombreBD & " -i " & App.path & "\Archivo.SQL'"

Execute Package Task using SSIS Package Store package

From the Execute Package Task Editor, what info do you have to enter to
reference a SSIS Package Store package?
For the Location, I've tried both "SQL Server" and "File" and nothing
works except selecting the actual location from the "File" option.
For the Connection, I've tried the default "Native OLE DB\..." and "..OLE DB
Provider for DTS.." as Provider with no result.
For PackageName, I've tried various combination "\File System\PackageName",
"PackageName", etc... and nothing works.

I'm assuming this task allow for the 3 different ways to store the package (SQL Server/MSDB, File System, Package Store (hybrid)).

Any ideas!!!?

The documentation for the task here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/042d4ec0-0668-401c-bb3a-a25fe2602eac.htm

says that the package can originate only from the File System or MSDB.

You could however use DTExec to fire the package with the /D[ts] package_path
parameter
Allan|||Remember as well that all the package store is the only the logical container for MSDB and the folder location specified in MsDtsSrvr.ini. The thing about it is that the service is aware of this location.
Allan|||The problem with the Package Store is that it is both or "managed" File System. I think someone has submitted a bug report on my behalf for this. Thanks.