Error executing xp_cmdshell from within a SQL task in a DTS package...

Hello

I am trying to execute xp_cmdshell' from within a DTS package that
was created by another person. When I try to execute that SQL Task'
selectively from within the package, I get the following error
message:

Error Title: Package Error
Error Details:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: xpsql.cpp: Error 87 from GetProxyAccount on line
604

Why is this error message popping up? When I create a new package
(myself) and create an exact same SQL task as above and run it, the
SQL task runs fine.

Appreciate any help / feedback.

Thanks in Advance
Jagannathan SanthanamWhen a sysadmin role member executes xp_cmdshell, it runs under the
security context of the SQL Server service account. However, when a
non-sysadmin user executes the proc, it runs under the SQL Agent proxy
account.

It looks like there is an issue with your configuration of the proxy
account and/or SQL Server service account security. Enterprise Manager
automatically assigns the necessary rights to these accounts when these
are specified or changed via the GUI. Alternatively, you can manually
assign the following Windows rights to the accounts.

<Excerpt
href="http://support.microsoft.com/default.aspx?scid=kb;en-us;264155"
MSSQLServer and SQLServerAgent Services
- Act as part of the Operating System.
- Increase Quotas.
- Replace a process level token.
- Log on as a batch job.
SQLAgentCmdExec Account
- Log on as a batch job.

NOTE: You must restart the entire server, not just the SQL Services, in
order for any changes made to user rights permissions to take effect.

</Excerpt
--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Jagannathan Santhanam" <jags_32@.yahoo.com> wrote in message
news:605df08e.0311170307.39d9b1f1@.posting.google.c om...
> Hello
> I am trying to execute 'xp_cmdshell' from within a DTS package that
> was created by another person. When I try to execute that 'SQL Task'
> selectively from within the package, I get the following error
> message:
> Error Title: Package Error
> Error Details:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: xpsql.cpp: Error 87 from GetProxyAccount on line
> 604
>
> Why is this error message popping up? When I create a new package
> (myself) and create an exact same SQL task as above and run it, the
> SQL task runs fine.
> Appreciate any help / feedback.
> Thanks in Advance
> Jagannathan Santhanam

Error executing SQL Script from VB:NET

Hi.

I have a complete script from a Database.

But when I try to execute this script (script.txt) from VB.NET shows me many errors. Basically, the special caracters (vbreturn, vbtab, etc.)

How execute this script or format the text to generate the database without problems?

Thanks 4all

IF it is a SQL script, you need a SQL interpreter to run the script.

You would use either OSQL.exe or SQLCmd.exe. (Refer to Books Online for complete syntax details.)

OSQL -S servername -U username -P password -i Scriptfile

Similar command line parameters for SQLCmd.exe

|||You could also tell us what the errors are, what VB object you are trying to use, etc. There are lots of different reasons why this won't work, but I am sure that it could be made to work. Using sqlcmd, osql, or isql would be the easiest way to go, (it changes from one version of SQL Server to the next for the past few versions but SQLCMD is just a .NET program running your batches, so we can make it happen if you will describe more about your issues.

error executing assemblie

hellow i execute assemblie in sql server, assemblie opens up file and writes some information, when i run that esemblie i get the follwing message

An error occured: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

thanks for any ideas

i found solution myself

solution is tu make your assemblie to have external access, but now other error occured when the assemblie is trying to write to file

An error occured: Access to the path 'C:\log.txt' is denied.

(1 row(s) affected)

|||When SQL Server executes something that goes outside of SQL (i.e file access), it does that under the security context of the account SQL runs under. It might be that, that account does not have sufficient permissions for that file.

If that's the case you can (if the original caller uses integrated security) do a impersonation, by using SqlContext.WindowsIdentity.

Niels

Error execute SSIS package

What this?

An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(error not found). Make sure you are accessing a local server via Windows security.".

Just a guess but...

Are you using SQL Server Destination by any chance?

If so, are you executing the package on the same server as your destination?

-Jamie

sql

error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

Error encountered while executing a UDF

Hi,

I get the following error while trying to execute a UDF.

Msg 6522, Level 16, State 1, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'test':

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at SalesCentral_Database.UserDefinedFunctions.udfTest()

My function is defined as follows:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _

Public Shared Function udfTest() As SqlString

