Using Xp_cmdshell In A Trigger

Jul 20, 2005

I'm using xp_cmdshell to output a text file from a trigger like this

CREATE TRIGGER [CreateFile] ON tblApplications
FOR INSERT
AS

DECLARE @FirstName varchar(75)
DECLARE @LastName varchar(75)

Declare @strcmdshell varchar(150)
Declare @filename varchar(100)
Declare @filecontents varchar(150)

SELECT @FirstName = FirstName FROM INSERTED
SELECT @LastName = LastName FROM INSERTED
SELECT @filecontents = 'FirstName: ' + @FirstName+char(13)+'LastName: ' +
@LastName+char(13)+
SELECT @filename = 'c: est.txt'
SELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filename
exec master..xp_cmdshell @strcmdshell

When I concatenate @FirstName and @LastName(neither is NULL) and try to
insert a blank line with char(13) the command doesn't run. If I don't use
char(13) it works fine. Does this make sense? IS there any other way I can
format the output file.

Thanks

View 3 Replies


ADVERTISEMENT

Yet Another Trigger/xp_cmdshell Post

Apr 4, 2003

I can run the xp_cmdshell command with any other command until I put something like this is.

CREATE TRIGGER RunUpdateProgram ON [dbo].[UpdateQue]
FOR INSERT
AS
declare @MybatchID varchar(32),
@sendcommand varchar(32)

select @MybatchID = batchid from updateque
select @sendcommand = '"d:xanaduostarterprog.exe ' + @MybatchID + '"'
exec master..xp_cmdshell @sendcommand, no_output

The starterprog is setup to receive commandline input and then do its thing but everytime I try to add in a commandline to the xp_cmdshell command i get nothing.

The security is right and the program is right because i can run it until i try to add that last part "@MybatchID". If I take out the quotes then it runs the program but without the commandline input. I've tested the program manually and it correctly accepts commandlines that way.

Anyone got any ideas on what I've done wrong here.

View 1 Replies View Related

Xp_cmdshell From Within A Trigger On SQL 2000 SP4

Jan 19, 2007

I have a trigger that should be execute on each row insert and only ifappcode = 'I' and datasent = 0. It should execute a DTS package. TheDTS package by itself runs about 6 seconds. Trigger was createdsuccessfuly. When I try to insert a row my db hangs. I can see thatSPID on my db is hang by SPID from master db. It doesn't completeuntil I kill that SPID. Why is that?This is the trigger that I am trying to execute:CREATE TRIGGER myExportON ruExportFOR INSERTASdeclare @appcode varchar (10)select @appcode = appcode from ruexport where appcode = 'I' anddatesent = 0if @appcode = 'I'exec master..xp_cmdshell 'DTSRun /S "KOCL384017SQL" /N "Sys_Export" /G"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'goAny suggestions?

View 2 Replies View Related

Using Xp_cmdshell In INSERT Trigger: Bad Idea?

Jul 20, 2005

Whenever something is inserted to a given table, I want to run someshell commands using xp_cmdshell. Would it be a bad idea to put thisxp_cmdshell in the INSERT trigger of this table?I understand that when using xp_cmdshell, the sql thread in questionwaits until xp_cmdshell finishes what it's doing. Does this mean ifmy xp_cmdshell call takes 30 seconds, that nobody else can insert tothis table until my xp_cmdshell and rest of the insert trigger finishup?The alternative solution seems to be frequent polling of the table inquestion; while this isn't really a great solution it would seem tocircumvent a table lock brought about by the INSERT trigger.Thoughts?Joel

View 4 Replies View Related

Insert Trigger And Xp_cmdshell With Carriage Return Text

Jul 20, 2005

I have created the following trigger:CREATE TRIGGER [CreateFile] ON OutputTableFOR INSERTASDeclare @filename nvarchar(35)Declare @filecontents nvarchar(2000)Declare @strcmdshell varchar(150)SELECT @filecontents = OutputText FROM INSERTEDSELECT @filename = 'c:' + OutputFileName FROM INSERTEDSELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filenameexec master..xp_cmdshell @strcmdshellIt works fine as long as the column OutputText has no carriagereturns.I have used my Access2K front end to dump about ten lines of text intoOutputText, each line broken by vbcrlf so that OutputText looks like:line1testline2textline3textetc.The trigger won't fire with this text.Is there something I can do to remedy this?lq

View 4 Replies View Related

CLR-Based Trigger? Recursive Trigger? Common Table Expression?

Nov 14, 2006

Hey,

I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.

A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.

A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.

Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.

If you need the design or create script (table layout), please let me know.

Thanks.

View 4 Replies View Related

Trouble With Update Trigger Modifying Table Which Fired Trigger

Jul 20, 2005

Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View 1 Replies View Related

Generic Audit Trigger CLR C#(Works When The Trigger Is Attached To Any Table)

Dec 5, 2006

This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.

The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);

switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}


//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}

The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.

Comments and Suggestion will be much appreciated.

View 16 Replies View Related

DTS & Xp_cmdshell

May 15, 2001

Hello,

I am trying to create a directory containing the date and then copy all the files in the current directory to it.

If I run the T-SQL script found below within Query Analyser it works fine (directory is created and files are copied in to it).

But if I run it as SQL task within DTS, only the directory is created. The files are not copied in to it!?!

I thought it maybe a permissions problem, but the SQL Server/Agent account is a local administrator and has sysadmin role with SQL Server.

I would be grateful of any assistance in this problem.

Thanks in advance,
Chris.

SQL:

declare @directoryname varchar(200)
declare @doscmd varchar(255)

select @directoryname = 'e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char))

select @doscmd = 'mkdir ' + @directoryname

exec master..xp_cmdshell @doscmd

select @doscmd = 'copy e:Audit_table_archive*.* e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char)) + '*.*'

exec master..xp_cmdshell @doscmd

View 1 Replies View Related

Ftp Through Xp_cmdshell Help?

Jul 9, 2001

does anyone know how to execute a FTP command through sql server 7? I am creation a table and need to ftp the result set. I have it currently creation the table, turning it into a CSV txt file and placing it in a directory. I have to then manually ftp the txt file. I want to automate this process but I cant get the FTP command to execute throught the xp_cmdshell. It will work at the DOS prompt though so I know the syntax is correct. Any sugetsions would be appreciated.
-Nathan

View 2 Replies View Related

Xp_cmdshell

Aug 10, 2001

hi everybody
My requirement is, I have to transfer database backup files from one server to another server to take tape backup. Generally in my backup folder there will be 4days backups. I want to schedule a job so that after database backed up, today’s backup file only copied to the other server. I wrote following code, upto xp_cmdshell every thing is working fine.
But xp_cmdshell statement is giving error. I have to use variable value with sp_cmdshell. Please give me the solution for this.



declare @year1 as varchar(4),@month1 as varchar(2),@day1 varchar(2),@filename varchar(40)
set @year1=ltrim(str(year(getdate())))
if month(getdate())<10
set @month1='0'+ltrim(str(month(getdate())))
else
set @month1=ltrim(str(month(getdate())))
if day(getdate())-1 <10
set @day1='0'+ltrim(str(day(getdate())-1))
else
set @day1=ltrim(str(day(getdate())-1))

set @filename='EMPTest_db_'+ @year1+@month1+@day1+'*.bak'

xp_cmdshell @a



thanks
Keerthi

View 1 Replies View Related

Xp_cmdshell

Aug 17, 2001

I am trying to run a Visual Basic Script using the xp_cmdshell stored procedure. When I try to run the file, the MS Script Debugger application is started.

I run the script like this: "xp_cmdshell 'D:ScriptFilesSpaceMail.vbs'"

I can run it from the DOS prompt successfully, but not from Query Analyzer. Does anyone have any suggestions on how to prevent the MS Script Debugger from running?

Chris

View 1 Replies View Related

Xp_cmdshell

Sep 7, 2001

Hi,

I am trying to run bcp with xp_cmdshell inside a trigger. Whenver I update table the server is hanging. It creates the file in specified location but of ZERO size and I cannot delete it unless I stop SQL server service.

The smae code runs from a stored procedure without any problem.

Can you pl tell me if there are any LIMITATIONS with xp_cmdshell and Trigger.

Thanks

sekhar

View 1 Replies View Related

Xp_cmdshell

Apr 14, 2000

Hi!!!

Can someone help me?
How can I get the result of following execution in some stored procedure, and work with it:

exec xp_cmdshell 'dir c:'

Thanx in advance
Laert

View 1 Replies View Related

Using Xp_cmdshell

Nov 1, 2000

Hi,
while using XP_cmdshell for renaming a file , can I concatenate the current date to the file?
Ramam

View 1 Replies View Related

Xp_cmdshell

Nov 28, 2000

Hi,

What is best way to compare two files and get the most recent one using xp_cmdshell?

Thanks

TT

View 1 Replies View Related

Xp_cmdshell

Nov 28, 2000

Hi,

I am trying to execute this command.
use master
exec xp_cmdshell..'dir estserverest_dataInventory_Files*.txt /b'

I am getting the following error..

output
------------------------------------------------------------------------------Logon failure: unknown user name or bad password.

I am logged on as 'sa' for that server.

Any suggestion on how to resolve this?

Thanks in advance.

View 1 Replies View Related

Xp_cmdshell

Mar 8, 2000

Hai ,

I beleive this is simple question but I have problem using this command

I was trying to delete contents of a temp folder thru TSQL.
Correct me if wrong
For example
xp_cmdshell " del c:emp*.*"
This is leading me to prompt 'Are you sure to delete Y/N'.
How do I write a command to say Y.
I want to schedule this every week. Is there any other way to do this task
like using AT command ,etc.

Thank you in advance
Surya

View 1 Replies View Related

Xp_cmdshell

Mar 1, 2000

Hi !

I am trying to use xp_cmdshell
---
xp_cmdshell 'dir abcc$'

---
where abc is host name
and C$ is shared name

I get the following error message
"Logon failure: unknown user name or bad password."
I execute the same command at the dos prompt , I get the directory listing

Any suggestions??

Thanks.
KMM

View 2 Replies View Related

Using Xp_cmdshell To Run An .EXE

Jul 6, 2000

Hi Guys,
i am trying to run an .exe file from queryanalyzer for my testing.I am using the following command
exec master..xp_Cmdshell 'C:extEXEproject1.exe -SCHEETAH -Uvijay -Pbell'
CHEETAH is my server name
User :vijay
pwd:bell
the problem is its running forever in QueryAnalyzer,when i tried to run the
same thing from dosprompt its executing fine.
Please help me in this
thanks for any help

View 1 Replies View Related

Xp_cmdshell

Jun 6, 2003

SQL 7.0
I have given a non sa user permissions to run xp_cmdshell via his NT logon. When he runs it and does a "dir" of the server it works. When he trys to do a "dir" of his own machine it comes up with "Logon failure unknown user name or bad password".
SQL Agent is running under a domain admin account. I have placed SQLAgentCmdExec in the servers local administrators group. I have de-selected the option from SQLAgent propertiesJob System which restricts non sa users executing CmdExec stuff. I did attempt to Reset Proxy Account and Reset Proxy Password. When I clicked on these it just gave me message that the account and password had successfully been reset without asking me for username, password or domain.

View 5 Replies View Related

Xp_cmdshell

Sep 24, 2003

Hello, friends,
This is probably not a question for DBA forum, but may be someone knows the answer.
In the stored procedure I'm using xp_cmdshell command to copy file from one dir to another.
How I can receive any indication what the process succeded?
xp_cmdshell return 1 or 0 if the stored procedure was invoked,
I would like to receive an indication that the file was copied.

View 2 Replies View Related

XP_CMDSHELL

Aug 22, 2001

How to create directory and file using xp_cmdshell..?

Thanks,
Harish

View 1 Replies View Related

Xp_cmdshell In 6.5.7.0

May 12, 1999

I tried to use the external procddure - xp_cmdshell - to copy a file from a local drive to a network drive, but failed. Yet the attempt succeeded if I tried to copy a file from one place to another in local drive.

It seems that SQL Server 6.5/7.0 did not recognize any network drive.
I greatly appreciate your help/assistance and/or any hint(s). Thanks a lot

View 1 Replies View Related

Xp_cmdshell

Nov 2, 1998

Hi there!
Is it possible to supply variable for the xp_cmdshell parameter?
here's an example:

declare @@okay char(20)
select @@okay = "dir c:mssqlinn"
exec master..xp_cmdshell @@okay

but this doesn't work. is there any workaround on this? instead
of writing a new extended sp to cater for this.

thanks in advance.

dion

View 6 Replies View Related

Xp_cmdshell

Apr 13, 2005

I was able to run DTS using xp_cmdshell. In my dts I have 3 global variables which I would like to pass. Can I pass a variable values using xp_cmdshell?
xp_cmdshell 'DTSRUN /S ServerName /U user/P pass /N "DTS_Name" '

View 3 Replies View Related

Xp_cmdshell Help

Jun 15, 2004

Hi,

I need to do FTP using a FTP script...here is the syntax that i am using

ftp -s:c: empftpcmd.txt > c: emplog.txt

when i run it thru the command line, log.txt file has the following
==================================================
ftp> Connected to 9.999.99.999.

open 9.999.99.999
220-YYYY1 XXX FTP CS V1R2 at XXXXXX.XXXXXX.XXX.COM, 16:55:07 on 2004-06-15.
220 Connection will close if idle for more than 5 minutes.
User (9.999.99.999:(none)):
331 Send password please.

