Default Database brought offline SQL Server Agent Job fails!!!

Hi All,

There is this SQL Server agent job that was calling a SSIS package (uses windows authentication) which was executing fine till the default database for the user was brought ofline, and now the job fails citing authentication failure for the user as the reason.

I have tried pointing master as the Default database for the user and now able to connect to SSMS using the users authentication, but the SQL Agent job wont succeed.

Any pointers ?

Have you tried tracing the connection attempt using SQL Profiler?

This is a SQL engine security issue - its nothing to do with SSIS.

-Jamie

|||

Jamie,

You were right this has nothing to do with SSIS indded, I was using a Proxy account and I tried resetting the password, and it seems to work now. Thanks in helping me look at the right area.

Declare cursor for execute stored_procedure

Hello,

I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @.Variable).
Something like this:

DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)

Not sure if i am doing this right all together.
any help would be greatly appreciate.See if this helps. It is not good practice to concatenate user entries into a SQL string, so be careful not to risk SQL Injection.

create proc SP_getDate (

@.v int

) as

SET NOCOUNT ON

SELECT TOP (@.v) HireDate

FROM AdventureWorks.HumanResources.Employee

ORDER BY EmployeeID

go

DECLARE @.sql NVARCHAR(1000)

DECLARE @.Variable int

SET @.Variable = 13

SET @.sql = '

DECLARE Cursor1 CURSOR FOR

SELECT HireDate

FROM OPENQUERY([SK8400\YUK], ''exec AdventureWorks.dbo.SP_getDate @.v'')'

SET @.sql = REPLACE(@.sql,'@.v',@.Variable)

exec (@.sql)

go

declare @.d datetime

open Cursor1

toploop:

fetch from Cursor1 into @.d

while @.@.fetch_status = 0 begin

print @.d

goto toploop

end

close Cursor1

deallocate Cursor1

go

drop proc SP_getDate

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

Yassi@.discussions.microsoft.com wrote:

> Hello,

>

> I am using SQL 2005 and i would like to create a cursor from executing a

> stored procedure (dbo.SP_getDate @.Variable).

> Something like this:

>

> DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

>

> i get an error saying "incorrect syntax near the keyword 'EXECUTE'."

> cannot get rid of the error. what am i doing wrong?

> (i am trying to avoid using #tempTbl to store the results of the execute

> first and then doing a select on the #tempTbl)

>

> Not sure if i am doing this right all together.

> any help would be greatly appreciate.

>

>

Deallocating cursor if exception occured.

Hi all,
i am facing one problem while executing stored procedure.
What i am doing is i am opening one cursor and then depending on values
fetch inside the cursor i am inserting data into one table.
But sometimes my insert query failes due to violation in primary key.
In that case sp is not directly stopping its execution.
So my question is if such situation occured how will i deallocate my
cursor. I am using sql server 2000.
How to handle such exception to execute next part of stored procedure.
Thanks in advance.
trialproduct2004@.yahoo.com wrote:
> Hi all,
> i am facing one problem while executing stored procedure.
> What i am doing is i am opening one cursor and then depending on values
> fetch inside the cursor i am inserting data into one table.
>
I suggest you do that with a WHERE clause rather than a cursor.
INSERT INTO tbl1 (co1, col2, ...)
SELECT col1, col2, ...
FROM tbl2
WHERE ... ?

> But sometimes my insert query failes due to violation in primary key.
> In that case sp is not directly stopping its execution.
Ditto. Better to fix your code rather than handle the errors it causes.
Change your INSERT query to avoid inserting duplicate rows.
Cursors should be a last resort only. At least 99.9% of the time you
can and should avoid them.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx