I have started to create a package in DTS. I have created 2 connections for source DB and destination DB. I want to schedule a task to copy data from source to destination
I'm not sure which task to choose from. I tried 'Data Driven Query Task'. Firstly, I entered the source, using the source DB connection. I then chose the table I want to copy.
Next I clicked on the Bindings tab and chose the destination connection and created an identical table to the source table, ready for transferring data.
I just want to transfer the data and schedule it but when I ran the package it said:
'ActiveX scripting requires Script Text and Langauge and at least one Phase function to be specified.'
I re-opened the 'Data Driven Query Task'
I clicked on the Queries tab and the Query type was set to the default 'Insert'. The next step is to build the Insert query, I thought. I clicked on the Build button and got a list of tables in the destination connection. I dragged the destination table over (the one I just created), selected all the fields and now I am lost. As I clicked on the field list, the Insert statement was generated as:
INSERT INTO tblMasterCrownOffice
(CrownOfficeUniqueID, ChangeHistory, CurrentChange, RelocationsContact)
VALUES ()
But I cannot fill in the VALUES () part as I can only choose from a lits of destination tables.
Does anyone know how to setup the package to copy data from one source connection table to a destination connection table?
Thanks.I found out that I could click on the Transformations tab and choose 'Copy Columns' transformation.
So, when I run it now I get the error
'The data driven query task requires at least one query (and associated columns) to be specified'
I don't want to set any queries. I just want the transformation to run when i execute the package.
Any ideas?|||Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>|||Originally posted by nigelrivett
Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>
Thanks,
I was using 'Data driven Query Task', when I only need 'Transform Data' task. I have to wait until next week before I can link the client's oracle table to sql-server, so I've simulated the task by scheduling a table to be copied and transformed a bit from one SQL-Server Database to another. I executed it no problem, and found the Job list under SQL Agent.
i think I'll straight copy the Oracle tables over to SQL-Server daily, then schedule an SP to run on successful completion, that will transform the data properly. my only worry is if a DTS fails then it will screw up the data. I think I'll copy the data into a temp holding table, check the no of rows are OK, and only then delete the current data with the bext day's data.
Showing posts with label connections. Show all posts
Showing posts with label connections. Show all posts
Wednesday, March 21, 2012
DTS connection
Hi
I'm using DTS to transform Data between 2 SQL server .My package uses Activex transformation with lookups.first I used 2 connections one for the Source and another for the destination and I used the destination connection to fetch the data for thr lookup but while executing I faced an error saying Connection is busy with results from another command. I creaed another connection for the lookups and it worked. I started the profiler while the package was running and I noticed the the lookup connection is opend and closed once per each lookup (in my case I use up to for lookups per row) which leaks my performance down .so i have these questions which I hope any one thankfully answer:
1- why can't I use the Destination connection for the lookup?
2- why the connection is open and closed each time it looks-up?
I'm using DTS to transform Data between 2 SQL server .My package uses Activex transformation with lookups.first I used 2 connections one for the Source and another for the destination and I used the destination connection to fetch the data for thr lookup but while executing I faced an error saying Connection is busy with results from another command. I creaed another connection for the lookups and it worked. I started the profiler while the package was running and I noticed the the lookup connection is opend and closed once per each lookup (in my case I use up to for lookups per row) which leaks my performance down .so i have these questions which I hope any one thankfully answer:
1- why can't I use the Destination connection for the lookup?
2- why the connection is open and closed each time it looks-up?
Thanks in advanceHi Eisa,
This is a forum for the next version of DTS, called Integration Services, so you probably won't get enough qualified eye balls looking at your question to answer it.
Try the microsoft.public.sqlserver.dts newsgroup instead, that should have more activity on it.
thanks,
ashsql
Wednesday, March 7, 2012
DTS - Strange Behavior
Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.
Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.
Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
DTS - Strange Behavior
Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server
does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server
does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
DTS - Strange Behavior
Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
Tuesday, February 14, 2012
DSN's do not display in ODBC Data Source Administrator
I'm currently having to create a raft of DB2 connections in a
reasonable hurry. In addition, this list has to be propogated to three
other workstations (ultimate goal is to have DBArtisan access the
servers, instances and databases in an identical manner without having
to spend days doing this manually.)
I've figured out that the registry keys
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
and
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
allow me to specify the System DSN's so that they can be picked up by
the ODBC Data Source Administration application.
Unfortuantely, I think that there's a hidden limit in how much data
the tool can handle. Somewhere around 155 of the 219 DSN's, one row
gets chopped.
Instead of a line that reads
server.instance.database IBM DB2 ODBC DRIVER
I get variants such as the latest:
server.insta <rest of line is blank>
Looks like a classic buffer reaches max-size, burp and do nothing but
continue running.
BTW, this is only the tip of the iceberg, not only are there going to
be 200+ DB2 databases, but we're also looking at Sybase, MS SQLServer,
Informix, and Oracle, plus a few other antique clangers and
boat-anchors.
A follow-up question, would shortening the name of the ODBC server have any
impact? (eg, just DB2)
Thanks in advance...
BruceHi Bruce,
Just wanted to ask if you have already installed the ODBC drivers for
all the other databases i.e. Sybase, SQL Server, Informix and Oracle?
Regards,
Ashok Rautela
"Byrocat" <strikemaster2000@.yahoo.ca> wrote in message news:<CM50c.68204$ee3.635958@.news20.
bellglobal.com>...
> I'm currently having to create a raft of DB2 connections in a
> reasonable hurry. In addition, this list has to be propogated to three
> other workstations (ultimate goal is to have DBArtisan access the
> servers, instances and databases in an identical manner without having
> to spend days doing this manually.)
> I've figured out that the registry keys
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
> and
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
> allow me to specify the System DSN's so that they can be picked up by
> the ODBC Data Source Administration application.
> Unfortuantely, I think that there's a hidden limit in how much data
> the tool can handle. Somewhere around 155 of the 219 DSN's, one row
> gets chopped.
> Instead of a line that reads
> server.instance.database IBM DB2 ODBC DRIVER
> I get variants such as the latest:
> server.insta <rest of line is blank>
> Looks like a classic buffer reaches max-size, burp and do nothing but
> continue running.
> BTW, this is only the tip of the iceberg, not only are there going to
> be 200+ DB2 databases, but we're also looking at Sybase, MS SQLServer,
> Informix, and Oracle, plus a few other antique clangers and
> boat-anchors.
> A follow-up question, would shortening the name of the ODBC server have an
y
> impact? (eg, just DB2)
> Thanks in advance...
> Bruce|||arautela@.openlinksw.co.uk (Ashok Rautela) wrote in message news:<f5f56ae7.0403020312.44da8d
0a@.posting.google.com>...
> Hi Bruce,
> Just wanted to ask if you have already installed the ODBC drivers for
> all the other databases i.e. Sybase, SQL Server, Informix and Oracle?
> Regards,
> Ashok Rautela
>
I definitely have DB2, Sybase and SQLServer ODBC drivers installed.
I don't think that that is the problem. I've played with the list
entries (having created and recreated the list via the Registry), and
can get the cut-off point to change locations.
Defintiely points to the buffer being hard-coded.
Did have an interesting suggestion... Could the MS ODBC Administrator
make use of a registry entry to determine the maximum size of its
buffer and can this be changed?|||I remember a posting from someone at Microsoft that the
limitation is the combined length of the DSNs cannot exceed
4096 characters. So if you have DSN names that are all 409
characters long, you can only have 10 of those that will
display in the ODBC Data Source Administrator. If you create
more DSNs after this, they won't show up in the
Administrator applet but will be written to the registry and
will be available for use by applications.
-Sue
On 2 Mar 2004 09:07:38 -0800, bdealhoy@.sympatico.ca
(Byrocat) wrote:
>I definitely have DB2, Sybase and SQLServer ODBC drivers installed.
>I don't think that that is the problem. I've played with the list
>entries (having created and recreated the list via the Registry), and
>can get the cut-off point to change locations.
>Defintiely points to the buffer being hard-coded.
>Did have an interesting suggestion... Could the MS ODBC Administrator
>make use of a registry entry to determine the maximum size of its
>buffer and can this be changed?
reasonable hurry. In addition, this list has to be propogated to three
other workstations (ultimate goal is to have DBArtisan access the
servers, instances and databases in an identical manner without having
to spend days doing this manually.)
I've figured out that the registry keys
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
and
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
allow me to specify the System DSN's so that they can be picked up by
the ODBC Data Source Administration application.
Unfortuantely, I think that there's a hidden limit in how much data
the tool can handle. Somewhere around 155 of the 219 DSN's, one row
gets chopped.
Instead of a line that reads
server.instance.database IBM DB2 ODBC DRIVER
I get variants such as the latest:
server.insta <rest of line is blank>
Looks like a classic buffer reaches max-size, burp and do nothing but
continue running.
BTW, this is only the tip of the iceberg, not only are there going to
be 200+ DB2 databases, but we're also looking at Sybase, MS SQLServer,
Informix, and Oracle, plus a few other antique clangers and
boat-anchors.
A follow-up question, would shortening the name of the ODBC server have any
impact? (eg, just DB2)
Thanks in advance...
BruceHi Bruce,
Just wanted to ask if you have already installed the ODBC drivers for
all the other databases i.e. Sybase, SQL Server, Informix and Oracle?
Regards,
Ashok Rautela
"Byrocat" <strikemaster2000@.yahoo.ca> wrote in message news:<CM50c.68204$ee3.635958@.news20.
bellglobal.com>...
> I'm currently having to create a raft of DB2 connections in a
> reasonable hurry. In addition, this list has to be propogated to three
> other workstations (ultimate goal is to have DBArtisan access the
> servers, instances and databases in an identical manner without having
> to spend days doing this manually.)
> I've figured out that the registry keys
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
> and
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
> allow me to specify the System DSN's so that they can be picked up by
> the ODBC Data Source Administration application.
> Unfortuantely, I think that there's a hidden limit in how much data
> the tool can handle. Somewhere around 155 of the 219 DSN's, one row
> gets chopped.
> Instead of a line that reads
> server.instance.database IBM DB2 ODBC DRIVER
> I get variants such as the latest:
> server.insta <rest of line is blank>
> Looks like a classic buffer reaches max-size, burp and do nothing but
> continue running.
> BTW, this is only the tip of the iceberg, not only are there going to
> be 200+ DB2 databases, but we're also looking at Sybase, MS SQLServer,
> Informix, and Oracle, plus a few other antique clangers and
> boat-anchors.
> A follow-up question, would shortening the name of the ODBC server have an
y
> impact? (eg, just DB2)
> Thanks in advance...
> Bruce|||arautela@.openlinksw.co.uk (Ashok Rautela) wrote in message news:<f5f56ae7.0403020312.44da8d
0a@.posting.google.com>...
> Hi Bruce,
> Just wanted to ask if you have already installed the ODBC drivers for
> all the other databases i.e. Sybase, SQL Server, Informix and Oracle?
> Regards,
> Ashok Rautela
>
I definitely have DB2, Sybase and SQLServer ODBC drivers installed.
I don't think that that is the problem. I've played with the list
entries (having created and recreated the list via the Registry), and
can get the cut-off point to change locations.
Defintiely points to the buffer being hard-coded.
Did have an interesting suggestion... Could the MS ODBC Administrator
make use of a registry entry to determine the maximum size of its
buffer and can this be changed?|||I remember a posting from someone at Microsoft that the
limitation is the combined length of the DSNs cannot exceed
4096 characters. So if you have DSN names that are all 409
characters long, you can only have 10 of those that will
display in the ODBC Data Source Administrator. If you create
more DSNs after this, they won't show up in the
Administrator applet but will be written to the registry and
will be available for use by applications.
-Sue
On 2 Mar 2004 09:07:38 -0800, bdealhoy@.sympatico.ca
(Byrocat) wrote:
>I definitely have DB2, Sybase and SQLServer ODBC drivers installed.
>I don't think that that is the problem. I've played with the list
>entries (having created and recreated the list via the Registry), and
>can get the cut-off point to change locations.
>Defintiely points to the buffer being hard-coded.
>Did have an interesting suggestion... Could the MS ODBC Administrator
>make use of a registry entry to determine the maximum size of its
>buffer and can this be changed?
Subscribe to:
Posts (Atom)