Running xp_cmdshell

Hi all,
Pls let me know how to run xp_cmdshell command for a user who does not belong to sysadmin role.
Rgds
Srinivas varanasiHi all,
Pls let me know how to run xp_cmdshell command for a user who does not belong to sysadmin role.
Rgds
Srinivas varanasi
the user should have execute permissions on the xtended procedure xp_cmdshell to run it .

Running trigger t-sql as the sa

How does one run the following command in the context of the sa account while logged in as themselves (not sa). The "SQL Authentication" user does not have access to master.

EXECUTE master.dbo.xp_sendmail @.recipients = 'hi@.hotmail.ca', @.subject = 'Script fired!', @.message = 'Hi'How does one run the following command in the context of the sa account while logged in as themselves (not sa). The "SQL Authentication" user does not have access to master.

EXECUTE master.dbo.xp_sendmail @.recipients = 'hi@.hotmail.ca', @.subject = 'Script fired!', @.message = 'Hi'

sql2k does not support runas. You have to wait for yukon.

Running Transac SQL in Command Line

Hi all

I currently have a problem where I need to create a backup from MSSQL, via a command line, ideally from a batch script. This is on a PC. Each machine has SQL Server 2000 on.

I just need to know the code that dos will tell it to run the sql statement. Ideally the command will also give the directory to place the backup.

Regards MiloJFunny that you should ask! There is a KB article just for you, #241937 (http://support.microsoft.com/default.aspx?scid=kb;en-us;241397) that describes in detail just what you want.

-PatP|||Pat, that's perfect you are 100% on the money, much obliged

Running the Package input file .xmla

Hi,

Is there a way to run the package input .xmla file to the command prompt instead of running it in management studio?

cherriesh

Try the ascmd command-line utility, part of Analysis Services Administrative Samples.

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

sql

Running the exec command on the second machine...

I have a query to execute. The query is :-

exec master.dbo.xp_fileexist @.nFilePath

This i can execute on the current machine.

But how do i run this one on another machine.
I took the machinename as a parameter and executed like :-

exec @.nMachinename.master.dbo.xp_fileexist @.nFilePath

But the exec command is not taking this...

Giving me errors.

Now how can i give the second machines name as a paramter and run dynamically??

Thanks,
Sandu.not sure about this but try using linked server.|||Okies..

I got it...

I have to set the value into a variable of the above mentioned query and then execute the variable...

Anyway thanks...

--Sandu.|||try

declare @.sql nvarchar(1000)
select @.sql = 'exec ' + @.nMachinename + '.master.dbo.xp_fileexist ''' + @.nFilePath + ''''
exec (@.sql)

Running SSIS from Command Line w/ SQL Agent

Hi Everyone!

I'm trying to run have SQL Server Agent excute an SSIS package from the command line and I keep recieving an error message. I will assume that I have miss typed something. Can someone validate that the execute line looks workable?

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /F "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" /CONNECTION "APLUS70F70.AKIN";"\"uid=AKIN;Dsn=APLUS70F70;\"" /CONNECTION "CRMPSQL.IBT_Aplus";"\"Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Thanks in advance!

Anthony Akin

Anthony, the " directly after dtexec.exe looks rogue to me.

-Jamie

|||

Jamie,

Thanks for the quick reply! A little background might help! I'm running Windows Server 2003 x64 and SQL 2005. We oringinally were recieving errors because of the BIDS trying to run the 64 bit ODBC's and all the information on the web said to run it from the command line. I can run the package listed below from the command line, but I cannot use SQL Server Agent to running this command. Any thoughts or suggestions would be greatly appreciated!

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"/FILE "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" /CONNECTION "APLUS70F70.AKIN";"\"uid=AKIN;Dsn=APLUS70F70;\"" /CONNECTION "CRMPSQL.IBT_Aplus";"\"Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Thanks,

Anthony Akin

|||

Sorry yeah you're right. The command-line syntax looks good.

What's the error message? usually if something works from dtexec.exe but not SQL Agent it will be a permissions issue.

-Jamie

|||

Jamie,

Here is what is in the log file. Is it because I'm calling out the 32bit dtexec with a 64bit program?

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
04/17/2007 13:00:08,Customer_Master,Error,0,CRMPSQL,Customer_Master,(Job outcome),,The job failed. The Job was invoked by User IBTINC\Administrator. The last step to run was step 1 (CustomerMaster).,00:00:00,0,0,Anthony Akin,,,0
04/17/2007 13:00:08,Customer_Master,Error,1,CRMPSQL,Customer_Master,CustomerMaster,,Executed as user: CRMPSQL\SYSTEM. The step failed.,00:00:00,0,0,,,,0
04/17/2007 13:00:08,Customer_Master,Unknown,1,CRMPSQL,Customer_Master,CustomerMaster,,Microsoft (R) SQL Server Execute Package Utility<nl/>Version 9.00.3042.00 for 32-bit<nl/>Copyright (C) Microsoft Corp 1984-2005. All rights reserved.<nl/><nl/>Option "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" is not valid.,00:00:00,0,0,,,,0

Thanks

Anthony Akin

|||

No idea. You kinda need to get hold of the output from dtexec.exe

You should also try executing using dtexec.exe from the command-line as the same user urnning SQL Server Agent.

-Jamie

|||

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

|||

A.Akin wrote:

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

I think you can, yeah. I'm not sure, I'm no expert on SQL Agent.

-Jamie

|||

Jamie,

I decided on build .bat files instead of messing around with SQL server agent. Thanks for the input today!

Anthony Akin

|||

A.Akin wrote:

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

It may be too late to help you on this, but for future reference - you can run the command prompt as a different user by right-clicking on the short-cut (usually located under Accessories) and choosing Run As...

Running SQL2005 profiler from an SQLAgent job

Hi,
I would like to run a certain profiler template against a server on a
regular basis. I can run profiler90 from the Command Prompt and it starts
creating the trace file. Can I run profiler90 from within an SQLAgent job? I
have tried and it did not run.
Thanks
Chris
Hi Chris,
You have to use the trace procedures. An easy way to get started, is to
script the trace from file > export menu.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>
|||Hi
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>
|||Thanks Uri and Jason. I will look at the url link and go from there.
Chris
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV5GL55nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> Hi
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
>

Running SQL2005 profiler from an SQLAgent job

Hi,
I would like to run a certain profiler template against a server on a
regular basis. I can run profiler90 from the Command Prompt and it starts
creating the trace file. Can I run profiler90 from within an SQLAgent job? I
have tried and it did not run.
Thanks
ChrisHi Chris,
You have to use the trace procedures. An easy way to get started, is to
script the trace from file > export menu.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>|||Hi
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I would like to run a certain profiler template against a server on a
> regular basis. I can run profiler90 from the Command Prompt and it starts
> creating the trace file. Can I run profiler90 from within an SQLAgent job?
> I have tried and it did not run.
> Thanks
> Chris
>|||Thanks Uri and Jason. I will look at the url link and go from there.
Chris
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uV5GL55nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> Hi
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e3dSWiwnIHA.4536@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I would like to run a certain profiler template against a server on a
>> regular basis. I can run profiler90 from the Command Prompt and it starts
>> creating the trace file. Can I run profiler90 from within an SQLAgent
>> job? I have tried and it did not run.
>> Thanks
>> Chris
>

Running SQL scripts from Command Prompt

I have a requirment, when a user logs in to the pc, I want to run some SQL scripts which is been saved as a stored procedure on another pc. Could this be possible. I know in oracle you could do it using SQLLDR. Is there any way to do it in SQL?

Please advice. Any help is greatly appreciated.

If you are using SQL 2000, check out OSQL utility. If you have plans to upgrade to 2005 you might also want to check out SQLCMD. OSQL will be removed in later versions.|||I have one more problem, I can only use the osql on the server. I want to run this on the client pc which conatins an odbc connection to the sql server|||Then you need to install SQL Server client tools from the SQL Server installation PC on the client pc. You can choose to install client tool only without installing database engine.

Running Some script files

Hello, I have some script FILES (.SQL FILES) on a folder, and I want to run them using command line, or a kind of utility or tool.

Sorry for the example but I used to do @.c:\myFile.sql in Oracle, that's exactly what I need for SQL SERVER 2000

Thank you so much !!

You can use ISQL or OSQL in this case, refer to the SQL books online and following links for further information:

http://www.databasejournal.com/features/mssql/article.php/10894_3313201_2

http://www.idevelopment.info/data/MSSQL/DBA_tips/Database_Administration/DBA_1.shtml

|||

Satya SKJ wrote:

You can use ISQL or OSQL in this case, refer to the SQL books online and following links for further information:

http://www.databasejournal.com/features/mssql/article.php/10894_3313201_2

http://www.idevelopment.info/data/MSSQL/DBA_tips/Database_Administration/DBA_1.shtml

Your Answer was so useful,

Thanks a lot !!!