Insert_Recordset Function from ActiveX VBScript

Posted on 2007-10-18
Last Modified: 2012-06-21

I have looked at a few posts, and also the following MSDN Site

Ideally I would like to Open a Recordset using a DTS ActiveX Script (VB essentially) and once I have the recordset open, i want to then DTS it into a Table in my SQL Server.
FROM Recordset
Insert_Recordset rs

This is instead of the Inserting each record individually which I have found in a few places on the web and EE.  I think you must be able to just Insert Into somehow??
I look forward to your thoughts.
Thanks in Advance!
Question by:Craig Lambie
    LVL 12

    Expert Comment

    Check OPENQUERY in Books Online. It is just what you want.
    It defines any ODBC connection and queries resultset from it.

    LVL 1

    Author Comment

    by:Craig Lambie
    Hi Patrikt,
    Unfotunately OPENQUERY only works against a linked table,
    You can use the sp_addlinkedtable function in the SP and then delete the Linked table.  But I was thinking it was a little messy, and will play havoc when I move onto the Live server (DBAs takeover)
    So ideally I want to be able to get the rs in ADO then copy all the records into possible another rs or DTS across.
    Ie the resultant rs of the ActiveX can then be transferred to a local table using DTS?
    Similarily to using OPENQUERY in your SQL statement area of a DTS package, and using the resulting columns to Transform into the SQL server connection.

    I am increasing the points as this is a little harder than I expected..
    LVL 12

    Accepted Solution

    Sorry, I not clearly understand what you need.
    There are severelal possibilities to connect to ODBC source and get data.

    If you talk about DTS you can do all this there and use package parameters to modigi source connection as you need. But DTS is primary intended for import processes where performance is not primary.

    Second possibility is OPENQUERY. You talk about linked table which I think you mean "linked server". If it is not possible to work with linked server in your environment you could step up to OPENROWSET which allows ad-hoc connection string.
    LVL 1

    Author Comment

    by:Craig Lambie
    Hi Patrikt,

    Thanks for your input.  I am going to give you the points.

    However I couldn't get OpenRowSet to work for some reason?

    What I ended up doing was using ADO in an ActiveX script to connect to my source, put the result set into a TempTable and then did the the join i required on that temp table.

    Seems to have worked great.

    Thanks for your suggestion.
    No idea why the OpenRowSet didn't work, I am thinking it is due to Security settings or something on my source (mind was an plain excel file?) might be due to Server not being able to find it, although that shouldn't have been the issue as server has full permissions for the Network location i had the file on.

    Thanks again.
    LVL 12

    Expert Comment

    You posted to SQL 2000 so I supposed this version but if you use OPENROWSET on SQL 2005 there is security block which has to be configured before use. So if on SQL 2005 read BoL article about OPENROWSET, it will show you a way.

    Second note is about Excel. Its ODBC is single-user even if only reading. It can cause lots of troubles when accesing sheet before other connect closed it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Title # Comments Views Activity
    pains and challenges of SQL DBAs in 2016.. 2 26
    SQL Server Fine Tuning 7 38
    report c# 9 56
    Poweshell file to remote server 11 17
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now