db files: runs

Showing posts with label runs. Show all posts

Friday, March 30, 2012

Runtime error 3704

Hi,

I've created an application that runs on several pc's. However, on one particular pc, when I run the following code to test the connection to the SQL server, it generates the error "Runtime error '3704'" and it happens only when it hits the Form1.Users.Movefirst section.

I've checked all the user permissions on the server and dont seem to find any difference there.

code:-

Option Explicit
Public Db As New ADODB.Connection
Public Users As New ADODB.Recordset

Private Sub Command1_Click()

If Not Form1.Users.BOF Then
Form1.Users.MoveFirst
End If

With Form1.Users
Do While Not Form1.Users.EOF
Debug.Print Form1.Users.Fields(0)
Form1.Users.MoveNext
Loop
End With

End Sub

Private Sub Form_Load()

On Error Resume Next
Set Db = Nothing

Db.Open _
"Provider=SQLOLEDB.1;Server=File;" & _
"Database=Cash Management;Trusted_Connection=Yes"

'Open the recordsets
With Form1.Users
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.ActiveConnection = Form1.Db
.Source = "Select * from Users order by userid"
.Open "SET NOCOUNT ON"
End With

End Sub

ThanksIt is very simple. You are running "SET NOCOUNT ON" only. What MDAC version do you use?|||Hi.. Thanks for your reply. Normally, my .open has nothing after it. Only after I searched on the net, did I add that "Set NoCount On", but it didn't help. Sorry to sound ignorant, but how do I tell what ver of MDAC I'm using ?

Carl|||Q1. What MDAC version am I using?
A1. Use regedit.exe, see branch "HKEY_LOCAL_MACHINE\Software\Microsoft\DataAccess" node "FullInstallVer"|||It looks like 2.70.7713.4.

Tks..|||You added "SET NOCOUNT ON" for test purposses only, remove it.
In your code, after the part where you try to open a connection,
add a check (+msgbox) to know if it is really connected.
It can be a domain security problem.|||Ok - I checked the registry on the pc that I was installing on and I see the version of MDAC is 2.53.6200.1. This is the same as another pc that is working.

If this is not the problem, I suspect that it is a permissions problem, but I have no idea where to check as this user has been registered exactly the same as the other users that work.

Thanks|||Run (for W9x)
command /K net config
from Start->Run commandline on the client machine and you will see his logon info (or ask him to do it and send results).|||My email address is alkemac@.hotmail.com. Can you please send an email and I will reply with screen shots of the different screens, including the SQL Svr user registration screen ?

Thanks|||It was a user default database problem. The user solved it by himself.|||What do you mean by "default database problem"? Can you explain how the user solved it himself? We have a similar problem with a VB application that opens an Access database. It runs on most computers, but not some. One gets the 3704 runtime error. Thanks.sql

Wednesday, March 28, 2012

Running Views on SP2 Slower than SP3

Hi has anybody experienced views running slower on SQL 8 SP2 is slower than
running it on SP3?
I have 1 log shipping report server on SP3 that runs those views with out
problems.
I have another server with Replication running SP2, I find that views runnin
g
on this server has more locks and runs a lot slower. Some reports can't even
finish.
Is there such a big difference in SP3 as it pertains to running Views?
oh btw hardware is the same on both servers.
any help would be greatly appreciated..
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1I have no doubt that SP3 contains fixes for a number of specific query
optimization (and other performance) problems that still existed in SP2.
That really shouldn't come as a surprise. It isn't as simple as SP3 being
better at running "Views", but rather that some set of your Views likely
have a narrow set of circumstances that versions prior to SP3 didn't handle
well.
Is there a reason you don't upgrade the SP2 server to SP3 (or SP4!)? SP2 is
definitely out of date.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"sqlclubber via droptable.com" <u19392@.uwe> wrote in message
news:5d11c243b34e5@.uwe...
> Hi has anybody experienced views running slower on SQL 8 SP2 is slower
> than
> running it on SP3?
> I have 1 log shipping report server on SP3 that runs those views with out
> problems.
> I have another server with Replication running SP2, I find that views
> running
> on this server has more locks and runs a lot slower. Some reports can't
> even
> finish.
> Is there such a big difference in SP3 as it pertains to running Views?
> oh btw hardware is the same on both servers.
> any help would be greatly appreciated..
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1

Running Views on SP2 Slower than SP3

Hi has anybody experienced views running slower on SQL 8 SP2 is slower than
running it on SP3?
I have 1 log shipping report server on SP3 that runs those views with out
problems.
I have another server with Replication running SP2, I find that views running
on this server has more locks and runs a lot slower. Some reports can't even
finish.
Is there such a big difference in SP3 as it pertains to running Views?
oh btw hardware is the same on both servers.
any help would be greatly appreciated..
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
I have no doubt that SP3 contains fixes for a number of specific query
optimization (and other performance) problems that still existed in SP2.
That really shouldn't come as a surprise. It isn't as simple as SP3 being
better at running "Views", but rather that some set of your Views likely
have a narrow set of circumstances that versions prior to SP3 didn't handle
well.
Is there a reason you don't upgrade the SP2 server to SP3 (or SP4!)? SP2 is
definitely out of date.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"sqlclubber via droptable.com" <u19392@.uwe> wrote in message
news:5d11c243b34e5@.uwe...
> Hi has anybody experienced views running slower on SQL 8 SP2 is slower
> than
> running it on SP3?
> I have 1 log shipping report server on SP3 that runs those views with out
> problems.
> I have another server with Replication running SP2, I find that views
> running
> on this server has more locks and runs a lot slower. Some reports can't
> even
> finish.
> Is there such a big difference in SP3 as it pertains to running Views?
> oh btw hardware is the same on both servers.
> any help would be greatly appreciated..
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200603/1

Running Views on SP2 Slower than SP3

Hi has anybody experienced views running slower on SQL 8 SP2 is slower than
running it on SP3?
I have 1 log shipping report server on SP3 that runs those views with out
problems.
I have another server with Replication running SP2, I find that views running
on this server has more locks and runs a lot slower. Some reports can't even
finish.
Is there such a big difference in SP3 as it pertains to running Views?
oh btw hardware is the same on both servers.
any help would be greatly appreciated..
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1I have no doubt that SP3 contains fixes for a number of specific query
optimization (and other performance) problems that still existed in SP2.
That really shouldn't come as a surprise. It isn't as simple as SP3 being
better at running "Views", but rather that some set of your Views likely
have a narrow set of circumstances that versions prior to SP3 didn't handle
well.
Is there a reason you don't upgrade the SP2 server to SP3 (or SP4!)? SP2 is
definitely out of date.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"sqlclubber via SQLMonster.com" <u19392@.uwe> wrote in message
news:5d11c243b34e5@.uwe...
> Hi has anybody experienced views running slower on SQL 8 SP2 is slower
> than
> running it on SP3?
> I have 1 log shipping report server on SP3 that runs those views with out
> problems.
> I have another server with Replication running SP2, I find that views
> running
> on this server has more locks and runs a lot slower. Some reports can't
> even
> finish.
> Is there such a big difference in SP3 as it pertains to running Views?
> oh btw hardware is the same on both servers.
> any help would be greatly appreciated..
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1sql

Friday, March 23, 2012

running the upgrade step from version 610 to version 611 fails

I am having an intermittent problem when restoring a SQL Server 2000 database to SQL Server 2005.

I have a job that runs daily that restores the database. This job has ran 20 times with no problem and has failed twice. When it fails it fails in the exact same place with the same error message:

>>Restoring Backup File: D:\Backups\Production\IHI.bak [SQLSTATE 01000]
Processed 388880 pages for database 'IHI', file 'IHIDat.mdf' on file 1. [SQLSTATE 01000]
Processed 1 pages for database 'IHI', file 'IHILog.ldf' on file 1. [SQLSTATE 01000]
RESTORE DATABASE successfully processed 388881 pages in 130.732 seconds (24.368 MB/sec). [SQLSTATE 01000]
>>Bringing database online... [SQLSTATE 01000]
Converting database 'IHI' from version 539 to the current version 611. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 539 to version 551. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 551 to version 552. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 552 to version 553. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 553 to version 554. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 554 to version 589. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 589 to version 590. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 590 to version 593. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 593 to version 597. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 597 to version 604. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 604 to version 605. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 605 to version 606. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 606 to version 607. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 607 to version 608. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 608 to version 609. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 609 to version 610. [SQLSTATE 01000]
Msg 3023, Sev 16, State 3, Line 1 : Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. [SQLSTATE 42000]
Msg 5069, Sev 16, State 1, Line 1 : ALTER DATABASE statement failed. [SQLSTATE 42000]
Msg 951, Sev 16, State 1, Line 287 : Database 'IHI' running the upgrade step from version 610 to version 611. [SQLSTATE 01000]
Msg 3014, Sev 16, State 1, Line 287 : RESTORE DATABASE successfully processed 0 pages in 30.370 seconds (0.000 MB/sec). [SQLSTATE 01000]

For some reason the last step of upgrading to 611 seems to be having a problem. If I rerun the job on the exact same IHI.bak 10 minutes later it will/has succeeded. So I don't particulary think there is a problem with the .bak it self.

Anyone have any ideas?

-Mark

I think there is some reordering of error messages here by the client that is confusing things some, but the 5069 error you are getting should only come from an ALTER DATABASE command, not the RESTORE, and that seems to be failing. Is your job doing some kind of option or state change on the DB after the RESTORE? Can you post a fragment of the errorlog around the time of such a failure?

|||

At 6AM the Job started which refreshes numerous databases. Three of these DBs are from 2000 server: DYNAMICS, IHI, and SBM01. All the others are from a 2005 server.

The Job was re-ran at 7:14 on the same day and completed successfully. The 6am job failed.

SQL Error Log:

01/28/2007 07:40:52,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database SBM01.
01/28/2007 07:40:25,spid57,Unknown,Recovery is writing a checkpoint in database 'SBM01' (7). This is an informational message only. No user action is required.
01/28/2007 07:40:25,spid57,Unknown,Starting up database 'SBM01'.
01/28/2007 07:40:25,Backup,Unknown,Database was restored: Database: SBM01<c/> creation date(time): 2005/01/04(11:36:34)<c/> first LSN: 3943:4917:1<c/> last LSN: 3943:4922:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\SBM01.bak'}). Informational message. No user action required.
01/28/2007 07:40:25,spid57,Unknown,The database 'SBM01' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:40:25,spid57,Unknown,Starting up database 'SBM01'.
01/28/2007 07:40:03,spid57,Unknown,The database 'SBM01' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:40:02,spid57,Unknown,Starting up database 'SBM01'.
01/28/2007 07:40:01,spid57,Unknown,Setting database option OFFLINE to ON for database SBM01.
01/28/2007 07:36:03,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database IHI.
01/28/2007 07:35:33,spid57,Unknown,Recovery is writing a checkpoint in database 'IHI' (5). This is an informational message only. No user action is required.
01/28/2007 07:35:31,spid57,Unknown,Starting up database 'IHI'.
01/28/2007 07:35:31,Backup,Unknown,Database was restored: Database: IHI<c/> creation date(time): 2003/07/22(11:29:55)<c/> first LSN: 17290:32:1<c/> last LSN: 17290:45:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\IHI.bak'}). Informational message. No user action required.
01/28/2007 07:35:31,spid57,Unknown,The database 'IHI' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:35:31,spid57,Unknown,Starting up database 'IHI'.
01/28/2007 07:33:21,spid57,Unknown,The database 'IHI' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:33:20,spid57,Unknown,Starting up database 'IHI'.
01/28/2007 07:33:20,spid57,Unknown,Setting database option OFFLINE to ON for database IHI.
01/28/2007 07:33:17,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DYNAMICS.
01/28/2007 07:33:14,spid57,Unknown,Recovery is writing a checkpoint in database 'DYNAMICS' (6). This is an informational message only. No user action is required.
01/28/2007 07:33:14,spid57,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 07:33:14,Backup,Unknown,Database was restored: Database: DYNAMICS<c/> creation date(time): 2003/07/22(11:20:05)<c/> first LSN: 579:34:1<c/> last LSN: 579:36:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\DYNAMICS.bak'}). Informational message. No user action required.
01/28/2007 07:33:14,spid57,Unknown,The database 'DYNAMICS' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:33:14,spid57,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 07:33:09,spid57,Unknown,The database 'DYNAMICS' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:33:09,spid57,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 07:33:09,spid57,Unknown,Setting database option OFFLINE to ON for database DYNAMICS.
01/28/2007 07:33:08,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DW_EventStats.
01/28/2007 07:33:08,spid57,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 07:33:08,Backup,Unknown,Database was restored: Database: DW_EventStats<c/> creation date(time): 2006/02/25(10:37:46)<c/> first LSN: 569:307:37<c/> last LSN: 569:323:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_EventStats.bak'}). Informational message. No user action required.
01/28/2007 07:33:08,spid57,Unknown,The database 'DW_EventStats' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:33:08,spid57,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 07:33:08,spid57,Unknown,The database 'DW_EventStats' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:33:08,spid57,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 07:33:07,spid57,Unknown,Setting database option OFFLINE to ON for database DW_EventStats.
01/28/2007 07:30:01,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17445:99857:1<c/> last LSN: 17445:99857:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128073001257_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 07:25:16,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DW_IHIDB.
01/28/2007 07:25:15,spid57,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 07:25:15,Backup,Unknown,Database was restored: Database: DW_IHIDB<c/> creation date(time): 2006/02/25(10:41:52)<c/> first LSN: 78692:11007:261<c/> last LSN: 78692:11114:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_IHIDB.bak'}). Informational message. No user action required.
01/28/2007 07:25:13,spid57,Unknown,The database 'DW_IHIDB' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:25:13,spid57,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 07:22:05,spid57,Unknown,The database 'DW_IHIDB' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:22:05,spid57,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 07:22:05,spid57,Unknown,Setting database option OFFLINE to ON for database DW_IHIDB.
01/28/2007 07:22:05,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DW_NEventStats.
01/28/2007 07:22:05,spid57,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 07:22:05,Backup,Unknown,Database was restored: Database: DW_NEventStats<c/> creation date(time): 2006/02/25(10:53:08)<c/> first LSN: 252:570:81<c/> last LSN: 252:603:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_NEventStats.bak'}). Informational message. No user action required.
01/28/2007 07:22:05,spid57,Unknown,The database 'DW_NEventStats' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:22:05,spid57,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 07:22:04,spid57,Unknown,The database 'DW_NEventStats' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:22:04,spid57,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 07:22:04,spid57,Unknown,Setting database option OFFLINE to ON for database DW_NEventStats.
01/28/2007 07:21:10,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DW_NICHQDB.
01/28/2007 07:21:07,spid57,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 07:21:07,Backup,Unknown,Database was restored: Database: DW_NICHQDB<c/> creation date(time): 2006/02/25(10:54:06)<c/> first LSN: 5137:438:99<c/> last LSN: 5137:479:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_NICHQDB.bak'}). Informational message. No user action required.
01/28/2007 07:21:06,spid57,Unknown,The database 'DW_NICHQDB' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:21:06,spid57,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 07:20:37,spid57,Unknown,The database 'DW_NICHQDB' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:20:37,spid57,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 07:20:37,spid57,Unknown,Setting database option OFFLINE to ON for database DW_NICHQDB.
01/28/2007 07:15:01,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17445:99857:1<c/> last LSN: 17445:99857:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128071501527_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 07:14:44,spid56,Unknown,Recovery is writing a checkpoint in database 'SBM01' (7). This is an informational message only. No user action is required.
01/28/2007 07:14:44,spid56,Unknown,0 transactions rolled back in database 'SBM01' (7). This is an informational message only. No user action is required.
01/28/2007 07:14:44,spid56,Unknown,1 transactions rolled forward in database 'SBM01' (7). This is an informational message only. No user action is required.
01/28/2007 07:14:44,spid56,Unknown,Starting up database 'SBM01'.
01/28/2007 07:14:44,spid56,Unknown,Setting database option ONLINE to ON for database SBM01.
01/28/2007 07:00:00,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17445:99857:1<c/> last LSN: 17445:99857:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128070000313_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 06:45:00,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17445:99824:1<c/> last LSN: 17445:99857:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128064500223_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 06:31:04,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17328:5750:1<c/> last LSN: 17445:99824:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128063000540_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 06:19:37,spid55,Unknown,Setting database option OFFLINE to ON for database SBM01.
01/28/2007 06:15:25,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17290:45:1<c/> last LSN: 17328:5750:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128061502183_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 06:15:22,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database IHI.
01/28/2007 06:14:51,spid55,Unknown,Recovery is writing a checkpoint in database 'IHI' (5). This is an informational message only. No user action is required.
01/28/2007 06:14:51,spid55,Unknown,Starting up database 'IHI'.
01/28/2007 06:14:51,Backup,Unknown,Database was restored: Database: IHI<c/> creation date(time): 2003/07/22(11:29:55)<c/> first LSN: 17290:32:1<c/> last LSN: 17290:45:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\IHI.bak'}). Informational message. No user action required.
01/28/2007 06:14:50,spid55,Unknown,The database 'IHI' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:14:50,spid55,Unknown,Starting up database 'IHI'.
01/28/2007 06:12:39,spid55,Unknown,The database 'IHI' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:12:39,spid55,Unknown,Starting up database 'IHI'.
01/28/2007 06:12:39,spid55,Unknown,Setting database option OFFLINE to ON for database IHI.
01/28/2007 06:12:37,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DYNAMICS.
01/28/2007 06:12:34,spid55,Unknown,Recovery is writing a checkpoint in database 'DYNAMICS' (6). This is an informational message only. No user action is required.
01/28/2007 06:12:34,spid55,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 06:12:34,Backup,Unknown,Database was restored: Database: DYNAMICS<c/> creation date(time): 2003/07/22(11:20:05)<c/> first LSN: 579:34:1<c/> last LSN: 579:36:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\DYNAMICS.bak'}). Informational message. No user action required.
01/28/2007 06:12:33,spid55,Unknown,The database 'DYNAMICS' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:12:33,spid55,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 06:12:28,spid55,Unknown,The database 'DYNAMICS' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:12:28,spid55,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 06:12:27,spid55,Unknown,Setting database option OFFLINE to ON for database DYNAMICS.
01/28/2007 06:12:27,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DW_EventStats.
01/28/2007 06:12:27,spid55,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 06:12:27,Backup,Unknown,Database was restored: Database: DW_EventStats<c/> creation date(time): 2006/02/25(10:37:46)<c/> first LSN: 569:307:37<c/> last LSN: 569:323:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_EventStats.bak'}). Informational message. No user action required.
01/28/2007 06:12:27,spid55,Unknown,The database 'DW_EventStats' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:12:27,spid55,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 06:12:26,spid55,Unknown,The database 'DW_EventStats' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:12:26,spid55,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 06:12:26,spid55,Unknown,Setting database option OFFLINE to ON for database DW_EventStats.
01/28/2007 06:04:43,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DW_IHIDB.
01/28/2007 06:04:42,spid55,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 06:04:42,Backup,Unknown,Database was restored: Database: DW_IHIDB<c/> creation date(time): 2006/02/25(10:41:52)<c/> first LSN: 78692:11007:261<c/> last LSN: 78692:11114:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_IHIDB.bak'}). Informational message. No user action required.
01/28/2007 06:04:40,spid55,Unknown,The database 'DW_IHIDB' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:04:40,spid55,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 06:01:31,spid55,Unknown,The database 'DW_IHIDB' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:01:30,spid55,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 06:01:30,spid55,Unknown,Setting database option OFFLINE to ON for database DW_IHIDB.
01/28/2007 06:01:30,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DW_NEventStats.
01/28/2007 06:01:30,spid55,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 06:01:30,Backup,Unknown,Database was restored: Database: DW_NEventStats<c/> creation date(time): 2006/02/25(10:53:08)<c/> first LSN: 252:570:81<c/> last LSN: 252:603:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_NEventStats.bak'}). Informational message. No user action required.
01/28/2007 06:01:30,spid55,Unknown,The database 'DW_NEventStats' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:01:30,spid55,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 06:01:29,spid55,Unknown,The database 'DW_NEventStats' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:01:29,spid55,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 06:01:29,spid55,Unknown,Setting database option OFFLINE to ON for database DW_NEventStats.
01/28/2007 06:00:33,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DW_NICHQDB.
01/28/2007 06:00:31,spid55,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 06:00:31,Backup,Unknown,Database was restored: Database: DW_NICHQDB<c/> creation date(time): 2006/02/25(10:54:06)<c/> first LSN: 5137:438:99<c/> last LSN: 5137:479:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_NICHQDB.bak'}). Informational message. No user action required.
01/28/2007 06:00:30,spid55,Unknown,The database 'DW_NICHQDB' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:00:30,spid55,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 06:00:01,spid55,Unknown,The database 'DW_NICHQDB' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:00:01,spid55,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 06:00:01,spid55,Unknown,Setting database option OFFLINE to ON for database DW_NICHQDB.

What I had posted earlier was the output from the Job.

My restore process is:

'ALTER DATABASE ' + @.database + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'
RESTORE DATABASE @.database FROM DISK = @.fileBAK WITH FILE = 1, MOVE @.backupMDFName TO @.dbMDFFile, MOVE @.backupLDFName O @.dbLDFFile, NORECOVERY, REPLACE
RESTORE DATABASE @.database FROM DISK = @.fileDFF WITH FILE = 1, MOVE @.backupMDFName TO @.dbMDFFile, MOVE @.backupLDFName O @.dbLDFFile, NORECOVERY, REPLACE
RESTORE DATABASE @.database WITH RECOVERY

I then change the owner, fix users, change recovery model to SIMPLE, and finally shrink the DB.

What I find strange is that this works almost all of the time. In the case of the error log above the job completed successfully the second time it ran 40 minutes later. What would cause it to fail. I haven't had any issues with the 2005 databases and they all to through the exact same steps.

Here is an excerpt from the job log where it completed successfully for the IHI DB today:

>>Restoring: 6=IHI [SQLSTATE 01000]
DATABASE: IHI [SQLSTATE 01000]
PATH: D:\Backups\Production [SQLSTATE 01000]
FILE: IHI [SQLSTATE 01000]
>>Taking the database offline... [SQLSTATE 01000]
>>Restoring Backup File: D:\Backups\Production\IHI.bak [SQLSTATE 01000]
Processed 390848 pages for database 'IHI', file 'IHIDat.mdf' on file 1. [SQLSTATE 01000]
Processed 3 pages for database 'IHI', file 'IHILog.ldf' on file 1. [SQLSTATE 01000]
RESTORE DATABASE successfully processed 390851 pages in 126.707 seconds (25.269 MB/sec). [SQLSTATE 01000]
>>Bringing database online... [SQLSTATE 01000]
Converting database 'IHI' from version 539 to the current version 611. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 539 to version 551. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 551 to version 552. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 552 to version 553. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 553 to version 554. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 554 to version 589. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 589 to version 590. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 590 to version 593. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 593 to version 597. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 597 to version 604. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 604 to version 605. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 605 to version 606. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 606 to version 607. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 607 to version 608. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 608 to version 609. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 609 to version 610. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 610 to version 611. [SQLSTATE 01000]
RESTORE DATABASE successfully processed 0 pages in 29.169 seconds (0.000 MB/sec). [SQLSTATE 01000]
>>Changing owner to sa... [SQLSTATE 01000]
>>Fixing user account: mark [SQLSTATE 01000]
The row for user 'mark' will be fixed by updating its login link to a login already in existence. [SQLSTATE 01000]
The number of orphaned users fixed by updating users was 1. [SQLSTATE 01000]
The number of orphaned users fixed by adding new logins and then updating users was 0. [SQLSTATE 01000]
USE IHI; EXEC sp_change_users_login 'Auto_Fix', 'mark' [SQLSTATE 01000]
>>Changing Recovery Model to SIMPLE... [SQLSTATE 01000]
>>Shrinking the database... [SQLSTATE 01000]

Thanks, -Mark

|||

These two lines are interesting. I'm going to reverse them so they are in chronological order

...

01/28/2007 06:15:22,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database IHI.
01/28/2007 06:15:25,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17290:45:1<c/> last LSN: 17328:5750:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128061502183_S.trn'}). This is an informational message only. No user action is required.
...

The first line actually gets output before we attempt the ALTER DATABASE operation. Do you have some kind of background process that goes through and does log backups on databases that are in FULL recovery mode? It looks like what is happening is that your ALTER DATABASE SET RECOVERY SIMPLE is failing because it is colliding with some other thread that is doing a BACKUP LOG at the same time. Is that possible?

|||

That is very possible. Since I convert these DBs to SIMPLE as part of the restore operation I didn't even really notice that the error log said a log was being taken.

This server has several custom jobs that our Datacenter host provides and supports that does daily backups along with 15 minute transaction logs. I convert these DBs to SIMPLE since we are only using them for reporting purposes on this server and I don't need to have the extra overhead with the backups.

When I am restoring the DB it is in FULL recovery mode since that is how it is on the production server we are copying from. During the restore operation the 15 minute backup is occasionally colliding with my restore causing the problem. All makes perfect sense now.

I never considered that job potentially being issue since for one I didn't create it and manage it and the other being that I know I put everything into SIMPLE which is skipped by that job.

I will work with my host to resolve the timing of my restores.

Thanks for your help. I definitelly needed another set of eyes.

-Mark

Wednesday, March 21, 2012

Running SSIS package using .Net code

I am running this code my question is when it runs ExecuteNonQuery() it doesn't wait for package to complete It just returns "Package succeeded' - The way you can test if you run this code 2 times one after another. ExecuteNonQuery() breaks with error -

SQLServerAgent Error: Request to run job RunSsisPkg (from User Domain\User) refused because the job is already running from a request by User Domain\User.

How can I check before return package is running and wait to complete before return?

using System;

using System.Data;

using System.Data.SqlClient;

namespace SSISRun

{

class Program

{

static void Main(string[] args)

{

SqlConnection jobConnection;

SqlCommand jobCommand;

SqlParameter jobReturnValue;

SqlParameter jobParameter;

int jobResult;

jobConnection = new SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI");

jobCommand = new SqlCommand("sp_start_job", jobConnection);

jobCommand.CommandType = CommandType.StoredProcedure;

jobReturnValue = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

jobReturnValue.Direction = ParameterDirection.ReturnValue;

jobCommand.Parameters.Add(jobReturnValue);

jobParameter = new SqlParameter("@.job_name", SqlDbType.VarChar);

jobParameter.Direction = ParameterDirection.Input;

jobCommand.Parameters.Add(jobParameter);

jobParameter.Value = "PackageName";

jobConnection.Open();

jobCommand.ExecuteNonQuery();

jobResult = (Int32)jobCommand.Parameters["@.RETURN_VALUE"].Value;

jobConnection.Close();

switch (jobResult)

{

case 0:

Console.WriteLine("Package succeeded.");

break;

default:

Console.WriteLine("Package failed.");

break;

}

I believe the RunningPackage class can help you.

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx|||

Is it true that to use Microsoft.SqlServer.Dts.Runtime I need to have SSIS services in the box? I am looking for a way to run SSIS package without having SSIS service in the box.

The other option using web service is really not working I have one post on that.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1949366&SiteID=1

Thank you - Ashok

|||

Ashok Ojha wrote:

Is it true that to use Microsoft.SqlServer.Dts.Runtime I need to have SSIS services in the box? I am looking for a way to run SSIS package without having SSIS service in the box.

The other option using web service is really not working I have one post on that.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1949366&SiteID=1

Thank you - Ashok

In SQL Server 2005 you cannot because we were telling developers to run data automation with DTS packages, Replication was threatened but you can do it in 2000 and it works like a charm.

|||

I don't believe you have to have the SSIS Service running to run packages, but you do have to have a license for SSIS on the computer where you will be running the packages, and you will need to install the SSIS components.

|||

jwelch wrote:

I don't believe you have to have the SSIS Service running to run packages, but you do have to have a license for SSIS on the computer where you will be running the packages, and you will need to install the SSIS components.

In 2000 the the DTS runtime is in the package it is not the same, it is one of the changes in the product I have used DTS from 7.0

|||

Thank you. I am using SSIS so it's sql 2005. What I understand that I can install sql server 2005 and SSIS services in server "A" with license and my server "B" which runs ETL can have this .net process which is using "Microsoft.SqlServer.Dts.Runtime" dll can run .dtsx files in Server "A" without having SSIS service/license in server "B". right?

- Ashok

|||

Ashok, Server B requires a license as well to get the Microsoft.SqlServer.Dts.Runtime dll.

The SSIS components are not redistributable like they were in DTS.

|||

Hi Phil,

How the SSIS components license and SSIS Services license works. I have to recheck installation of SSIS is it while installing SSIS I can pick only SSIS components or Service.

Thank you - Ashok

|||Just install the SSIS components. I think it's just one checkbox. You should not need the service, if you can ignore that.

Running SSIS for the first time

Hello,

If my SSIS runs every day, how can I "know" that I'm running it for the first time?

I need an SQL Select1 statement to execute the first day that my flow starts (to get all the old data) and after that SQL Select2 statement should always execute (to get the latest data).

How is this possable?

Thank you.

You could :

1. Run a SQL Task with SQL Statement = conditional expression on a flag value,

2. Set the true Output=Select2 and the false output=Select1

3. Get your SQL select1 to also update a flag to true in the database

HTH

Kar

|||Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed.|||

Thanks for your replies.

Is it possable to have a bool flag (fetchall = true/false) that is set in the beginning when SSIS is first started?

Is it possable to do this without having to write to a database?

Thank you.

|||

Sure,

Taht would be easy to implement. You can create a boolean variable in the package and then assign its value via command line when executing the package (/SET option of DTEXEC command). Then you can build/change the where clause of the source components based on the value of that variable.

Don't forget to provide an intial value to the variable as the designer requires it for validation prupose.

|||

"Run a SQL Task with SQL Statement = conditional expression on a flag value"

How is this done? Can you please give me an example?

Thank you.

|||

Even if I use a bool value that is stored in a database, how do I set it back to 0 when the SSIS service is stopped?

Would you recommend keeping the bool value in a database or storing it as a variable in SSIS?

Thank you.

|||

You could put the whole query in a variable, let's call it MyQuery, then use an expression in MyQuery variable to actually build the query at run time based on the other boolean variable.

You would need to figure out how to control and maintain the boolean varaible. I sgugested to build a table to track the load history; but that is up to you.

|||Rafael Salas:

"I suggested to build a table to track the load history"

I'm sorry, but can you please explain this for me how it would solve my problem?

My SSIS is supposed to run every day as a scheduled task.

==Start SSIS process==

1day: Send all the old data (1000 rows)

2day: Send 1 row

3day: Send 1 row

==Stop SSIS process==

Since I'm new to SSIS, I'm still very confused how to implement this.

What would be the most simple solution?

Thanks a lot!!

|||

Rafael Salas:

"Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed. "

Lets say I do this and log everything I do in a table.

==Start SSIS process==

1. 1000 rows

2. 1 row

3. 1 row

4. 1 row

==Stop SSIS process==

==Start SSIS process==

How can I know now what to do (get 1000 or 1 row) by looking at the table above that I've logged?

Maby Im misunderstanding

Thank you.

|||

I'm guessing you have some sort of WHERE clause on your select to determine what rows to retrieve, perhaps a modified date. Something like "WHERE modified_date >= @.last_date".

You'd store the maximum retrieved value for modified_date in a control table. Each time the package runs, you'd retrieve the last modified_date value from the control table, and use that for the @.last_date parameter.

|||

That is right John.

What I am suggestion is to log the latest date or ID you loaded in the previous execution; then the subsequent execution will use that value to filter the rows (in a where clause) and pick only 'new' rows.

Perhaps, I am not understanding your requirement. What would be the difference between the 'first run' and the following ones?

|||

Thank you for your patience

As I understand it, SSIS can be configured as a scheduled task.

That means that, f.ex. if I want my SSIS flow to be run at 18:00 every day, my flow will be executed at that time day after day.

When the database guys setup my SSIS flow to be run as a schedules task, Im guessing they pick my flow and say "start". When that happens, the first day at 18:00 I want to send all my old data (1000rows).

However, the next day at 18:00 (and the next, and the next...) I only want to send an update (1row).

If the database guy "stops" my scheduled task (SQL went down for some reasons) and then "starts" my flow again, I need to send all my old data the first day at 18:00 (1000rows), then the next day at 18:00 (and the day after that...) only an update (1row) etc...

Im still very puzzled how to do this.

If you have any suggestions, please let me know.

Thanks very much!!

|||

I think that I still really don't understand what you are attempting to accomplish, because this scenario doesn't make much sense to me. If you've already moved your 1000 old rows, why does it matter if the scheduled task is restarted? The rows were already moved.

However, assuming that you really do want to reset everything because of the scheduled task being stopped, I don't see an easy way to handle it in SSIS. SSIS has no way of knowing if the the scheduled task was stopped or not. You'd have to do something on the SQL Server database engine side.

Again, you might want to better explain the reason why you are trying to do this, as that might help us to come up with a better solution.

|||

I agree...Please provide a better explanation of your scenario.

Anyway, you could have am Execute SQL Task at the begining of the dataflow to check whatever you want and then based on that result decide what kind of process it will be run (Incremental, Vs initial).

Running SSIS for the first time

Hello,

If my SSIS runs every day, how can I "know" that I'm running it for the first time?

I need an SQL Select1 statement to execute the first day that my flow starts (to get all the old data) and after that SQL Select2 statement should always execute (to get the latest data).

How is this possable?

Thank you.

You could :

1. Run a SQL Task with SQL Statement = conditional expression on a flag value,

2. Set the true Output=Select2 and the false output=Select1

3. Get your SQL select1 to also update a flag to true in the database

HTH

Kar

|||Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed.|||

Thanks for your replies.

Is it possable to have a bool flag (fetchall = true/false) that is set in the beginning when SSIS is first started?

Is it possable to do this without having to write to a database?

Thank you.

|||

Sure,

Don't forget to provide an intial value to the variable as the designer requires it for validation prupose.

|||

"Run a SQL Task with SQL Statement = conditional expression on a flag value"

How is this done? Can you please give me an example?

Thank you.

|||

Even if I use a bool value that is stored in a database, how do I set it back to 0 when the SSIS service is stopped?

Would you recommend keeping the bool value in a database or storing it as a variable in SSIS?

Thank you.

|||

You could put the whole query in a variable, let's call it MyQuery, then use an expression in MyQuery variable to actually build the query at run time based on the other boolean variable.

You would need to figure out how to control and maintain the boolean varaible. I sgugested to build a table to track the load history; but that is up to you.

|||Rafael Salas:

"I suggested to build a table to track the load history"

I'm sorry, but can you please explain this for me how it would solve my problem?

My SSIS is supposed to run every day as a scheduled task.

==Start SSIS process==

1day: Send all the old data (1000 rows)

2day: Send 1 row

3day: Send 1 row

==Stop SSIS process==

Since I'm new to SSIS, I'm still very confused how to implement this.

What would be the most simple solution?

Thanks a lot!!

|||

Rafael Salas:

"Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed. "

Lets say I do this and log everything I do in a table.

==Start SSIS process==

1. 1000 rows

2. 1 row

3. 1 row

4. 1 row

==Stop SSIS process==

==Start SSIS process==

How can I know now what to do (get 1000 or 1 row) by looking at the table above that I've logged?

Maby Im misunderstanding

Thank you.

|||

I'm guessing you have some sort of WHERE clause on your select to determine what rows to retrieve, perhaps a modified date. Something like "WHERE modified_date >= @.last_date".

|||

That is right John.

Perhaps, I am not understanding your requirement. What would be the difference between the 'first run' and the following ones?

|||

Thank you for your patience

As I understand it, SSIS can be configured as a scheduled task.

That means that, f.ex. if I want my SSIS flow to be run at 18:00 every day, my flow will be executed at that time day after day.

However, the next day at 18:00 (and the next, and the next...) I only want to send an update (1row).

Im still very puzzled how to do this.

If you have any suggestions, please let me know.

Thanks very much!!

|||

Again, you might want to better explain the reason why you are trying to do this, as that might help us to come up with a better solution.

|||

I agree...Please provide a better explanation of your scenario.

sql

Tuesday, March 20, 2012

Running SQL Server DTS Package from ASP.NET page problem

I'm having some problems running a DTS package from my ASP.NET appllication. It runs prefectly in debug from my local machine but as soon as I move it to the web server I receive the following error:

Error: -2147467259
Source: Microsoft JET Database Engine
Description: The Microsoft Jet database engine cannot open the file '\\serverB\PayFiles\Pay3.xls'. It is already opened exclusively by another user, or you need permission to view its data.

The file is not opened by anyone else and the share permissions are full control for everyone.

My set up is as follows

serverA - Webserver & DB Server
serverB - File Server

The DTS package pulls in the file from serverB to a database table on serverA. The sa account is used for the database connection and connecting to the DTS package from the application. My code for running the DTS package is as follows -

Private Sub ExecutePackage(ByVal serverAs String,ByVal packageAs String,ByVal usernameAs String,ByVal passwordAs String)Const DTSSQLStgFlag_Default = 0Const DTSStepExecResult_Failure = 1Dim oPKGAs DTS.Package, oStepAs DTS.Step oPKG =New DTS.PackageDim sServerAs String, sUsernameAs String, sPasswordAs String Dim sPackageNameAs String, sMessageAs String Dim lErrAs Long, sSourceAs String, sDescAs String' Set Parameter Values sServer = server sUsername = username sPassword = password sPackageName = package' Load Package oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _ DTSSQLStgFlag_Default, , , , sPackageName)' Set Exec on Main ThreadFor Each oStepIn oPKG.Steps oStep.ExecuteInMainThread =True Next' Execute oPKG.Execute()' Get Status and Error MessageFor Each oStepIn oPKG.StepsIf oStep.ExecutionResult = DTSStepExecResult_FailureThen oStep.GetExecutionErrorInfo(lErr, sSource, sDesc) sMessage = sMessage &"Step " & oStep.Name &" Failed<br><br>Error: " & lErr &"<br>Source: " & sSource &"<br>Description: " & sDesc &"<br><br>"Else sMessage = sMessage &"Step " & oStep.Name &" Succeeded<br>"End If Next oPKG.UnInitialize() oStep =Nothing oPKG =Nothing' Display Results 'MsgBox(sMessage) lblOutput.Text = sMessageEnd Sub

I'm using windows authentication with identity impersonate set to true. If I change to use basic authentication and enter my login when I go to the site the DTS package runs successfully. So I assume that I'm seeing this 'double hop' issue.

serverA is for testing so I moved the DTS package and web application to serverB so now everything is on the same server and should eliminate the double hop? No! Works perfectly from my PC in debug, but from the web server the same error occurs. Changing to basic authentication does not workaround the issue on the live server.

The DTS package always works when run from Enterprise manager.

I know this is a premissions issue - does anyone know what I'm doing wrong? Hopefully I've supplied enough info, if not just ask!

You may want to use a windows service instead of a web page, as when the web page stops rendering the process will also stop.

|||

If I use a windows service will I still be able to give the user control over when the package is run?

|||

I've decided to use a stored procedure and DTSRun to do this instead and then simply execute the stored procedure from my page. Just in case anyone else wants to use it this is my code.

CREATE PROC [dbo].[sp_DTSRun] @.ServerNameVARCHAR(30),@.PackageNameVARCHAR(50)ASBEGINDECLARE @.ReturnValueint, @.Cmdvarchar(500)SET @.ReturnValue = -1SET @.Cmd ='dtsrun /S ' + @.ServerName +' /N ' + @.PackageName +' /E'--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUT--RETURN @.ReturnValueSELECT @.ReturnValue [Result]END

Private Sub ExecutePackage(ByVal serverAs String,ByVal packageAs String)Dim ExecuteAdapterAs New PAY_DAL.RunDTSTableAdapters.sp_DTSRunTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String = ExecuteAdapter.Execute(server, package)Select Case strResultCase 0 lblOutput.Text ="The package executed successfully."Case 1 lblOutput.Text ="The package failed - contact support."Case 2 lblOutput.Text ="2"Case 3 lblOutput.Text ="The package was cancelled by the user."Case 4 lblOutput.Text ="The utility was unable to locate the requested package. The package could not be found."Case 5 lblOutput.Text ="The utility was unable to load the requested package. The package could not be loaded."Case 6 lblOutput.Text ="The utility encountered an internal error of syntactic or semantic errors in the command line."End Select lblOutput.Visible =True End Sub

Just pass the package and server name in. To use a username and password remove the /E (trusted connection) and add /U and /P.

Tuesday, February 21, 2012

Running SQL Jobs manually

Does anyone know why as a sysadmin I am unable to run jobs
manually? The SQL agent runs under my account and the jobs
run fine on schedule but when I try to run them manually
nothing happens and nothing is logged in Event Viewer.Silly question - Are you refreshing the jobs node to see what activity is
taking place?
Are you gettting any entries under "View job history" for the job you are
trying to run?
--
Simon Worth
"Robert" <anonymous@.discussions.microsoft.com> wrote in message
news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
> Does anyone know why as a sysadmin I am unable to run jobs
> manually? The SQL agent runs under my account and the jobs
> run fine on schedule but when I try to run them manually
> nothing happens and nothing is logged in Event Viewer.|||Yes I am refreshing and no there is no history of the
attempts to run manually
>--Original Message--
>Silly question - Are you refreshing the jobs node to see
what activity is
>taking place?
>Are you gettting any entries under "View job history" for
the job you are
>trying to run?
>--
>Simon Worth
>
>"Robert" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
>> Does anyone know why as a sysadmin I am unable to run
jobs
>> manually? The SQL agent runs under my account and the
jobs
>> run fine on schedule but when I try to run them manually
>> nothing happens and nothing is logged in Event Viewer.
>
>.
>|||Did you try the stored proc 'sp_start_job' in query analyzer to see what the
output is or any error return values?
--
Simon Worth
<anonymous@.discussions.microsoft.com> wrote in message
news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
> Yes I am refreshing and no there is no history of the
> attempts to run manually
> >--Original Message--
> >Silly question - Are you refreshing the jobs node to see
> what activity is
> >taking place?
> >Are you gettting any entries under "View job history" for
> the job you are
> >trying to run?
> >
> >--
> >Simon Worth
> >
> >
> >"Robert" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
> >> Does anyone know why as a sysadmin I am unable to run
> jobs
> >> manually? The SQL agent runs under my account and the
> jobs
> >> run fine on schedule but when I try to run them manually
> >> nothing happens and nothing is logged in Event Viewer.
> >
> >
> >.
> >|||I ran the job using 'sp_start_job' and the message
said 'starting job successfully'. In EM when I refresh the
job it doesn't appear that the job ran. And I confirmed
that this particular job did not actually run.
>--Original Message--
>Did you try the stored proc 'sp_start_job' in query
analyzer to see what the
>output is or any error return values?
>--
>Simon Worth
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
>> Yes I am refreshing and no there is no history of the
>> attempts to run manually
>> >--Original Message--
>> >Silly question - Are you refreshing the jobs node to
see
>> what activity is
>> >taking place?
>> >Are you gettting any entries under "View job history"
for
>> the job you are
>> >trying to run?
>> >
>> >--
>> >Simon Worth
>> >
>> >
>> >"Robert" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
>> >> Does anyone know why as a sysadmin I am unable to run
>> jobs
>> >> manually? The SQL agent runs under my account and the
>> jobs
>> >> run fine on schedule but when I try to run them
manually
>> >> nothing happens and nothing is logged in Event
Viewer.
>> >
>> >
>> >.
>> >
>
>.
>|||what about in the SQL Server logs (you said you checked the event viewer
logs), are their any entries in there for failed jobs, or problems starting
jobs?
--
Simon Worth
<anonymous@.discussions.microsoft.com> wrote in message
news:43aa01c520ed$172250b0$a601280a@.phx.gbl...
> I ran the job using 'sp_start_job' and the message
> said 'starting job successfully'. In EM when I refresh the
> job it doesn't appear that the job ran. And I confirmed
> that this particular job did not actually run.
> >--Original Message--
> >Did you try the stored proc 'sp_start_job' in query
> analyzer to see what the
> >output is or any error return values?
> >
> >--
> >Simon Worth
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
> >> Yes I am refreshing and no there is no history of the
> >> attempts to run manually
> >> >--Original Message--
> >> >Silly question - Are you refreshing the jobs node to
> see
> >> what activity is
> >> >taking place?
> >> >Are you gettting any entries under "View job history"
> for
> >> the job you are
> >> >trying to run?
> >> >
> >> >--
> >> >Simon Worth
> >> >
> >> >
> >> >"Robert" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
> >> >> Does anyone know why as a sysadmin I am unable to run
> >> jobs
> >> >> manually? The SQL agent runs under my account and the
> >> jobs
> >> >> run fine on schedule but when I try to run them
> manually
> >> >> nothing happens and nothing is logged in Event
> Viewer.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Check the logs for SQL Agent as well to see if there is anything listed in
there.
--
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:O21On$OIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> what about in the SQL Server logs (you said you checked the event viewer
> logs), are their any entries in there for failed jobs, or problems
starting
> jobs?
> --
> Simon Worth
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:43aa01c520ed$172250b0$a601280a@.phx.gbl...
> > I ran the job using 'sp_start_job' and the message
> > said 'starting job successfully'. In EM when I refresh the
> > job it doesn't appear that the job ran. And I confirmed
> > that this particular job did not actually run.
> > >--Original Message--
> > >Did you try the stored proc 'sp_start_job' in query
> > analyzer to see what the
> > >output is or any error return values?
> > >
> > >--
> > >Simon Worth
> > >
> > >
> > ><anonymous@.discussions.microsoft.com> wrote in message
> > >news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
> > >> Yes I am refreshing and no there is no history of the
> > >> attempts to run manually
> > >> >--Original Message--
> > >> >Silly question - Are you refreshing the jobs node to
> > see
> > >> what activity is
> > >> >taking place?
> > >> >Are you gettting any entries under "View job history"
> > for
> > >> the job you are
> > >> >trying to run?
> > >> >
> > >> >--
> > >> >Simon Worth
> > >> >
> > >> >
> > >> >"Robert" <anonymous@.discussions.microsoft.com> wrote in
> > >> message
> > >> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
> > >> >> Does anyone know why as a sysadmin I am unable to run
> > >> jobs
> > >> >> manually? The SQL agent runs under my account and the
> > >> jobs
> > >> >> run fine on schedule but when I try to run them
> > manually
> > >> >> nothing happens and nothing is logged in Event
> > Viewer.
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>|||i did see one entry for my logon. Error: 15457, Severity:
0, State: 1
Configuration option 'show advanced options' changed from
1 to 1. Run the RECONFIGURE statement to install.
Do you know what this means?
>--Original Message--
>Check the logs for SQL Agent as well to see if there is
anything listed in
>there.
>--
>Simon Worth
>
>"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote
in message
>news:O21On$OIFHA.3628@.TK2MSFTNGP10.phx.gbl...
>> what about in the SQL Server logs (you said you checked
the event viewer
>> logs), are their any entries in there for failed jobs,
or problems
>starting
>> jobs?
>> --
>> Simon Worth
>>
>> <anonymous@.discussions.microsoft.com> wrote in message
>> news:43aa01c520ed$172250b0$a601280a@.phx.gbl...
>> > I ran the job using 'sp_start_job' and the message
>> > said 'starting job successfully'. In EM when I
refresh the
>> > job it doesn't appear that the job ran. And I
confirmed
>> > that this particular job did not actually run.
>> > >--Original Message--
>> > >Did you try the stored proc 'sp_start_job' in query
>> > analyzer to see what the
>> > >output is or any error return values?
>> > >
>> > >--
>> > >Simon Worth
>> > >
>> > >
>> > ><anonymous@.discussions.microsoft.com> wrote in
message
>> > >news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
>> > >> Yes I am refreshing and no there is no history of
the
>> > >> attempts to run manually
>> > >> >--Original Message--
>> > >> >Silly question - Are you refreshing the jobs node
to
>> > see
>> > >> what activity is
>> > >> >taking place?
>> > >> >Are you gettting any entries under "View job
history"
>> > for
>> > >> the job you are
>> > >> >trying to run?
>> > >> >
>> > >> >--
>> > >> >Simon Worth
>> > >> >
>> > >> >
>> > >> >"Robert" <anonymous@.discussions.microsoft.com>
wrote in
>> > >> message
>> > >> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
>> > >> >> Does anyone know why as a sysadmin I am unable
to run
>> > >> jobs
>> > >> >> manually? The SQL agent runs under my account
and the
>> > >> jobs
>> > >> >> run fine on schedule but when I try to run them
>> > manually
>> > >> >> nothing happens and nothing is logged in Event
>> > Viewer.
>> > >> >
>> > >> >
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>

Running SQL Jobs manually

Running Software from SQL Server/ DTS Package / Job Schedule

Why won't SQL Server Agent GetObject or CreateObject.
I have a scheduled Job that runs a DTS Package which runs an executable
which uses GetObject and CreateObject to connect to an instance of Solid Edg
e
software. It works fine if I run the DTS manually. It doesn't work if I ru
n
the Job.
I know about RunAs.exe but can't use it because it prompts for a password.
This is the code that connects to the software;
________________________________________
__
'Connect to a instance of Solid Edge.
Set objSEApp = GetObject(, "SolidEdge.Application") 'It was already open
If Err Then
Err.Clear
Set objSEApp = CreateObject("SolidEdge.Application") 'It wasn't open
booSEAppWasOpen = False
Else
booSEAppWasOpen = True
End If
objSEApp.Visible = False
objSEApp.DisplayAlerts = FalseCheck the account that SQL Server Agent runs under. From here it looks like
that account doesn't have the permissions needed. If you run the DTS
manually, it will run under your account, not under the SQL Server Agent
account.
Jacco Schalkwijk
SQL Server MVP
"Matt Sonic" <MattSonic@.discussions.microsoft.com> wrote in message
news:84D203EE-7C3B-4218-A5EA-D2328EC2F02D@.microsoft.com...
> Why won't SQL Server Agent GetObject or CreateObject.
> I have a scheduled Job that runs a DTS Package which runs an executable
> which uses GetObject and CreateObject to connect to an instance of Solid
> Edge
> software. It works fine if I run the DTS manually. It doesn't work if I
> run
> the Job.
> I know about RunAs.exe but can't use it because it prompts for a password.
> This is the code that connects to the software;
> ________________________________________
__
> 'Connect to a instance of Solid Edge.
> Set objSEApp = GetObject(, "SolidEdge.Application") 'It was already open
> If Err Then
> Err.Clear
> Set objSEApp = CreateObject("SolidEdge.Application") 'It wasn't open
> booSEAppWasOpen = False
> Else
> booSEAppWasOpen = True
> End If
> objSEApp.Visible = False
> objSEApp.DisplayAlerts = False

Friday, March 30, 2012

Runtime error 3704

Wednesday, March 28, 2012

Running Views on SP2 Slower than SP3

Running Views on SP2 Slower than SP3

Running Views on SP2 Slower than SP3

Friday, March 23, 2012

running the upgrade step from version 610 to version 611 fails

Wednesday, March 21, 2012

Running SSIS package using .Net code

Running SSIS for the first time

Running SSIS for the first time

Tuesday, March 20, 2012

Running SQL Server DTS Package from ASP.NET page problem

Tuesday, February 21, 2012

Running SQL Jobs manually

Running SQL Jobs manually

Running SQL Jobs manually

Running Software from SQL Server/ DTS Package / Job Schedule

db files

Blog Archive

About Me