Need dtsrun help

I am trying to run two dts jobs using a batch file obviously using dtsrun but im having trouble. I've never attempted this before and it looked easy but not as easy as I thought.

I have two dts jobs, call them package1 and package2. I also saved these as .dts files. I made a .bat file and put it in the same location as the .dts files.

My batch file looks like this:

dtsrun /F package1
dtsrun /F package2
pause

This does not work and I get the following:

C:\WINDOWS>dtsrun /F package1
DTSRun: Loading...

Error: -2147287038 (80030002); Provider Error: 0 (0)
Error string: The system cannot find the file specified.

Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 704

Can anyone help and tell me what I am doing wrong?

Thanks

Neil.What happens when you fully path out the package files?|||check this link ,it may help u.
http://www.sqlteam.com/item.asp?ItemID=16512|||By fully path I guess you mean C:\folder\xyz etc...?

If so then I just tried that and still the same error.|||Oh dear, I'm definately having a slow day today...

I had a thought about where I was running the batch file from. I was running it from my PC even though it is located on the server. I then remote connected to the server and ran it there, IT WORKS!

Sorry for trouble but thanks for the link

NeilAsql

Need Dirxn --> How to schedule recurring stored procedure executio

Hi,
I need to schedule a stored procedure to run on a recurring basis. It would
be acceptable if the procedure ran 1x per minute though it would be better if
I could schedule & re-schedule procedure start time from within a trigger or
other stored procedure. The recurring stored procedure is NOT an
administrative maintenance task. The recurring procedure calls other stored
procedures across servers to gather data for the application.
Question:
1) What's the preferred way to schedule & reschedule recurring execution of
an application's stored procedures? From reading Books Online I gather that
SQL Server Agent and DTS can both schedule tasks? Is one of those two
approaches appropriate for my needs?
--RichardRichard,
You can place your stored proc invocation into a SQLAgent T-SQL job step.
You can invoke a SQLAgent job using sp_start_job. You can schedule this
job by creating a SQLAgent Job Schedule. You can change the Job Schedule
using sp_update_jobschedule.
So, your "other" stored proc can invoke sp_update_jobschedule to change
the frequency that the job executes.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Richard wrote:
> Hi,
> I need to schedule a stored procedure to run on a recurring basis. It would
> be acceptable if the procedure ran 1x per minute though it would be better if
> I could schedule & re-schedule procedure start time from within a trigger or
> other stored procedure. The recurring stored procedure is NOT an
> administrative maintenance task. The recurring procedure calls other stored
> procedures across servers to gather data for the application.
> Question:
> 1) What's the preferred way to schedule & reschedule recurring execution of
> an application's stored procedures? From reading Books Online I gather that
> SQL Server Agent and DTS can both schedule tasks? Is one of those two
> approaches appropriate for my needs?
> --Richard
>|||I agree with Mark, use SQL Agent for this.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u7AHIUE3EHA.2312@.TK2MSFTNGP15.phx.gbl...
> Richard,
> You can place your stored proc invocation into a SQLAgent T-SQL job step.
> You can invoke a SQLAgent job using sp_start_job. You can schedule this
> job by creating a SQLAgent Job Schedule. You can change the Job Schedule
> using sp_update_jobschedule.
> So, your "other" stored proc can invoke sp_update_jobschedule to change
> the frequency that the job executes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Richard wrote:
> > Hi,
> >
> > I need to schedule a stored procedure to run on a recurring basis. It
would
> > be acceptable if the procedure ran 1x per minute though it would be
better if
> > I could schedule & re-schedule procedure start time from within a
trigger or
> > other stored procedure. The recurring stored procedure is NOT an
> > administrative maintenance task. The recurring procedure calls other
stored
> > procedures across servers to gather data for the application.
> >
> > Question:
> >
> > 1) What's the preferred way to schedule & reschedule recurring execution
of
> > an application's stored procedures? From reading Books Online I gather
that
> > SQL Server Agent and DTS can both schedule tasks? Is one of those two
> > approaches appropriate for my needs?
> >
> > --Richard
> >

Need Dirxn --> How to schedule recurring stored procedure executio

Hi,
I need to schedule a stored procedure to run on a recurring basis. It would
be acceptable if the procedure ran 1x per minute though it would be better if
I could schedule & re-schedule procedure start time from within a trigger or
other stored procedure. The recurring stored procedure is NOT an
administrative maintenance task. The recurring procedure calls other stored
procedures across servers to gather data for the application.
Question:
1) What's the preferred way to schedule & reschedule recurring execution of
an application's stored procedures? From reading Books Online I gather that
SQL Server Agent and DTS can both schedule tasks? Is one of those two
approaches appropriate for my needs?
--Richard
Richard,
You can place your stored proc invocation into a SQLAgent T-SQL job step.
You can invoke a SQLAgent job using sp_start_job. You can schedule this
job by creating a SQLAgent Job Schedule. You can change the Job Schedule
using sp_update_jobschedule.
So, your "other" stored proc can invoke sp_update_jobschedule to change
the frequency that the job executes.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Richard wrote:
> Hi,
> I need to schedule a stored procedure to run on a recurring basis. It would
> be acceptable if the procedure ran 1x per minute though it would be better if
> I could schedule & re-schedule procedure start time from within a trigger or
> other stored procedure. The recurring stored procedure is NOT an
> administrative maintenance task. The recurring procedure calls other stored
> procedures across servers to gather data for the application.
> Question:
> 1) What's the preferred way to schedule & reschedule recurring execution of
> an application's stored procedures? From reading Books Online I gather that
> SQL Server Agent and DTS can both schedule tasks? Is one of those two
> approaches appropriate for my needs?
> --Richard
>
|||I agree with Mark, use SQL Agent for this.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u7AHIUE3EHA.2312@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Richard,
> You can place your stored proc invocation into a SQLAgent T-SQL job step.
> You can invoke a SQLAgent job using sp_start_job. You can schedule this
> job by creating a SQLAgent Job Schedule. You can change the Job Schedule
> using sp_update_jobschedule.
> So, your "other" stored proc can invoke sp_update_jobschedule to change
> the frequency that the job executes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Richard wrote:
would[vbcol=seagreen]
better if[vbcol=seagreen]
trigger or[vbcol=seagreen]
stored[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]

need brief answers

what are pseudo tables
how queries run by MSDE
how stored procedures run in backend by MSDE
how triggers run by MSDE
where triggers and stored proc stored in MSDE and in which form
where logs are being maintained of transactions/DML statement by default

what are pseudo tables - Never heard of them, you may mean Derived Tables (A table created in a query that lives only during that query), or Views (a sql statement describing a virtual table)

how queries run by MSDE - You have to use a tool, like Query Analyzer or a client application to send queies to the MSDE engine

how stored procedures run in backend by MSDE - Same as above

how triggers run by MSDE - Triggers are run automatically by the database engine when the proper event happens to a table with a trigger on it.

where triggers and stored proc stored in MSDE and in which form - They are stored in system tables, you access them to create and edit them through Enterprise Manager or writing a DDL query in Query Analyzer

where logs are being maintained of transactions/DML statement by default - The log file(s) are where ever you tell the database to put them when you create a database. By default I think they are in the <SQl Server install location>\Data folder.

|||I sometimes use the term "pseudo table" to describe the INSERTED and DELETED logical tables that are available in triggers. I am not sure if that is what applies here or not.|||ah, good point. I have never heard that term though, always heard them referred to as virtual tables.

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topic Creating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topicCreating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need Alternative

The following query is being run on SQL Server 2000, so I do not have the ability to use the OpenRowset (BULK ....) option. I am trying to modify this query so that it does not treat the first record as a header record:

select *
from OpenRowset
( 'MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:\aDir; HDR=NO; ' ,
' SELECT * FROM D:\aDir\Jj.TXT '
)

Is my best option to switch this over to a BULK INSERT operation? I am looking for options.

hmm do you have to option to change the contents of the .txt or .csv file?
are you just trying to insert the record to a table from that data source?|||No, I cannot change the contents of the .TXT file. Right now, the process processes the data into columns. The file has tilde characters for column separators but is also each field is fix length.|||do have other process after retrieving the data from the source like you have to update a record on the result set etc.?

or are you just going to insert the result set directly to the database? if yes, you can use BCP instead|||Yes, there is a whole army of processes that run after this process -- updates and selects but little or no deleting|||how bout contention issues? will your process run on multiple users and simultaneously?

imo, i would go with bcp or bulk insert to a table variable or temp table|||No, it is a batch process that runs single thread around 3AM when the server is otherwise quiet. I was looking at bulk insert but I want other opinions and I appreciate you working with me.|||

Kent,

This is a kludge, but ...

Create a file with a single row, dummy values.

Concatenate the data file to the 'header' file. Something like...

c:>\copy DummyHeader.txt /A + DataFile.txt /A NewDataFile.txt /A

|||

Thanks, Arnie. In fact, this is presently what I have done; however, since my input files can be a few gigs this is not my first choice, but indeed, it does work. :-)

|||nice idea arnie. i think that would work too. kent's problem now would lead to comparing performance issues beween bcp, bulk insert and openrowset|||

My other thought on the header issue was to get the mainframe job that creates the file to add a dummy header -- iff I can get the help.

[ :-) You are right: it IS PAINFUL! ]

( Now I gotta scrounge up another 10 GB of space so I can run the test. Can you say COMPRESS? )

|||

get the mainframe job that creates the file to add a dummy header

I feel your pain.

|||Kent,

Give the Jet driver a try. For Jet's text engine, whether or not the first row is treated as a header row depends on a registry entry. If you change it, you should have luck. (I don't recommend using xp_regwrite, but it's easier to post this T-SQL-only solution.)

Code Snippet

exec master..xp_regwrite
@.rootkey='HKEY_LOCAL_MACHINE',
@.key='SOFTWARE\Microsoft\Jet\4.0\Engines\Text',
@.value_name='FirstRowHasNames',
@.type='REG_BINARY',
@.value=0x00
go

select F1 as MyColumnName
from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'text; Database=C:\Data;' ,
'SELECT * FROM C:\Data\yourfile.txt'
)

Steve Kass
Drew University
http://www.stevekass.com

|||Excellent information Steve -THANKS!|||

Thanks Steve; I'll give it a roll.

Kent

( That seemed to work fine with all the small test files; not to try it with the large files. )

Well, it works, but it was ... [just caught a nut flush on the river].. significantly slower than the previous version. More testing is required.

Need advice on buiding a dynamic TSQL command to run against all DB's

Hi All,

I am putting together a series of TSQL statements to be used as part of a DB maintenance plan on SQL server 2005 on Windows Server 2003. I know that the statement itself works against a single database. The intention is to write one routine that will execute on all non-system DB's. Here are the system specs:

__

Microsoft SQL Server Management Studio 9.00.2047.00

Microsoft Analysis Services Client Tools 2005.090.2047.00

Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324

1447)

Microsoft MSXML 2.6 3.0 6.0

Microsoft Internet Explorer 7.0.5730.11

Microsoft .NET Framework 2.0.50727.42

Operating System 5.2.3790

__

So I've actually tried two methods. The first is without using sp_MSforeachdb

__

-- Declare variables

Declare @.dbname sysname,

@.cmd varchar(4096)

-- Select DB Names

Select @.dbname = min(name) from master.dbo.sysdatabases

-- Loop through each database.

WHILE @.dbname is not null -- <-- (NEED TO NOT INCLUDE SYSTEM DB'S HERE)

Begin

Select @.cmd = 'use ' + @.dbname +

'
-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1)

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1)

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1)

-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in

