Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?
Thanks
Colin
Most probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")
On Mar 6, 5:52Xpm, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi
> I'm using SQL Server 2000. I've created a local package which runs
> fine when i execute it manually from Enterprise Manager, but
> constantly fails when I schedule it. Can anybody suggest why this
> might be?
> Thanks
> Colin
|||On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")
>
The history log says the following:
Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.
The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.
The package works fine if I
|||On 8 Mar, 09:44, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
>
> The history log says the following:
> Executed as user: SQLSRV01\SYSTEM. ...Start:
> DTSStep_DTSExecuteSQLTask_1 X DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 X DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 X DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 X DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) X X XError
> string: XThe driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 X X XError source: XMicrosoft OLE DB
> Provider for ODBC Drivers X X XHelp file: X X X XHelp context: X0
> Error Detail Records: X X XError: X-2147467259 (80004005); Provider
> Error: X444 (1BC) X X XError string: XThe driver returned invalid (or
> failed to return) SQL_DRIVER_ODBC_VER: 2.00 X X XError source:
> Microsoft OLE DB Provider for ODBC Drivers X X XHelp file: X X X XHelp
> context: X0 X X X X Error: X-2147467259 (80004005); Provider Error: X0
> (0) X X XError string: XCannot find all files in data path X XXError
> source: XMicrosoft OLE DB Provider for ODBC Drivers X X XHelp
> file: X X ... XProcess Exit Code 2. XThe step failed.
> The package has just three steps. Step 1 is an execute SQL task which
> deletes from two tables. Steps 2 & 3 connect to our accounts package
> (Sage) using ODBC and Xuses two Transform data tasks to copy data from
> Sage tables to the two SQL server tables. I am connecting to SQL sever
> using the Microsoft OLE DB provider. I know that Step 1 is working
> because after the package fails the two SQL tables are empty.
> The package works fine if I
.....right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!
Thanks for any help,
Colin
sql
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Wednesday, March 21, 2012
Monday, February 20, 2012
Overhead of Multiple SQL Calls
Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)
Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)
Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>
Subscribe to:
Posts (Atom)