Connect to DB2 in a stored procedure

Hi
I read data from a db2 database on an IBM mainframe through ODBC and
transfer it to a dabase on our SQL Server. Everything is done using a DTS and
it works fine. For serveral resons I would like to implement a stored
procedure doing the same task. Does anybody know how to solve this? Specially
the connection to the IBM mainframe is a bog question mark to me.
Any help appreciated.
Rene
Me again!
I found some information about linked server which might help. But how do I
set up a linked server to DB2? Can someone give me an example?
Regards
Rene
"René" wrote:

> Hi
> I read data from a db2 database on an IBM mainframe through ODBC and
> transfer it to a dabase on our SQL Server. Everything is done using a DTS and
> it works fine. For serveral resons I would like to implement a stored
> procedure doing the same task. Does anybody know how to solve this? Specially
> the connection to the IBM mainframe is a bog question mark to me.
> Any help appreciated.
> Rene
|||You can find an example in Books Online. From the index,
search on Linked Servers. Select the Access DB2 Database
subtopic.
-Sue
On Mon, 9 Aug 2004 04:33:05 -0700, "Ren"
<Ren@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Me again!
>I found some information about linked server which might help. But how do I
>set up a linked server to DB2? Can someone give me an example?
>Regards
>Rene
>"Ren" wrote:

Connect to DB2 in a stored procedure

Hi
I read data from a DB2 database on an IBM mainframe through ODBC and
transfer it to a dabase on our SQL Server. Everything is done using a DTS an
d
it works fine. For serveral resons I would like to implement a stored
procedure doing the same task. Does anybody know how to solve this? Speciall
y
the connection to the IBM mainframe is a bog question mark to me.
Any help appreciated.
ReneMe again!
I found some information about linked server which might help. But how do I
set up a linked server to DB2? Can someone give me an example?
Regards
Rene
"René" wrote:

> Hi
> I read data from a DB2 database on an IBM mainframe through ODBC and
> transfer it to a dabase on our SQL Server. Everything is done using a DTS
and
> it works fine. For serveral resons I would like to implement a stored
> procedure doing the same task. Does anybody know how to solve this? Specia
lly
> the connection to the IBM mainframe is a bog question mark to me.
> Any help appreciated.
> Rene|||You can find an example in Books Online. From the index,
search on Linked Servers. Select the Access DB2 Database
subtopic.
-Sue
On Mon, 9 Aug 2004 04:33:05 -0700, "Ren"
<Ren@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Me again!
>I found some information about linked server which might help. But how do I
>set up a linked server to DB2? Can someone give me an example?
>Regards
>Rene
>"Ren" wrote:
>

Connect to AS400 table

Hi,
Is there anyway to connect to AS400 table (something like link table feature in MS Access). OR. I have to use the DTS package and schedule it to update in intervals, this i am not preferring due to the performance issue ?

your help highly appreciate

