Insert from DB2 into MsSql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert from DB2 into MsSql

Author

Topic

xakeko
Starting Member

5 Posts

Posted - 2010-04-29 : 05:47:48

Hi, I am not sure if this question fits into this area.
I will import data from DB2 into MsSql. I did it wit the import task an it works fine. But I will put it into a stored procedure instead into an dts. How can I do this, what do I need to put the connection and security etc into my proc. Thanks for any help
Cheers Volker

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 05:54:25

Have a read up on OPENROWSET. You can use that to open a remote dynaset on another server, which can be read/write. Open one each for DB2 and MySQL, and select from one into the other. You can pass all the connection details to the OPENROWSET call.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

xakeko
Starting Member

5 Posts

Posted - 2010-04-29 : 07:14:31

Hi, i got the following error message, and i am not the admin to change it

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-29 : 07:26:15

Why not to save the import, generated by the wizard, as an SSIS-package?

No, you're never too old to Yak'n'Roll if you're too young to die.

xakeko
Starting Member

5 Posts

Posted - 2010-04-29 : 07:32:59

because therfore I need the admin, too. And I will be on my own and flexible, to start the proc/query from my webpage, when I need it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-29 : 07:38:59

Then have a meeting with the admin...

No, you're never too old to Yak'n'Roll if you're too young to die.

xakeko
Starting Member

5 Posts

Posted - 2010-04-29 : 07:44:57

it´s a rule from our company, that we cannot change :-(

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 08:44:42

Does it have to be done with SQL Server? You could use MS Access to do this if you're desperate.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

xakeko
Starting Member

5 Posts

Posted - 2010-04-29 : 09:05:58

;-) I am not desperate, I just look for a flexible solution. If it works with a import via dts it should work with an query/proc. I thought it wasn't that difficult...

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-04-29 : 16:07:29

Did you try OpenQuery? I've used this several times with linked server

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   field1 as product,
           field2 as name,
           sum(cast(field3 as decimal(15,2))) as qty
           from       LibraryName.File
where      field3 > 0
group by    field1
order by   field1
fetch first 3 rows only'')'

insert into #TempTable1
                   (
                    product_code,
                    product_name,
                    product_qty
                                   ) 
exec (@SqlString)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources