knowing which jobs/dts/SSIS affect which tables

hi, we were doing something and began to wonder which jobs/dts were
affecting a certain table (we use SQL 2000)... is there anyway to check
this?

and have the features for such dependency analysis been added for SQL
2005?I've never tried this, but you might be able to trap it using Profiler;
set up a log and filter on object_id.

http://www.microsoft.com/technet/ar...9.mspx?mfr=true

Post back and let us know if that works for you.

Stu

metaperl wrote:
> hi, we were doing something and began to wonder which jobs/dts were
> affecting a certain table (we use SQL 2000)... is there anyway to check
> this?
> and have the features for such dependency analysis been added for SQL
> 2005?sql

Kick off Stored Procedure to run Nightly?

I have a stored procedure that updates a table. I want that table to be updated nightly. I looked into the DTS package but it seems a bit difficult. Is there some other schedule in Enterprise that I can kick off my stored proc without having to build a DTS package?

In DTS, they ask for all of these symbols and want the query, but my code is already in stored proc form.

Help?What you need to do is use SQL Agent. You can schedule the job to run nightly at your prferred time. When you create a new SQL Agent job, you then ad a job step. In this job step you specify the SQL command you want to run. I'm assuming you are using SQL Server 2000 since you refer to DTS. Using SQL Server Enterprise manager, expand the SQL Server you are working with, then expand the 'Managment' tree, then expand 'SQL Agent' tree and right click on Jobs and choose New Job... The tabs here should be self explanatory.|||Ok I see where the job steps are. In the command window, I can type in my proc name sp_DailyOrders and it will know how to kick it off?|||In the step name tab, just give the step a name like 'Execute procedure', make sure the database is the correct database where your procedure lives, and type in exec sp_your_proc_name in the command section. Then move to the Scheduile tab and click the button to add a new schedule and choose the frequency etc that you want the job to tun. If you have operators setup on your server, then you can use the notification tab to have the job email you when it completes or fails.|||Thanks soooooooo much! You are awesome!

Josql

Kick off a DTS Package from ASP?

Is it possible to execute a DTS Package from ASP?

For example, if a user went to my website and clicked a button, it'd execute the DTS package? Thanks.This dts needs to kick off from asp or asp.net?

There is a stored procedure in the master db that can do this. I cannot remember the name, but I remember finding it on some SQL Server site. I will try to track it down again.|||Baxicall you have to use DTS.Package object to run those!

See belwo URL that has some code example on how to do it!

http://support.microsoft.com/default.aspx?scid=kb;en-us;252987

http://www.asp101.com/articles/carvin/dts/default.asp

Hope it helps!|||It was ASP.NET, I apologize for not mentioning it. Will the cited examples work in .NET?|||Here is the one related to that
http://support.microsoft.com/default.aspx?scid=kb;en-us;321525

But I haven't tried at (As my old app which I made using ASP)

Keyboard not working (backspace and arrow keys) when viewing 2000 DTS packages from 2005 (ba

I know my problem is not directly associated w/ SSIS, so please forgive the post here. I figured this would be the "most" appropriate place to post this challenge.

We're attempting to edit a 2000 DTS package in Design mode from within the 2005 SQL Management Studio. To do this we downloaded/installed the following packages from the MS download site:

Microsoft SQL Server 2000 DTS Designer Components
Microsoft SQL Server 2005 Backward Compatibility Components

We're able to open the package in design mode from the Management Studio (Server > Management > Legacy > Data Transformation Services > DTS Package Name). But, when the DTS designer is open, the backspace and arrow keys won't respond in the Management Studio. They begin responding when we close the DTS designer. There are no error messages when we close the DTS designer.

Does anyone know of a fix or a workaround? Any help would be greatly appreciated.

Thanks, Mike

Do you have the Logitech MX5000 BlueTooth keyboard/mouse? I encountered this problem just yesterday and assumed it was my bluetooth connection going in and out.

That is very strange...

|||Nope, I have a wired Compaq keyboard. My colleague has the same problem w/ a different brand of wired keyboard.|||If you do a search there are a lot of people that have had problems with this component. It hosed enterprised manager on my machine and one of my co-workers. Even when it installed correctly a bunch of us still got errors opening packages. So far the only solution I've heard is to grin and bear it.|||

Thanks for the feedback, this was identified as a bug and was fixed. The fix will be in the next release after SP1

wenyang

|||

Is this fix out yet?

|||

I saw a reference to this recently which indicated it was fixed in SP2, or the associated feature pack. That is currently released as a CTP, so you may wish to risk it. I cannot find the source niow, which is rather annoying...

Download details: Feature Pack for SQL Server 2005 SP2 CTP
(http://www.microsoft.com/downloads/details.aspx?FamilyID=7A9AD90F-7F95-4369-A206-E84053D63FD3&displaylang=en)

|||

Thanks,

but unfortunately, the same behavior is happening with the CTP version of the dts designer :(

Keep speed

Hi...

I'm inserting and deleting about 30 000 records into 2 tables each day - import them from a text file using DTS.

The users add about 1000 records a day using Access and Windows .NET frontends...

Which TSQL commands should I run frequently to keep the database up to speed ?

I'm doing the following ... do you know of anything else?

Backup LOG MyDataBase WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (MyDataBase , 40)
GO

Backup LOG tempdb WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (tempdb, 70)
GO

THANKS!!!!!!!!!!!!!!!

Dave

There are a lot of different things you can do to keep the speed of your database up, such as:

Set the database to Simple Recovery and enable Auto Shrink, this will perform the same function you are doing with your DBCC Shrink commands. There are quite a few debates as to whether or not Simple Recovery and Auto Shrink impact performance but I have not seen any negative impacts myself.

Partition your database across multiple physical drives, this can dramatically improve performance

If you are using SAN disk, properly align the sector boundaries of your disk, here is a rather large document on the subject but it's a good one

http://www.hertenberger.co.za/resources/diskpar.pdf#search='how%20to%20use%20diskpar'

Delete any data older than "x" days from your tables, which it sounds like you may already be doing

Dedicate "x" amount of RAM to your instance

Dedicate "x" number of CPU's to your instance

The list goes on but that is a few examples.

|||Simple recovery model will not impact performance. However, the autoshrink certainly will. If the autoshrink kicks off when you are trying to process other queries, it will definitely slow everything down.|||

Hi Dave,

What comes to mind immediatly is that there will be a potentially large amount of fragmentation of tables/indexes in your db. This is because: a) you are performing a large amount of deletes and inserts often and b) shrinkdatabase introduces logical fragmentation.

So, I would recommend you run dbcc showcontig on your main tables, then perform rebuilds as needed with either DBCC DBREINDEX or DBCC INDEXDEFRAG. As an aside, if there has been a large amount of fragmentation on several tables, make sure your statistics are up to date, and run sp_recompile on the tables in question so that any stored procs you have can make use of the new stats immediatly.

Cheers
Rob

|||

Hi, Lesego.

If you're going to be doing queries agianst these tables that you're adding and removing data from, it's probably a good idea to update the statistics on the table. UPDATE STATISTICS is the command to use, and you'll want to run it against any statistics the table has -- you can find those most easily by exploring in the object browser in managemnet studio, but you'll have one for each index on the table, plus any that you've created yourself, plus any that the server has created automatically.

UPDATE STATISTICS might not be too important if you're selecting data directly from the table. But it will be very important if you are using the table that's the target of your insert/delete batch job in any JOINs with other tables. The query optimizer makes many decisions about how to best execute a statement based on information it can gather from the statistics on the table.

Hope that helps, and do let us know if you have more follow-up questions.

.B ekiM