('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DB's

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

'

End -- (NOT SURE IF THIS IS IN THE RIGHT PLACE)

-- Execute the command

Exec (@.cmd)

__

The second attempted method was trying to use sp_MSforeachdb. I modified the following to successfully loop a simple print command to ensure that the looping occurs:

__

declare @.cmd1 varchar(4096)

set @.cmd1 =
'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''

exec sp_MSforeachdb @.command1=@.cmd1

-- THIS WORKS

__

However after many attempts, I haven't been able to get the syntax right to run my

statements abonve using this method. Seems very unforgiving with spaces and apostrophes.

Any advice is much appreciated. Thank you in advance.

Rick B

Hi Rick,

Try something like this:

--THIS CREATES ONE TEMP TABLE WITH ALL NON SYSTEM DATABASES

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM from master.dbo.sysdatabases

where SID<>0x01;

DECLARE @.ID INT, @.DBNAME VARCHAR(250)

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > 0;

WHILE @.ID IS NOT NULL

BEGIN

SELECT @.DBNAME=DBNAME

FROM TEMP_CURSOR

WHERE ID = @.ID;

EXEC(' USE ' + @.DBNAME + '

-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);

-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in ('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DB's

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

')

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > @.ID;

END

DROP TABLE TEMP_CURSOR;

I thinks this can do your trick

Regards,

|||

One of the ways I use to test the syntax of dynamic sql is to print the SQL rather than run it. Then you can see the outputted SQL and see if its correct and if not, where you have gone wrong

hope this helps

|||

Hey Luis

Upon execution against Master, I'm receiving the following error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'DBNAME'.

After a few tweaks, the following is the current syntax that I am using based off of Luis's feedback. Can anyone ID additional syntax errors? Thanks in advance. Rick B.

__

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

DECLARE @.ID INT, @.DBNAME VARCHAR(250)

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > 0;

WHILE @.ID IS NOT NULL

BEGIN

SELECT @.DBNAME=DBNAME

FROM TEMP_CURSOR

WHERE ID = @.ID;

EXEC(' USE ' + @.DBNAME + '

-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);

-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in (TableX,TableY,TableZ)
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DBs

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

')

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > @.ID;

END

DROP TABLE TEMP_CURSOR;

__

|||

Replace this statement:

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

with this one

SELECT IDENTITY(INT, 1,1) AS ID, NAME AS DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

Hope it works

Need advice

I would like to do the following:
I don=B4t want to run SQL Jobs on my sql server. I will be using an application called ITO. This application will have a user assigned to it and will execute the jobs and monitor it (TNG Style). Anyways this is the problem:
To execute "exec sp_start_job @.job_name", the user must be the owner of the job.
Now lets say your job is an sql query. It has (for example) deletes. This user will need priviledges to be able to execute those queries.
The problem with this is that this application executes the job through a batch:
it lauches the batch file which contains the commands necessary: isql -S servname -U username -P password -
i "inputfile".
The problem is that a user=B4s account (that has priviledges) and its password is out in the open. Is there anyway i can go arround this. Any other command that can execute the job and dont need priviledges. I was thinking about SETUSER but for SETUSER you need to be sysadmin.
Does anyone have any idea?
:)Not sure Im following. But in isql -E means use a trusted connection so if your using Windows authentication you wont need to specify the user/ password. Does this help?
>--Original Message--
>I would like to do the following:
>I don=B4t want to run SQL Jobs on my sql server. I will be >using an application called ITO. This application will >have a user assigned to it and will execute the jobs and >monitor it (TNG Style). Anyways this is the problem:
>To execute "exec sp_start_job @.job_name", the user must be >the owner of the job. >Now lets say your job is an sql query. It has (for >example) deletes. This user will need priviledges to be >able to execute those queries. >The problem with this is that this application executes >the job through a batch:
>it lauches the batch file which contains the commands >necessary: isql -S servname -U username -P password -
>i "inputfile".
>The problem is that a user=B4s account (that has >priviledges) and its password is out in the open. Is there >anyway i can go arround this. Any other command that can >execute the job and dont need priviledges. I was thinking >about SETUSER but for SETUSER you need to be sysadmin.
>Does anyone have any idea?
>:)
>.
>

Navigation is not working correctly when I apply security on RS.

The site is working, all of the reports run, but all of the navigation drops
the https navigation to http links which will not render the application.
Does anyone know where I missed the https to configure as a secure
application?This is everything in my navigation
Home | My Subscriptions | Site Settings | Help
and if I go to the subfolders the home link for the subfolders does it too..
Any help will be nice!!
"Matthew" wrote:
> The site is working, all of the reports run, but all of the navigation drops
> the https navigation to http links which will not render the application.
> Does anyone know where I missed the https to configure as a secure
> application?
>