HELP DTS problems !

I had some DTS packages that are scheduled to run as a job every week. They have been running fine for about 4 months.

One of the jobs failed in the weekend. i was normally able to run the job by logging into the server. The job failed instantenously.

I can't even run the DTS package. I am registered on my enterprise manager as an administrator as well. I tried registering as sa but it comes with the same result. i do not have any aliases. and the DTS packages are only working within the indivdual server. The errror messsage is :-

[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied

Any help would be greatly appreciated.mmmm...have you checked that all your connection inside the DTS point to a valid server? Can you connect to the server that you use inside the DTS?|||i actually created another new package and it works.
so

1. the old package does not run
2. The new package runs fine.

Do you think that my msdb database is corrupted ? or is it the individual databases ?|||Right-mouse click on the package and select "Versions". See what that shows you (busy fingures, man, busy fingures ;))|||Did that, the packages haven't been modified for the last 6 months.
I think i might restore the msdb database and go from there.|||Check the schedule relationed with your DTS package in SQL Server agent manager.See whether the owner of job had been changed or not.|||The SQL Server agent is being run as a domain user who is an administrator on the SQL Server.

Currently merging our backup tape to get the msdb database.
There was actually some patching of the servers in the weekend and one of the domain controllers was rebooted.

Does anyone think that there is some relation with the domain account, sql server agent and my dts packages are not working|||Things don't happen on their own. Some had to have been changed for the "anomaly" to happen.|||I restored the msdb database still the same error

[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied

the UDLs i believe i not working properly. Does anyone know about UDL corruption?

Would restoring the master database help ?|||Is there anything of note in your server logs? What shows up in the job history (especially if you show details)?

Have you tried to execute the package one step at a time, to see what fails? If you have multiple connections, it might not be failing where you'd expect.

Once you know which connection is failing, try to make a connection using the same credentials via Query Analyzer or OSQL.EXE to see if you get a more useful diagnostic message.

If push comes to shove, export your DTS package to a structured storage file and post it... Maybe someone here can see something in there that might help.

I'd exhaust every other avenue before even considering restoring master. That would be an absolute last ditch option for me.

-PatP|||Restoring MASTER would have no affect on success or failure of a DTS package. There NOTHING in MASTER that relates to DTS, NOTHING!..Having said that, - actually there is everything, - databases, logins, linked servers...I'd save the package as a BAS (not structured, Pat) and see what's going on there.|||When you restore the msdb database from a different server, u restore the jobs, dtspackages etc. Does any part of the system tables point to previous server. ? like sysjobs.

help : Update statement

Here's the statement I need some help with:

SELECT UID FROM sandbox.jobs WHERE username = 'username' order by UID desc
FROM sandbox.timespent;
UPDATE sandbox.timespent
SET UID = UID, Username = 'username', Job_Description = 'test_job'
WHERE Username = 'username'
;

what' i'm trying to do is insert the job description to the most recent entry (which is why i sorted) but it's adding the job description everywhere it sees username (which is more than once) so i need it to update the latest line

something like:

UID | Username | Job description
1 username test_job
2 username test_job1
3 username test_job2

when it updates it should add to UID 3 since it's the most recent. if anyone knows how to write the update statement please let me know. THANK YOU!

What determines if a row is 'most recent'?

Is it the larger UID?

What are the other columns in the tables?

|||I'm not sure how to make it so it selects the most recent. I wanted to sort it and select the first one (which would be most recent) but I'm not sure how to select the first one.

SELECT UID FROM sandbox.jobs WHERE username = 'username' order by UID desc LIMIT 1
FROM sandbox.timespent;
UPDATE sandbox.timespent
SET UID = UID, Username = 'username', Job_Description = 'test_job100'
WHERE UID = UID
;

that's what i wanted it to do, but instead of
WHERE UID = UID i want it to be something like WHERE UID = "most recent UID" some how

|||

So how do you determine which is the 'most recent UID'?

Is is the largest UID value? (It's still not clear.)

|||alright i'll try to explain myself better... I'm writing a program in vb.net

the table looks something like this:

UID | Username | Job | PLC_option | PLC_software
0 user1 9 option1 software1

that's what I want the data to look like. When the program runs, it adds a new UID and the users username to the table... another form opens and the user selects a job, i want the job to be added to that same column (where '9' is) but what's happening is the username dissapears. Another form will come up and the user selects the PLC option

what i'm trying to accomplish is the ID and username gets inserted and then the job and plc options get updated into that same row...

hope that's a little more clear.. thank you
|||

Kenny,

There are at least a couple of ways to go on this. First, let me ask a couple more questions.

What is the code that inserts the new UID and UserName into the table?

How is that code executed?

|||that's where i'm not sure... right now i have this code...

this is in my main form where the UID and username gets sent to the table...

strsuccess = " INSERT INTO sanbox.timespent "
strsuccess &= " ( `username ` ) VALUES ( "
strsuccess &= " '" & GlobalV.strUsername & "');"
x.dbCommand = strsuccess
x.ExecuteNonQuery()

strTime = "Update(sandbox.timespent) "
strTime &= " SET UID = UID , Username = '" & GlobalV.strUsername & "';"
strTime &= " WHERE(uid = uid) "
x.dbCommand = strTime
x.ExecuteNonQuery()

not sure if i did that part right but what I want to do is add a new UID to the form and add the username right next to that UID as a new session kind of deal... once the user proceeds to the next form this code is executed:

strjob = "Update(sandbox.timespent) "
strjob &= " SET job_description = '" & lstJobList.SelectedItem & "'"
strjob &= " WHERE username = '" & GlobalV.strUsername & "';"
x.dbCommand = strjob
x.ExecuteNonQuery()
x = Nothing

i'm pretty sure i'm not doing that part right, i want the job item to be added right next to the username... but what's happening is, the job item is filling in everywhere it finds the username to be the variable value...

i'd like to have it added to the newest UID, which would only add once... that's why i mentioned possibly making it so " WHERE UID = " some kind of uid variable "

it's a little hard to explain but if you can figure out how to add the job item once to the newest column i'd really appreciate it. thanks!

|||

OK, I see a couple of problems.

A couple more questions.

Where is the UID created?

Is it a IDENTITY field value from the table?

And What version/Edition of SQL Server? ( 2000 / 2005 ) ( Express / Standard / Enterprise )

|||the UID is unique and is incrimented by 1, it's the primary key if that helps.|||

Kenny,

You didn't respond about the version/edition of SQL Server, so I will assume that you are using SQL 2005.

This will not execute in your application, but it should point you in the direction that you need to go to make it work as you desire.

First, you need to capture the UID into a newly created application variable (scope it appropriately).

To do so, change the query type to ExecuteQuery so you can get a resultset.

Change the query to your version of this:

INSERT INTO SandBox.TimeSpent ( UserName )
OUTPUT inserted.UID
VALUES ( GlobalV.strUserName )

The application will do something like:

GlobalV.UID = x.ExecuteQuery

At the execution of the insert query, you will have the UID for the newly inserted row -which contains the UserName.

Then your UPDATE statement should be somewhat like this:

UPDATE SandBox.Timespent
SET Job_Description = {Your listbox.SelectedItem for JobDescription}
WHERE UID = GlobalV.UID

By using the UID, you will UPDATE ONLY the row you desire. AS you noticed, when you UPDATE by UserName, you are updating all rows with that UserName.

HELP - SQLDMO Problem

Hi,
I am using SQLDMO in my application to create data tables, full-text
catalogs and jobs to populate the catalogs. My datatables are created
successfully but I get error on line where it says :
Dim oSQLServer as SQLDMO.SQLServer
oSQLServer = new SQLDMO.SQLServer
I have added reference to SQLDMO library (Name: Interop.SQLDMO) in my
project (VB.NET 2003, Framework 1.1)
The error message is:
System.RuntimeInteropServices.COMException (0x80040154)
COM Object with CLSID {classId} is either not valid or not registered.
Stack Trace points to line oSQLServer = new SQLDMO.SQLServer
How do I fix this problem? (i.e. how do I register this COM object)
I dont have this problem when I run my app from VS.NET Studio (so with
machine with installed VS.NET) but it occurs on a machine without
VS.NET) when I run the executable file inside bin directory (which
also contains Interop.SQLDMO.dll file)
Any help will be appreciated
_dino_
try going to this location via a command window
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
and type regsvr32 sqldmo.dll
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:u10ch11m71na02qksua5dr68uofcv3qi3o@.4ax.com...
> Hi,
> I am using SQLDMO in my application to create data tables, full-text
> catalogs and jobs to populate the catalogs. My datatables are created
> successfully but I get error on line where it says :
> Dim oSQLServer as SQLDMO.SQLServer
> oSQLServer = new SQLDMO.SQLServer
> I have added reference to SQLDMO library (Name: Interop.SQLDMO) in my
> project (VB.NET 2003, Framework 1.1)
> The error message is:
> System.RuntimeInteropServices.COMException (0x80040154)
> COM Object with CLSID {classId} is either not valid or not registered.
> Stack Trace points to line oSQLServer = new SQLDMO.SQLServer
> How do I fix this problem? (i.e. how do I register this COM object)
> I dont have this problem when I run my app from VS.NET Studio (so with
> machine with installed VS.NET) but it occurs on a machine without
> VS.NET) when I run the executable file inside bin directory (which
> also contains Interop.SQLDMO.dll file)
> Any help will be appreciated
> _dino_
|||Hillary,
thank you for your help. The sql client tools have been installed on
the machine and we tested it in the office at several machines and it
worked just fine; however, on client's side it would not work.
The only difference is that at the office, we provided server name
instead of IP address. Today, we tried the same at clients side and
it works just fine. Why would SQLDMO accept server name but not
server IP address? Any Ideas ?
I appreciate your help
_dino_
On Wed, 31 Aug 2005 20:56:08 -0400, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:

>try going to this location via a command window
>C:\Program Files\Microsoft SQL Server\80\Tools\Binn
>and type regsvr32 sqldmo.dll
|||Try putting the sever name in using Client Network Utility and enter the IP
address there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:oqfeh1tjdu4fi03trd0eka0t7bi4qk1g5v@.4ax.com...
> Hillary,
> thank you for your help. The sql client tools have been installed on
> the machine and we tested it in the office at several machines and it
> worked just fine; however, on client's side it would not work.
> The only difference is that at the office, we provided server name
> instead of IP address. Today, we tried the same at clients side and
> it works just fine. Why would SQLDMO accept server name but not
> server IP address? Any Ideas ?
> I appreciate your help
> _dino_
> On Wed, 31 Aug 2005 20:56:08 -0400, "Hilary Cotter"
> <hilary.cotter@.gmail.com> wrote:
>
|||Thanks, I'll try it next time on client's site
I appreciate your help
_dino_
On Thu, 1 Sep 2005 14:13:13 -0400, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:

>Try putting the sever name in using Client Network Utility and enter the IP
>address there.

Help - DTS Custom Task to handle Job Failuer

I need to find a better way to handle DTS Job Failure issue. Currently, we have about 50 jobs which executed through DTS packages. Everytime when sources were not there or came in late, the DTS sent out an email to my page which I carried every day. Some those came in during the holidays even though I know the source party wont generate the source files at that day.

Trying to avoid to get beep everytime when job was failed. Someone suggested that it is possible to add kind of executable file within the Custom task and let it trigger the DTS packages. If, for example, a holiday then dont run the package so I wont get the page.

Any hints suggestions would be greatly appreciated

J827I get unhappy when I see 0 replies, but I have not done this before but this is how I would handle it.

I would write a dll in VB. It would check todays date against a list of holidays I might keep in a database table. If it did'nt return a result I would would use the VB syntax for firing off stored procedures (still in my dll).

Of course before you do this you need to already have your 50 DTS packages in place.

I would then build a new DTS package whose only step was to fire off this custom DLL. Register your cutom task and assign it icon and then build your DTS package using the DTS Designer (not the wizard).

Good luck.|||create a table or array with the dates of all non action days (holidays weekends down days).
create a master package that runs all of your sub packages(use the execute package task).
create a Execute SQL (or activex)Task to evaluate the NonActiveDays table against the execution of the package if getdate() = @.nonactiveday, return 0
else 1
then pass that value as a global variable to the task
when the 1 hits, run the package
this is a fairly simple solution.

and just for giggles if your code returns a 0, you can have the task email you a reminder that the task didnt run becuase of a holiday.

this opens you up to many possibllilties, for instance you could have this code run in a job the day before to remind you that the next day is a holiday and dts wont run...|||thanks thanks!|||Are you executing this from the web like in ASP.Net or something?

If you are, the problem is that the DTS package needs to run in the same security context that you created it in. The IUSR_etc... account in IIS does not have the ability to execute the package. The workarounds are sloppy and I have discussed them on this forum before. Do a search for ASP and DTS.|||I created a C# Console Application to trigger the DTS package directly and it worked (see code below). But it was an EXE (not DLL) file type.

I would like to plug this app as part of control in an existing DTS package by adding it into a Custom Task. I just noticed the DTS Custom Task only allows adding DLL format file to be registered. Then I recreated the app with Class library Type and tried to register / run Regsvr32 appName. I got Could not find entry point for DLLRegisterServer in the DLL / appName.DLL was load but the DLLRegisterServer entry point was not found, the file cant be registered.

Any helps would be highly appreciated!

---------------------
try
{
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;

string ServerName = "DEV";
string ServerUserName = null;
string ServerPassword = null;
string PackagePassword = null;
string PackageGUID = null;
string PackageVersionGUID = null;
string PackagName = "DTSTestPackage";


package.LoadFromSQLServer(
ServerName,
ServerUserName,
ServerPassword,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
PackagePassword,
PackageGUID,
PackageVersionGUID,
PackagName,
ref pVarPersistStgOfHost);

package.Execute();
package.UnInitialize();

// force Release() on COM object
System.Runtime.InteropServices.Marshal.ReleaseComO bject(package);

package = null;
}
catch(System.Runtime.InteropServices.COMException e)
{
Console.WriteLine("COMException {0}", e.ErrorCode.ToString() );
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}
catch(System.Exception e)
{
Console.WriteLine("Exception");
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}