thanks
Murali.As long as you have ODBC drivers that will allow a Windows application to access the AS400 table, you can use sp_addlinkedserver (http://msdn2.microsoft.com/en-us/library/ms190479.aspx) to allow SQL Server to access it too.

-PatP

Confusion for loading data

Hi,
I am building in a data mart using SQL server DTS. I have
confusion that
Example
Suppose we have text file for Cust table. It has 3
customers
A,
B,
C
This information comes in text file from legacy system.
We load this data in cust table in staging.
Now we get a new text file with 5 customers i.e
A,
B,
C,
D,
E.
What query shall I write to include the 2 new customers
i.e D & E in my Staging Table. How will I refer to text
file. Meaning that
select new_customers
from text file (what shall I write here to refer my text
file).
Or how is done.
If some one can solve this simple puzzle it will be great
help.
Thanks
Steve
What I usually do is import the text file "as is" in a staging table and
manipulate the data from there. Tables are a lot easier to query than text
files.
You can get your new customers with a query similar to:
SELECT customer AS new_customer
FROM staging_table s
LEFT OUTER JOIN existing_table e
ON s.customer = e.customer
WHERE e.customer IS NULL
Jacco Schalkwijk
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:49d101c47392$055e44a0$a301280a@.phx.gbl...
> Hi,
> I am building in a data mart using SQL server DTS. I have
> confusion that
> Example
> Suppose we have text file for Cust table. It has 3
> customers
> A,
> B,
> C
> This information comes in text file from legacy system.
> We load this data in cust table in staging.
> Now we get a new text file with 5 customers i.e
> A,
> B,
> C,
> D,
> E.
> What query shall I write to include the 2 new customers
> i.e D & E in my Staging Table. How will I refer to text
> file. Meaning that
> select new_customers
> from text file (what shall I write here to refer my text
> file).
> Or how is done.
> If some one can solve this simple puzzle it will be great
> help.
> Thanks
> Steve

Confusion for loading data

Hi,
I am building in a data mart using SQL server DTS. I have
confusion that
Example
Suppose we have text file for Cust table. It has 3
customers
A,
B,
C
This information comes in text file from legacy system.
We load this data in cust table in staging.
Now we get a new text file with 5 customers i.e
A,
B,
C,
D,
E.
What query shall I write to include the 2 new customers
i.e D & E in my Staging Table. How will I refer to text
file. Meaning that
select new_customers
from text file (what shall I write here to refer my text
file).
Or how is done.
If some one can solve this simple puzzle it will be great
help.
Thanks
SteveWhat I usually do is import the text file "as is" in a staging table and
manipulate the data from there. Tables are a lot easier to query than text
files.
You can get your new customers with a query similar to:
SELECT customer AS new_customer
FROM staging_table s
LEFT OUTER JOIN existing_table e
ON s.customer = e.customer
WHERE e.customer IS NULL
Jacco Schalkwijk
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:49d101c47392$055e44a0$a301280a@.phx.gbl...
> Hi,
> I am building in a data mart using SQL server DTS. I have
> confusion that
> Example
> Suppose we have text file for Cust table. It has 3
> customers
> A,
> B,
> C
> This information comes in text file from legacy system.
> We load this data in cust table in staging.
> Now we get a new text file with 5 customers i.e
> A,
> B,
> C,
> D,
> E.
> What query shall I write to include the 2 new customers
> i.e D & E in my Staging Table. How will I refer to text
> file. Meaning that
> select new_customers
> from text file (what shall I write here to refer my text
> file).
> Or how is done.
> If some one can solve this simple puzzle it will be great
> help.
> Thanks
> Steve

configuring smtp connection on dts in VS2005

I'm trying to send mail through DTS Send Mail Task in the final release of VS 2005 however i'm getting this error "Mailbox unavailable.The server response was:5.7.1 Unable to relay for <email adress>.

I do not have exchange server installed. The smtp adress is correct. So what could be the problem?

I appreciate your help

Thanks

Network issues

Incorrect address

DNS failure

Authentication failure

etc...

The list of things that it COULD be are endless. Presumably you think that the problem exists somewhere in SSIS. Have you removed SSIS from the equation and see if it works then?

There isn't really enough information in your post to diagnise what the problem may be.

-Jamie

|||Christina,
The error message you are receiving indicates that the mail server you are trying to send mail through does not allow relaying. This is the usual setup for mail servers to cut down on spammers sending email through legitimate mail servers.
I would contact the administrator of the mail server you are trying to attach to and see if he can setup relaying for your SSIS box or email account.
Larry

|||

has anything changed in the configuration of the ssis send mail task? because in the beta version (before i moved to the final release) it was sending normally without any errors.

i can ask the smtp provider to allow relaying but that would be risky as you said.

thanks and happy new year

|||

i checked with the mail server administrator and relaying from the email address i'm using is allowed. so it's not an issue of relaying.

i also tested sending a test email from command prompt using smtp commands and it sent the mail normally. however from sql i still get the relaying error.

any clue?

thanks

|||

problem is solved. i used a different smtp server and unchecked the "use windows authentication" in the smtp connection manager. i thought it wouldn't matter since i didnt have exchange server. but it seems to have counted.

thanks to all

Christina