Executing SQL Stored Procedures in VB

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

Are possible errors caught within the procedure or your vb code ? How do you know that the procedure is not executed successfully if you get no additional error and why do you think it stops ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Executing SQL Stored Procedures in VB

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

Quote:

Originally Posted by SQLusername

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

tons of reasons, check for these possibilities:
1. object locking
2. the fourth stored proc is not returning anything
3. your server is configured to time-out after a certain time.|||

Quote:

Originally Posted by ck9663

tons of reasons, check for these possibilities:
1. object locking
2. the fourth stored proc is not returning anything
3. your server is configured to time-out after a certain time.

Can you descibe what object locking is and how to remedy it? Also using SQL Server Enterprise Manager where can I edit time-out settings?

executing package as job

Hi,

I am having trouble executing a package as a job in SQL Server 2005. I can run the package fine manually from sql server but when I create a job for that package and run the job I get an error that says

The package execution failed. The step failed.

I am completely lost at this point. Any help would be greatly appreciated.

Thanks
Brian

This article describes what can go wrong and how to troubleshoot it:
http://support.microsoft.com/kb/918760

Please check it out.

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel B1) Locate the job (under SQL Agent)
2) Right click on the job
3) Pick View Job History...
4) Check the Show Job Details box
5) Read the error message(s)

-PatP|||This is the error message i get.

The job failed. The Job was invoked by User REPEATSEAT\dberezniak. The last step to run was step 1 (DTS Execute App).

This doesn't tell me much. It blows up when trying to execute the app.
( The application is test and does work on its own and within the package. )|||Did you click on the "Show Job Details" box to be sure that it is checked? That message sounds like what you'd get if that box wasn't checked.

-PatPsql

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel BThis is really a question for the Microsoft SQL Server (http://www.dbforums.com/f7) forum. I'd be happy to answer the question there!

-PatP

EXECUTE AS clause help

I am having trouble utilizing the EXECUTE AS clause. Here is the simple statement:

Execute as user = 'Common.com\bsmith'
--DBCC log(Actg, Type=2)
DBCC SQLPERF(LOGSPACE)

bsmith is the system administrator that has rights to both DBCC in connection with SQLPERF and the following statement:

DBCC log(Makeup, Type=2)

where the database is Makeup. My login (Common.com\dchrist)
does not have permission to do anything connected with the log file. The goal of this SQL is to allow dchrist to run items connected with Actg log files ONLY. I am logging into the server (EVR-NON-DB) as dchrist with my password.

Is there any way to do this or am I way off base. I have read several postings that explain EXECUTE AS and thought I understood how to use it. When I run:

Execute as user = 'Common.com\bsmith'
--DBCC log(Actg, Type=2)
DBCC SQLPERF(LOGSPACE)

I get the following error message:

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "Common.com\bsmith" does not exist, this type of principal cannot be impersonated, or you do not have permission.

If anyone has any idea what I should do next I would really appreciate it. Thanks for all help in advance.

A couple of guesses. First, you probably need to use EXECUTE AS LOGIN =

Then look up "GRANT Server Principal Permissions". You probably have to give your login rights to IMPERSONATE the other login.

|||It sounds to me like you're referring to a LOGIN, not a USER. Try using 'LOGIN' instead of 'USER', or else work out which user maps to the login. A user is how a login accesses a database. Your login might access one database as dbo, another as bsmith, and a third as something else. Once you've got that sorted, let us know what the next error message says.