SqlContext.Pipe.Send(System.DateTime.Now().ToString())

Dim conn As New SqlConnection("context connection=true")

conn.Open()

Dim cmd As New SqlCommand("SELECT MAX(ACCTPERIOD) FROM TBL_OPPORTUNITY_HIS", conn)

Return New SqlString(cmd.ExecuteScalar().ToString())

End Function

End Class

SQL Server

CREATE ASSEMBLY SalesCentral_Database from '\\isew5l6c\tranf\SFS_Sales\SalesCentral_Database.dll' WITH PERMISSION_SET = SAFE

CREATE FUNCTION test() RETURNS NVARCHAR(10) AS EXTERNAL NAME [SalesCentral_Database].[SalesCentral_Database.UserDefinedFunctions].[udfTest]

SELECT dbo.test()

- Here I get the error mentioned above. Can anyone please tell me what am I missing.

Thanks.

Hi,

This is the same error as in this thread. You should remove the "SqlContext.Pipe.Send..." line from your code. I'm curious what you intent is with that linethe pipe is really to return results from within a stored procedure.

Cheers,
-Isaac

|||Thanks for bringing that to my notice. Sorry it has slipped out of my mind :)

Error during Execute sp with IMAGE parameter

Hello.
It is the first time that Im using IMAGE field type..

I created a stored procedure that should return an IMAGE field back to a
c-sharp program .
(@.OutImg IMAGE declared as an output parameter of the procedure.)
(Select @.OutImg = ImgFld from table)
Well,I can compile it , but when execute i get this error :
Msg 2739, Level 16, State 1, Line 14
The text, ntext, and image data types are invalid for local variable.

Is there something I can do ,OR should I use any cast/convert func to solve it ..?
Many thanks.:ofrom 2005 BOL:

text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure.

So you'll have to return this in a record set, not in an OUT param.

Error Condition for Updates

Hi,

I have a series of Updates that I perform on 7 tables. I would like to execute these updates on a nighly basis via a job. I have the update statements lined up in a stored proc.

So I have -

Create Proc Update
AS
SET NOCOUNT ON

DECLARE @.RETURNVALUE int
DECLARE @.ERRORMESSAGETXT varchar(510)
DECLARE @.ERRORNUM int

set @.RETURNVALUE = 0

BEGIN --Start here

Update table1
Update table2
.
.
.
Update table7

SELECT @.ERRORNUM = @.@.ERROR
IF @.ERRORNUM = 0
SELECT @.RETURNVALUE = 0
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END
RETURN @.RETURNVALUE
END -- END HERE

--

Is the error checking at the appropriate place? Should I have error conditions after every update? If yes then what condition do I check for?

Thanks@.@.ERROR only shows the result of the most recent statement. It is cleared for the next statement. Therefor your code is only going to indicate whether an error occured on Update Table7.

You will need to check for an error after each critical step, and then store the results of the error check. You can store it in a single value like:
@.ERROR_MONITOR = @.ERROR_MONITOR + @.@.ERROR
...which will result in a value greater than zero if any errors occured. Or you can store the results of each check separately (@.UPDATETABLE1ERROR, @.UPDATETABLE2ERROR..etc).|||So is this correct. Check the where clause in the ELSE part and the statement after Update Table 7.
----
Update table1
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR
Update table2
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR
.
.
.
Update table7
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR

IF @.ERRORNUM = 0
SELECT @.RETURNVALUE = 0
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR (OR is this @.ERRORNUM)
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END
RETURN @.RETURNVALUE

Let me know.

Thanks|||@.ERRORNUM holds the sum of all the error message IDs, so it can be used to determine whether any error occured but if more than one error occured then it is useless for looking up the error description.

"SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages] WHERE error = @.@.ERROR "
...will, again, only give you the error message for the last error to occur.

If you want to be able to handle multiple errors, create a varchar variable and append any error text to it after each critical step.

Depending on your process, you may just want to exit the procedure after the first error...|||Don't call your procedure "Update". And I don't think you could actually compile what you posted.

And if you are really interested in capturing all the errors while continuing to do your updates, then you'll need a @.temptable to acquire a value of @.@.error on each update. At the end you would parse a string of all errors by joining this table with sysmessages and scrolling through it while concatenating [description] into a varchar variable.