230 USERNAME is logged on. Working directory is "USERNAME.".
ftp> Invalid command.

ftp>
ascii
200 Representation type is Ascii NonPrint
ftp> get 'XXX.YYY.ZZZ.ACCOUNT' c: empXXX.YYY.ZZZ.ACCOUNT.txt
200 Port request OK.
125 Sending data set XXX.YYY.ZZZ.ACCOUNT FIXrecfm 22
250 Transfer completed successfully.
ftp: 1172544 bytes received in 3.31Seconds 353.82Kbytes/sec.

ftp> quit
221 Quit command received. Goodbye.
=================================================

but when i run it from inside a query...using

declare @cmd varchar(1000)
select @cmd = 'ftp -s:c: empftpcmd.txt > c: emplog.txt'
exec master..xp_cmdshell @cmd

the log.txt file has
=================================================
User (3.172.28.153:(none)): open 3.172.28.153
Invalid command.




ascii
get XXX.YYY.ZZZ.ACCOUNT' c: empXXX.YYY.ZZZ.ACCOUNT.txt
quit
=================================================

why is this differnece? what can i do so that i get full details in the log file.
please help.

thanks
rohit

View 9 Replies View Related

Xp_cmdshell

Apr 27, 2007

Where should i read up on using
dos commands in conjunction with the xp_cmdshell extended stored procedure..
I need to manipulate file names....
move copy take off ... parts of the filename and replace... them...
etc... any good sites or topics... so that I can learn how to do this...
thanks...
jonathan

View 13 Replies View Related

Xp_cmdshell

Oct 4, 2007

Hi All,

I've recently installed SQL Server 2005 and now trying to enable xp_cmdshell through SQL Server 2005 Surface Area Configuration. However, I am getting the following error:

User does not have permission to perform this action.
You do not have permission to run the RECONFIGURE statement.

I am logged in as "sa".

How can I make it work?

Thanks.

View 4 Replies View Related

Xp_cmdshell

Feb 25, 2004

how to enable xp_cmdshell

View 2 Replies View Related

Xp_cmdshell And Ftp

Mar 10, 2004

Basic problem:

When xp_cmdshell interacts with FTP it doesn't log the FTP return codes.

History:
I found Nigel Rivett's T-SQL FTP scripts http://www.nigelrivett.net/ and concatenated them together with some modifications so I could have a process that actually errors when an FTP fails.

The process FTP's a file, disconnects, and then reconnects to do a dir and then select from the returned listing to see if the file name exists.

I wanted to review the initial log from the put and check for a valid FTP return code of 250 that says "Transfer completed successfully"

Log Snippets:

A normal FTP log shows:
ftp> put foo 'mainframe.node.structure.foo'
200 Port request OK.
125 Storing data set MAINFRAME.NODE.STRUCTURE.FOO
250 Transfer completed successfully.
ftp: 7972888 bytes sent in 99.63Seconds 80.03Kbytes/sec.
ftp> quit
221 Quit command received. Goodbye.

xp_cmdshell log shows (with FTP debug on) shows:
7 put foo 'mainframe.node.structure.foo'
8 PORT 10,99,11,16,7,30
9 ---> STOR MAINFRAME.NODE.STRUCTURE.FOO
10 ---> quit
11 QUIT
12 (null)

As you can see, the return codes and some other messages are missing.

I've messed around with various options and can't seem to get any return codes in the log at all.

Any ideas?

View 1 Replies View Related

Xp_cmdshell

Apr 1, 2004

I need to kill some user session that i get from below query output

xp_cmdshell 'netstat -an | find /i "est"'

output: -

TCP 0.0.0.47:1099 0.0.0.39:139 ESTABLISHED
TCP 0.0.0.47:1433 0.0.0.104:2961 ESTABLISHED
TCP 0.0.0.47:1433 0.0.0.104:3012 ESTABLISHED
TCP 0.0.0.47:1433 0.0.0.39:2915 ESTABLISHED
TCP 0.0.0.47:1433 0.0.0.39:3106 ESTABLISHED
TCP 0.0.0.47:2007 0.0.0.26:1487 ESTABLISHED
TCP 0.0.0.47:2007 0.0.0.79:1139 ESTABLISHED

do we have any command to kill the connected session to my sql except kill spid.

ex: i want to kill the session of IP 0.0.0.79 from my sql server.

thanks in advance

View 3 Replies View Related

Is There Any Way Better Than XP_CMDSHELL?

May 31, 2004

Hi ..
I want to Write in files or read from files
for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt .
and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??

Is there any way better than XP_CMDSHELL for writing in or reading from MyFile.txt ??

thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved