Connect to a DSN for data

I have an AS400 system and I created a DSN to see the data on my workstation. The connection works fine. I created the samne connection on the Server that has SQL 2005 installed. What I need to do is to populate the SQL database with tables from the AS400 table but I am having trouble connecting a DSN to the SQL database.

Can anyone offer some advice or a website that could walk me through this?

Hello,

I think you should be using Integration Services for this, Reporting Services does not do any type of data transfer. IS will be able to handle this easily, one connection will be your SQL Server, the other will be a ODBC or OLE DB connection using your DSN.

Hope this helps.

Jarret

Connect SQL 2005 to another database?

I'm trying to connect my SQL server to a Progress database. I have the DSN setup but I don't have a clue how to get to it or the database I'm trying to access. All I want to do is be able to view all the tables in SQL from the Progress database so I can build reports. I can't imagine this is too difficult but right now it is for me. Any help is greatly appreciated.
Thanks,
Tony Sheehan
Tony
Linked Servers?
"Tony" <tsheehan@.tji-holdings.com> wrote in message news:%23qeeAlMbIHA.1168@.TK2MSFTNGP02.phx.gbl...
I'm trying to connect my SQL server to a Progress database. I have the DSN setup but I don't have a clue how to get to it or the database I'm trying to access. All I want to do is be able to view all the tables in SQL from the Progress database so I can build reports. I can't imagine this is too difficult but right now it is for me. Any help is greatly appreciated.
Thanks,
Tony Sheehan
|||Hi Uri,
Thanks for the idea. I managed to speak with someone yesterday that mentioned this and this is the answer I was looking for. Thank you for your reply.
Tony
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eg8bdBVbIHA.3696@.TK2MSFTNGP03.phx.gbl...
Tony
Linked Servers?
"Tony" <tsheehan@.tji-holdings.com> wrote in message news:%23qeeAlMbIHA.1168@.TK2MSFTNGP02.phx.gbl...
I'm trying to connect my SQL server to a Progress database. I have the DSN setup but I don't have a clue how to get to it or the database I'm trying to access. All I want to do is be able to view all the tables in SQL from the Progress database so I can build reports. I can't imagine this is too difficult but right now it is for me. Any help is greatly appreciated.
Thanks,
Tony Sheehan

Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...
Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_nam e.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizard
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_name.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizar
d
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

connect by DSN

Hi,

how can i connect MSSQL through System DSN. i know how to create DSN. but i don't know how to code the connection string.

regards,

SeeConnectionStrings.com.

Ryan