I was wondering if any of you could shed some light on the following
problem?
We have 4 Servers (WinNT SP6, SQL 7 SP3, IBM Client Access ODBC ver 6,
DR, Production, Development, Test) on our Datawarehousing Project.
They in turn connect to AS/4000 systems (Live, Dev, Test, 1st two
running version 4.5, last one upgraded to 5.1) for data refreshes,
using SP's and lots of DTS Packages.
Problem is that whenever we try to run DTS (using dtsrun.exe, works
fine in Designer Mode) against the upgraded AS/400 system on our Test
Server it gives out the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSActiveScriptTask_5 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_5 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error
string: up_processDTS Failed Error source: Microsoft OLE DB
Provider for SQL Server Help file: Help context: 0
Error Detail Records: Error: -2147217900 (80040E14); Provider
Error: 50000 (C350) Error string: up_processDTS Failed
Error source: Microsoft OLE DB Provider for SQL Server Help
file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 Error: -2147220440 (80040428);
Provider Error: 0 (0) Error string: Package failed because Step
'DTSStep_DTSExecuteSQLTask_1' failed. Error source: Microsoft
Data Transformation Services (DTS) Package Help file: sqldts.hlp
Help context: 700. Process Exit Code 1. The step failed.
However, when I run the same DTS Packages from our Live, Dev and DR
machines, it works fine!
I have checked and double-checked the revision details, all four boxes
are the same. I have run the error message against MS KB and IBM,
nothing. Before the AS/400 was upgraded the DTS Packages DID work, now
they only work from other NT boxes and not the Test Server which is
most bizarre. I've rebooted twice, nada.
Any help, suggestions, pointers etc. would be much appreciated.
Regards
ALIIt is only the one step that is failing
DTSStep_DTSExecuteSQLTask_1
Can you isolate this and run it. What does it do? If everything alse runs
fine and the ONLY difference is the version (5.1) then I would suggest that
as a possible cause
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"ALI" <ali.shah@.itnetplc.com> wrote in message
news:8487385c.0308070353.6f5ee16@.posting.google.com...
> I was wondering if any of you could shed some light on the following
> problem?
> We have 4 Servers (WinNT SP6, SQL 7 SP3, IBM Client Access ODBC ver 6,
> DR, Production, Development, Test) on our Datawarehousing Project.
> They in turn connect to AS/4000 systems (Live, Dev, Test, 1st two
> running version 4.5, last one upgraded to 5.1) for data refreshes,
> using SP's and lots of DTS Packages.
> Problem is that whenever we try to run DTS (using dtsrun.exe, works
> fine in Designer Mode) against the upgraded AS/400 system on our Test
> Server it gives out the following error:
> DTSRun: Loading... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSActiveScriptTask_5 DTSRun OnFinish:
> DTSStep_DTSActiveScriptTask_5 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
> DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14) Error
> string: up_processDTS Failed Error source: Microsoft OLE DB
> Provider for SQL Server Help file: Help context: 0
> Error Detail Records: Error: -2147217900 (80040E14); Provider
> Error: 50000 (C350) Error string: up_processDTS Failed
> Error source: Microsoft OLE DB Provider for SQL Server Help
> file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 Error: -2147220440 (80040428);
> Provider Error: 0 (0) Error string: Package failed because Step
> 'DTSStep_DTSExecuteSQLTask_1' failed. Error source: Microsoft
> Data Transformation Services (DTS) Package Help file: sqldts.hlp
> Help context: 700. Process Exit Code 1. The step failed.
> However, when I run the same DTS Packages from our Live, Dev and DR
> machines, it works fine!
> I have checked and double-checked the revision details, all four boxes
> are the same. I have run the error message against MS KB and IBM,
> nothing. Before the AS/400 was upgraded the DTS Packages DID work, now
> they only work from other NT boxes and not the Test Server which is
> most bizarre. I've rebooted twice, nada.
> Any help, suggestions, pointers etc. would be much appreciated.
> Regards
> ALI
Showing posts with label odbc. Show all posts
Showing posts with label odbc. Show all posts
Monday, March 26, 2012
Saturday, February 25, 2012
Pivot Tables in Excel
I Created pivot tables and pivot charts in excel getting data from an external source (SQL Server 2000) using an ODBC Connection. The problem is when i want to use the same spreadsheet acceess the server from outside through the internet using an ODBC connection.
The odbc which access the sql server remotely uses as server name the ip address of the server than sql server running to.
The pivot tables when it was created it used the odbc connection accessed from with in the LAN (Server name /SERVERGROUP/SQLSERVER).
I was just wondering if there is an easy way to modify the code that the pivot tables uses to make the connection to the SQL Server with out
recreating all those pivot tables and pivot charts.
When I try to open the file remotely it fails to make a connection of course.
Any help is appreciated!
ThanksIf you are using an Excel Macro to do your queries, you are in luck. All you need to do is open the VB Editor and edit the connection string.
Otherwise, you will most likely have to recreate your queries (but if you do so, create a Macro, so you can edit it moreeasily in the future). All you need to do it turn on the Macro Recorder, build your queries as you did before, even perform any formatting, then stop the recorder. Now, if you ever need to tweek the query or change the datasource, it's simply a matter of editing the Marco code.
-b|||well thanks i knew that I could do that but I have over 20 graphs and 10 reports so it would be nice if there is a way for a quick and dirty solution. Saying all that if there is not a way to access the code that excel creates on the background when you use those pivot table wizards so I could manually modify the connection string then I guess I need to redo it from scrach and use macros this time.
Thanks for your input
The odbc which access the sql server remotely uses as server name the ip address of the server than sql server running to.
The pivot tables when it was created it used the odbc connection accessed from with in the LAN (Server name /SERVERGROUP/SQLSERVER).
I was just wondering if there is an easy way to modify the code that the pivot tables uses to make the connection to the SQL Server with out
recreating all those pivot tables and pivot charts.
When I try to open the file remotely it fails to make a connection of course.
Any help is appreciated!
ThanksIf you are using an Excel Macro to do your queries, you are in luck. All you need to do is open the VB Editor and edit the connection string.
Otherwise, you will most likely have to recreate your queries (but if you do so, create a Macro, so you can edit it moreeasily in the future). All you need to do it turn on the Macro Recorder, build your queries as you did before, even perform any formatting, then stop the recorder. Now, if you ever need to tweek the query or change the datasource, it's simply a matter of editing the Marco code.
-b|||well thanks i knew that I could do that but I have over 20 graphs and 10 reports so it would be nice if there is a way for a quick and dirty solution. Saying all that if there is not a way to access the code that excel creates on the background when you use those pivot table wizards so I could manually modify the connection string then I guess I need to redo it from scrach and use macros this time.
Thanks for your input
Subscribe to:
Posts (Atom)