Max Query: figure

Showing posts with label figure. Show all posts

Friday, March 30, 2012

help with sql query

I am trying to fill a dataset with customers in Table1, but i am searching by ItemID(probably from Table3). I can't figure out the sql query for this request.
Maybe something like:"select * from Table1 ...and now I think I should some how inner join Table1 and Table3 so i can select according to ItemID.

I have the three following tables:

Table1--It's primary key is the CustID identity field:

CustID CustName CustEmail ...

Table2--It's primary key is the ItemID identity field:

ItemID ItemName

Table3--ItemID and CustID function together for the primary key of the table:

ItemID CustID
1 1
2 1
2 2
3 2
1 3
3 3

ThanksTry this


select customers.* from table1, table2,table3 where
table1.custid=table3.custid and table3.itemid=table2.itemid

HTH|||Thanks!

Wednesday, March 28, 2012

Help with sp_lock output

I am trying to figure out something in the dump of my sp_lock output
(sql server 2000)
spid Db Objectid IndId Type
Resource Mode Status
373 QADB 0 0 PAG
1:204097 S WAIT
What does an object id = 0 mean? i thought this always show the id of
an actual table
Hi Derek,
The output you provided means that a shared lock is held on a page, not on a
table, hence the 0 value for objectid. Resource column states that the lock
is held on file id 1 page number 204097.
To easily work with locks and/or deadlocks, I suggest you try out the tool
called SQL Deadlock Detector. It monitors your database for locks and
deadlocks and
provides complete information on captured events. It tells you everything
you need to know (locked objects, blocked statements, blocking statements,
etc.) to solve your blocking/deadlock problems. The great thing about this
tool is it's event diagram which makes it exremely easy to see what exactly
is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
I've been using it for quite a while now (I purchased it) and find it very
handy and useful.
HTH.
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1187376178.875402.209170@.d55g2000hsg.googlegr oups.com...
>I am trying to figure out something in the dump of my sp_lock output
> (sql server 2000)
> spid Db Objectid IndId Type
> Resource Mode Status
> 373 QADB 0 0 PAG
> 1:204097 S WAIT
> What does an object id = 0 mean? i thought this always show the id of
> an actual table
>
sql

Monday, March 26, 2012

Help with sp_lock output

I am trying to figure out something in the dump of my sp_lock output
(sql server 2000)
spid Db Objectid IndId Type
Resource Mode Status
373 QADB 0 0 PAG
1:204097 S WAIT
What does an object id = 0 mean? i thought this always show the id of
an actual tableHi Derek,
The output you provided means that a shared lock is held on a page, not on a
table, hence the 0 value for objectid. Resource column states that the lock
is held on file id 1 page number 204097.
To easily work with locks and/or deadlocks, I suggest you try out the tool
called SQL Deadlock Detector. It monitors your database for locks and
deadlocks and
provides complete information on captured events. It tells you everything
you need to know (locked objects, blocked statements, blocking statements,
etc.) to solve your blocking/deadlock problems. The great thing about this
tool is it's event diagram which makes it exremely easy to see what exactly
is going on.
You can download it from here:
http://lakesidesql.com/downloads/DL...br />
007.zip.
I've been using it for quite a while now (I purchased it) and find it very
handy and useful.
HTH.
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1187376178.875402.209170@.d55g2000hsg.googlegroups.com...
>I am trying to figure out something in the dump of my sp_lock output
> (sql server 2000)
> spid Db Objectid IndId Type
> Resource Mode Status
> 373 QADB 0 0 PAG
> 1:204097 S WAIT
> What does an object id = 0 mean? i thought this always show the id of
> an actual table
>

Help with sp_lock output

I am trying to figure out something in the dump of my sp_lock output
(sql server 2000)
spid Db Objectid IndId Type
Resource Mode Status
373 QADB 0 0 PAG
1:204097 S WAIT
What does an object id = 0 mean? i thought this always show the id of
an actual tableHi Derek,
The output you provided means that a shared lock is held on a page, not on a
table, hence the 0 value for objectid. Resource column states that the lock
is held on file id 1 page number 204097.
To easily work with locks and/or deadlocks, I suggest you try out the tool
called SQL Deadlock Detector. It monitors your database for locks and
deadlocks and
provides complete information on captured events. It tells you everything
you need to know (locked objects, blocked statements, blocking statements,
etc.) to solve your blocking/deadlock problems. The great thing about this
tool is it's event diagram which makes it exremely easy to see what exactly
is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
I've been using it for quite a while now (I purchased it) and find it very
handy and useful.
HTH.
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1187376178.875402.209170@.d55g2000hsg.googlegroups.com...
>I am trying to figure out something in the dump of my sp_lock output
> (sql server 2000)
> spid Db Objectid IndId Type
> Resource Mode Status
> 373 QADB 0 0 PAG
> 1:204097 S WAIT
> What does an object id = 0 mean? i thought this always show the id of
> an actual table
>

Help with SOAP Lite client error problems - CGI code

Hi,

I have been working on a soap client project for over a week now and can not figure out what I am doing wrong. I am not an expert using Perl or SOAP so any help would be "greatly" appreciated.

I get basic errors throughout, starting with:
Error - SOAP::Transport::HTTP::Client::send_receive: POST
It seems as if the user access information is not carried through as it should, but I truly have no idea what the problem is.

Here is what I have put together so far.

Thanks,
Mark

sub procgetmemberinfo {

my $pin = "$form{'lPin'}";
my $password = "$form{'sPassword'}";

my $soap = SOAP::Lite
-> uri('https://xmlsql.XXXXX.xxx:441')
-> on_action( sub { join '/', 'https://xmlsql.XXXXX.xxx:441', $_[1] } )
-> proxy('https://xmlsql.XXXXX.xxx:441/service.asmx?WSDL');

my @.params = (
SOAP::Data->name(lPin => $pin),
SOAP::Data->name(sPassword => $password)
);

my $method = SOAP::Data->name('GetMemberInfo')->attr({xmlns => 'https://xmlsql.XXXXX.xxx:441/'});

my $result = $soap->call($method => @.params);
unless ($result->fault) {

my $title = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/TITLE');
my $firstname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/FIRST');
my $middlename = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/MIDDLE');
my $lastname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/LAST');
my $suffix = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/SUFFIX');
my $address1 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STREET_ADDRESS');
my $address2 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ADDRESS2');
my $city = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/CITY');
my $stateprovince = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STATE_PROV');
my $postalcode = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ZIP');
my $country = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/COUNTRY');
my $homephone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/HOME_PHONE');
my $workphone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/WORK_PHONE');
my $emailaddress = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/EMAIL');

print "PIN Number: $pin ";
print "Password: $password ";
print "Title: $title ";
print "First Name: $firstname ";
print "Middle Name: $middlename ";
print "Last Name: $lastname ";
print "Suffix: $suffix ";
print "Address1: $address1 ";
print "Address2: $address2 ";
print "City: $city ";
print "State: $stateprovince ";
print "ZIP/Postal Code: $postalcode ";
print "Country: $country ";
print "Home Phone: $homephone ";
print "Work Phone: $workphone ";
print "Email Address: $emailaddress";

}

else {
print join ', ',
$result->faultcode,
$result->faultstring,
$result->faultdetail;
}

}

Here is a nice easy way to debug SSL issue with Windows 2003 that I discovered while working on SOAP projects.

1. Create batch file c:\ssltrace.cmd with following contents:

logman start http_ssl_trace -pf c:\guids.txt -o out.etl -ets
pause
logman stop http_ssl_trace -ets
tracerpt /y out.etl
notepad dumpfile.csv

2. Create file c:\guids.txt with following contents:

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

3. On the web service machine run c:\ssltrace.cmd to start tracing, then hit your web service with your POST. Once you are finished testing press the spacebar to in the ssltrace.cmd command window to stop tracing and display the trace file. You should see everything coming in and going out along with error codes, etc...

|||

Hello Matt,

your Message was helpfull to solve a problem with tracing.

Could you please tell me where you get the information

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

from?

Thanks

str01014

|||

Actually I updated my batch file to inline everything (i've included it below).

To get a list of trace providers, run this ->

C:\>logman query providers

Provider GUID
-
...

IIS: SSL Filter {1fbecc45-c060-4e7c-8a0e-0dbd6116181b}
...
HTTP Service Trace {dd5ef90a-6398-47a4-ad34-4dcecdef795f}.
...

This will list each trace provider and it's GUID. The 2nd and 3rd values are flags passed to the trace provider to turn on and off various traces. How these values are used are provider specific. However if you just want to turn on all the flags pass in 0xFFFFFFFF and 255 like in my example below. Search MSDN for HTTP Service Trace and SSL Filter for information on what these flags do.

Here is my updated batch file (note it requires logparser tool which is downloadable from Microsoft) ->

rem START SSL TRACE DEMO
if exist ssltrace.guids del ssltrace.guids
echo {1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0xFFFFFFFF 255 IIS: SSL Filter >> ssltrace.guids
echo {dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0xFFFFFFFF 255 HTTP Service Trace >> ssltrace.guids
logman start ssltrace -pf ssltrace.guids -o out.etl -ets
rem PRESS <SPACEBAR> TO STOP SSL+HTTP TRACING AND VIEW RESULTS
pause
logman stop ssltrace -ets
if exist ssltrace.guids del ssltrace.guids
if exist ssltrace.log del ssltrace.log
logparser -i:ETW "select * from out.etl" -e:ON >> ssltrace.log
start notepad ssltrace.log
rem END SSL TRACE DEMO

Help with SOAP Lite client error problems - CGI code

Hi,

I have been working on a soap client project for over a week now and can not figure out what I am doing wrong. I am not an expert using Perl or SOAP so any help would be "greatly" appreciated.

Here is what I have put together so far.

Thanks,
Mark

sub procgetmemberinfo {

my $pin = "$form{'lPin'}";
my $password = "$form{'sPassword'}";

my $soap = SOAP::Lite
-> uri('https://xmlsql.XXXXX.xxx:441')
-> on_action( sub { join '/', 'https://xmlsql.XXXXX.xxx:441', $_[1] } )
-> proxy('https://xmlsql.XXXXX.xxx:441/service.asmx?WSDL');

my @.params = (
SOAP::Data->name(lPin => $pin),
SOAP::Data->name(sPassword => $password)
);

my $method = SOAP::Data->name('GetMemberInfo')->attr({xmlns => 'https://xmlsql.XXXXX.xxx:441/'});

my $result = $soap->call($method => @.params);
unless ($result->fault) {

}

else {
print join ', ',
$result->faultcode,
$result->faultstring,
$result->faultdetail;
}

}

Here is a nice easy way to debug SSL issue with Windows 2003 that I discovered while working on SOAP projects.

1. Create batch file c:\ssltrace.cmd with following contents:

logman start http_ssl_trace -pf c:\guids.txt -o out.etl -ets
pause
logman stop http_ssl_trace -ets
tracerpt /y out.etl
notepad dumpfile.csv

2. Create file c:\guids.txt with following contents:

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

|||

Hello Matt,

your Message was helpfull to solve a problem with tracing.

Could you please tell me where you get the information

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

from?

Thanks

str01014

|||

Actually I updated my batch file to inline everything (i've included it below).

To get a list of trace providers, run this ->

C:\>logman query providers

Provider GUID
-
...

IIS: SSL Filter {1fbecc45-c060-4e7c-8a0e-0dbd6116181b}
...
HTTP Service Trace {dd5ef90a-6398-47a4-ad34-4dcecdef795f}.
...

Here is my updated batch file (note it requires logparser tool which is downloadable from Microsoft) ->

sql

Wednesday, March 7, 2012

help with one more SQL query...

i am trying to figure out how to write one more sql query. basically i now have 2 tables, both filled with item numbers and quantities. i want to write queries that will produce what is missing between the two tables. here is what i need more specifically:

a query that looks for item numbers that are in one table and not the other, and vice versa (ie item number 20004 is in our table, but doesn't exist in the other table).

a query that prints out discrepancies between quantities for item numbers that do match up (ie item number 20004 is in both tables, but has a quantity of 10 in one and 20 in the other).

it seems that i should be using the SQL join functions for these? is that right, or is there a better way to do this??yes, for the first task (rows in one table but not the other) you will need two queries, each featuring a LEFT OUTER JOIN, with a test for IS NULL in the WHERE clauseselect table1.itemnumber
from table1
left outer
join table2
on table1.itemnumber
= table2.itemnumber
where table2.itemnumber is null

select table2.itemnumber
from table2
left outer
join table1
on table2.itemnumber
= table1.itemnumber
where table1.itemnumber is null

for the second task you will need a simple INNER JOINselect table1.itemnumber
, table1.quantity
, table2.quantity
from table1
inner
join table2
on table1.itemnumber
= table2.itemnumber
where table1.quantity
<> table2.quantity|||Essentially the queryselect table1.itemnumber
from table1 left outer join table2 on table1.itemnumber = table2.itemnumber
where table2.itemnumber is nullis a "set difference", hence can also be achieved by using an "EXCEPT" construction:select itemnumber from table1
EXCEPT ALL
select itemnumber from table2The main difference in the result being that in the former case, duplicates in table1 will be shown (as duplicates) only if they aren't present in table2, while in the latter case duplicates may be shown (but with a smaller repeat count) when they are present in the second table.
Without duplicates in table1, both queries give the same result.
In most situations, the EXCEPT query will be faster, though, especially if the second table is large.|||In most situations, the EXCEPT query will be faster...unless your database system doesn't support that syntax, in which case it will take positively forever ;) :)|||unless your database system doesn't support that syntaxin which case it will probably neither support the OUTER JOIN syntax ...
;)|||um, peter, they all support OUTER JOIN syntax ;)

which database(s) were you thinking of that do support EXCEPT?|||Just thinking of Oracle (up to version 9) which has no FULL OUTER JOIN, but has MINUS.
Maybe there are others as well, no idea.|||but you don't need FULL OUTER JOIN to create an "EXCEPT" query, just LEFT OUTER JOIN|||And which DB systems were you thinking of that do not support EXCEPT?|||many more than you were!! ;) :)|||That would (not) surprise me!
;)|||I call a remote/stored procedure on another server.

It then calls other stored procedure with the EXEC statement.

It seems to me that logically this would get done remotely as well no? But It seems not to be from my tests.

Is there a easy way to specify that the SP - and all it's sub calls get done remotely, or do I need to specifically specify for each of these that they should be run remotely in each and every EXEC statement?

Monday, February 27, 2012

Help with modifying a data source''s query at runtime

Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call.

The error is

Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B

I pieced together this code from the examples in the online books, but I am not sure what to do.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Public Class ScriptMain

Public Sub Main()

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

app.LoadPackage("c:\systime\ExcelOut\ExcelOut\ExcelOutDo.dtsx", Nothing)

Dim pkgVars As Variables = package.Variables

Dim gsVar As Variable = pkgVars("User::gsExcelFile")

Dim currVars As Variables = Dts.Variables

Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)

gsVar.Value = Dts.Variables("User::gsExcelFile").Value

pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value

pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value

Dim e As Executable = package.Executables("ExcelOutTask")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Get the source component.

Dim SourceComponent As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Local Source")

Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()

srcDesignTime.ProvideComponentProperties()

' Reinitialize the metadata.

srcDesignTime.AcquireConnections(vbNull)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Get the destination component.

Dim destination As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Excel Destination")

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()

destDesignTime.ProvideComponentProperties()

' Create the path.

dataFlowTask.PathCollection.RemoveAll()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _

destination.InputCollection(0))

'Console.WriteLine(dataFlowTask.PathCollection.Count)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I think (but I'm not positive) that you need to call ReinitializeMetaData on the destination component after updating the path, so it has a chance to correct any changed columns. Actually, if you call that, you might not have to remove and add the path at all.

|||

Before running the AcquireConnections you need to re-link the connection with the source component. It has the connection ID persisted and you need to get the connection object (use the ID to find it) and assign it to the component's runtime connection.

HTH,

Bob

Help with modifying a data source''s query at runtime

The error is

Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B

I pieced together this code from the examples in the online books, but I am not sure what to do.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Public Class ScriptMain

Public Sub Main()

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

app.LoadPackage("c:\systime\ExcelOut\ExcelOut\ExcelOutDo.dtsx", Nothing)

Dim pkgVars As Variables = package.Variables

Dim gsVar As Variable = pkgVars("User::gsExcelFile")

Dim currVars As Variables = Dts.Variables

Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)

gsVar.Value = Dts.Variables("User::gsExcelFile").Value

pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value

pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value

Dim e As Executable = package.Executables("ExcelOutTask")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Get the source component.

Dim SourceComponent As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Local Source")

Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()

srcDesignTime.ProvideComponentProperties()

' Reinitialize the metadata.

srcDesignTime.AcquireConnections(vbNull)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Get the destination component.

Dim destination As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Excel Destination")

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()

destDesignTime.ProvideComponentProperties()

' Create the path.

dataFlowTask.PathCollection.RemoveAll()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _

destination.InputCollection(0))

'Console.WriteLine(dataFlowTask.PathCollection.Count)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||

HTH,

Bob

Help with modifying a data source''s query at runtime

The error is

Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B

I pieced together this code from the examples in the online books, but I am not sure what to do.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Public Class ScriptMain

Public Sub Main()

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

app.LoadPackage("c:\systime\ExcelOut\ExcelOut\ExcelOutDo.dtsx", Nothing)

Dim pkgVars As Variables = package.Variables

Dim gsVar As Variable = pkgVars("User::gsExcelFile")

Dim currVars As Variables = Dts.Variables

Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)

gsVar.Value = Dts.Variables("User::gsExcelFile").Value

pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value

pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value

Dim e As Executable = package.Executables("ExcelOutTask")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Get the source component.

Dim SourceComponent As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Local Source")

Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()

srcDesignTime.ProvideComponentProperties()

' Reinitialize the metadata.

srcDesignTime.AcquireConnections(vbNull)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Get the destination component.

Dim destination As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Excel Destination")

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()

destDesignTime.ProvideComponentProperties()

' Create the path.

dataFlowTask.PathCollection.RemoveAll()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _

destination.InputCollection(0))

'Console.WriteLine(dataFlowTask.PathCollection.Count)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||

HTH,

Bob

Sunday, February 19, 2012

Help with jobs failing

Hello,
I posted this message on 6/28 and am still looking for some help. I
apologize for the repost, however we cannot figure out the issue. For some
reason a couple of our jobs have been failing lately. We get the following
msg on the details of the job. Any ideas? This happens to jobs randomly.
Sometimes the jobs will work fine and other times we get the failed msg.
Thanks in advance.
Msg:
Unable to connect to SQL Server 'COMPUTERNAME\DBSERVER' . The step
failed.
SQLAGENT.OUT File
2004-08-23 16:31:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-23 16:31:30 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-23 16:32:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-23 16:32:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-23 16:32:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-23 16:32:15 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-23 16:32:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-23 16:32:16 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-23 16:32:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-23 16:32:16 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 03:01:52 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 03:01:52 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 03:31:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 03:31:36 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 04:01:46 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 04:01:54 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 04:02:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 04:02:02 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 04:02:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 04:02:02 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 04:03:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 04:03:02 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:03:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:03:42 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:03:43 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:04:24 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:04:24 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:04:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:04:52 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:05:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:05:07 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:05:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:05:25 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:05:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:05:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:05:43 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:08:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:08:02 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:35:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:35:07 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:35:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:35:12 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:36:05 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:36:05 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 06:36:06 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 06:36:06 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 07:01:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 07:01:37 - ! [382] Logon to server 'computername\DBSERVER' failed
(JobManager)
2004-08-24 07:01:46 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 07:01:46 - ! [382] Logon to server 'computername\DBSERVER' failed
(JobManager)
2004-08-24 07:01:57 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-08-24 07:01:57 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-08-24 07:01:57 - ! [382] Logon to server 'computername\DBSERVER' failed
(JobManager)
2004-08-24 07:03:31 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-08-24 07:03:31 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-08-24 07:03:31 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 07:05:17 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-24 07:05:17 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-24 07:05:17 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 03:01:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 03:31:47 - ! [298] SQLServer Error: 17, SQL Server does not exist
or access denied. [SQLSTATE 08001]
2004-08-25 03:31:47 - ! [298] SQLServer Error: 53, ConnectionOpen
(Connect()). [SQLSTATE 01000]
2004-08-25 04:01:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 04:01:08 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-25 04:31:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 04:31:51 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-25 04:31:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 04:31:51 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-25 05:00:20 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 05:14:05 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 06:58:22 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 06:58:23 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)
2004-08-25 06:59:26 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 07:31:42 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
HYT00]
2004-08-25 07:31:42 - ! [382] Logon to server 'computername\DBSERVER' failed
(ConnAttemptCachableOp)What does the job do? Is it connection to a remote workstation that has
hard disks which hibernate? Are there jobs running on that workstation that
prevent a user to connect? Does that workstation do windows updates at
night and possibly self-reboots? More details are needed.
Jake wrote:
> Hello,
> I posted this message on 6/28 and am still looking for some help.
> I apologize for the repost, however we cannot figure out the issue.
> For some reason a couple of our jobs have been failing lately. We get
> the following msg on the details of the job. Any ideas? This happens
> to jobs randomly. Sometimes the jobs will work fine and other times
> we get the failed msg. Thanks in advance.
> Msg:
> Unable to connect to SQL Server 'COMPUTERNAME\DBSERVER' . The step
> failed.
> SQLAGENT.OUT File
> 2004-08-23 16:31:30 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-23 16:31:30 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-23 16:32:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-23 16:32:15 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-23 16:32:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-23 16:32:15 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-23 16:32:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-23 16:32:16 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-23 16:32:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-23 16:32:16 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 03:01:52 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 03:01:52 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 03:31:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 03:31:36 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 04:01:46 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 04:01:54 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 04:02:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 04:02:02 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 04:02:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 04:02:02 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 04:03:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 04:03:02 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:03:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:03:42 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:03:43 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:04:24 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:04:24 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:04:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:04:52 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:05:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:05:07 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:05:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:05:25 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:05:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:05:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:05:43 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:08:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:08:02 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:35:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:35:07 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:35:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:35:12 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:36:05 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:36:05 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 06:36:06 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 06:36:06 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 07:01:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 07:01:37 - ! [382] Logon to server 'computername\DBSERVER'
> failed (JobManager)
> 2004-08-24 07:01:46 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 07:01:46 - ! [382] Logon to server 'computername\DBSERVER'
> failed (JobManager)
> 2004-08-24 07:01:57 - ! [298] SQLServer Error: 17, SQL Server does
> not exist or access denied. [SQLSTATE 08001]
> 2004-08-24 07:01:57 - ! [298] SQLServer Error: 53, ConnectionOpen
> (Connect()). [SQLSTATE 01000]
> 2004-08-24 07:01:57 - ! [382] Logon to server 'computername\DBSERVER'
> failed (JobManager)
> 2004-08-24 07:03:31 - ! [298] SQLServer Error: 17, SQL Server does
> not exist or access denied. [SQLSTATE 08001]
> 2004-08-24 07:03:31 - ! [298] SQLServer Error: 53, ConnectionOpen
> (Connect()). [SQLSTATE 01000]
> 2004-08-24 07:03:31 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 07:05:17 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-24 07:05:17 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-24 07:05:17 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 03:01:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 03:31:47 - ! [298] SQLServer Error: 17, SQL Server does
> not exist or access denied. [SQLSTATE 08001]
> 2004-08-25 03:31:47 - ! [298] SQLServer Error: 53, ConnectionOpen
> (Connect()). [SQLSTATE 01000]
> 2004-08-25 04:01:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 04:01:08 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-25 04:31:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 04:31:51 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-25 04:31:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 04:31:51 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-25 05:00:20 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 05:14:05 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 06:58:22 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 06:58:23 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)
> 2004-08-25 06:59:26 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 07:31:42 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> HYT00]
> 2004-08-25 07:31:42 - ! [382] Logon to server 'computername\DBSERVER'
> failed (ConnAttemptCachableOp)|||Eric,
Thanks for the response.
"Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
news:OTFKf1riEHA.4044@.tk2msftngp13.phx.gbl...
> What does the job do?
The jobs do multiple things. There isn't a specific job that keeps failing,
it's random. Most of out jobs are either moving data from one database to
another.
Is it connection to a remote workstation that has
> hard disks which hibernate?
The sql jobs are executing against the databases that are on the same server
as the sql agent trying to run the job. It's all one box.
>Are there jobs running on that workstation that prevent a user to connect?
Users are still able to connect via query anlyzer, website, etc... it's just
the jobs that are failing.
Does that workstation do windows updates at night and possibly self-reboots?
No it has the download automatically but not to install automatically. This
is a win2k3 box. SQL2k, SP3a
More details are needed.
>
>
> Jake wrote:
> > Hello,
> >
> > I posted this message on 6/28 and am still looking for some help.
> > I apologize for the repost, however we cannot figure out the issue.
> > For some reason a couple of our jobs have been failing lately. We get
> > the following msg on the details of the job. Any ideas? This happens
> > to jobs randomly. Sometimes the jobs will work fine and other times
> > we get the failed msg. Thanks in advance.
> >
> > Msg:
> > Unable to connect to SQL Server 'COMPUTERNAME\DBSERVER' . The step
> > failed.
> >
> > SQLAGENT.OUT File
> > 2004-08-23 16:31:30 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-23 16:31:30 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-23 16:32:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-23 16:32:15 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-23 16:32:15 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-23 16:32:15 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-23 16:32:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-23 16:32:16 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-23 16:32:16 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-23 16:32:16 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 03:01:52 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 03:01:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 03:01:52 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 03:31:36 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 03:31:36 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 04:01:46 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 04:01:54 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 04:02:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 04:02:02 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 04:02:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 04:02:02 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 04:03:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 04:03:02 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 05:31:11 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 05:31:11 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:03:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:03:42 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:03:43 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:04:24 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:04:24 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:04:52 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:04:52 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:05:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:05:07 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:05:25 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:05:25 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:05:30 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:05:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:05:43 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:08:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:08:02 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:35:02 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:35:07 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:35:07 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:35:12 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:36:05 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:36:05 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 06:36:06 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 06:36:06 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 07:01:37 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 07:01:37 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (JobManager)
> > 2004-08-24 07:01:46 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 07:01:46 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (JobManager)
> > 2004-08-24 07:01:57 - ! [298] SQLServer Error: 17, SQL Server does
> > not exist or access denied. [SQLSTATE 08001]
> > 2004-08-24 07:01:57 - ! [298] SQLServer Error: 53, ConnectionOpen
> > (Connect()). [SQLSTATE 01000]
> > 2004-08-24 07:01:57 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (JobManager)
> > 2004-08-24 07:03:31 - ! [298] SQLServer Error: 17, SQL Server does
> > not exist or access denied. [SQLSTATE 08001]
> > 2004-08-24 07:03:31 - ! [298] SQLServer Error: 53, ConnectionOpen
> > (Connect()). [SQLSTATE 01000]
> > 2004-08-24 07:03:31 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 07:05:17 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-24 07:05:17 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-24 07:05:17 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 03:01:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 03:31:47 - ! [298] SQLServer Error: 17, SQL Server does
> > not exist or access denied. [SQLSTATE 08001]
> > 2004-08-25 03:31:47 - ! [298] SQLServer Error: 53, ConnectionOpen
> > (Connect()). [SQLSTATE 01000]
> > 2004-08-25 04:01:08 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 04:01:08 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-25 04:31:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 04:31:51 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-25 04:31:51 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 04:31:51 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-25 05:00:20 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 05:14:05 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 06:58:22 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 06:58:23 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
> > 2004-08-25 06:59:26 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 07:31:42 - ! [165] ODBC Error: 0, Timeout expired [SQLSTATE
> > HYT00]
> > 2004-08-25 07:31:42 - ! [382] Logon to server 'computername\DBSERVER'
> > failed (ConnAttemptCachableOp)
>

Friday, March 30, 2012

help with sql query

Wednesday, March 28, 2012

Help with sp_lock output

Monday, March 26, 2012

Help with sp_lock output

Help with sp_lock output

Help with SOAP Lite client error problems - CGI code

Help with SOAP Lite client error problems - CGI code

Wednesday, March 7, 2012

help with one more SQL query...

Monday, February 27, 2012

Help with modifying a data source''s query at runtime

Help with modifying a data source''s query at runtime

Help with modifying a data source''s query at runtime

Sunday, February 19, 2012

Help with jobs failing

Help with jobs failing

Help with jobs failing

Max Query

Blog Archive

About Me