Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Insert_Recordset Function from ActiveX VBScript

Posted on 2007-10-18
Medium Priority
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
  • 3
  • 2
LVL 12

Expert Comment

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


Author Comment

by:Craig Lambie
ID: 20107899
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

patrikt earned 750 total points
ID: 20107974
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.

Author Comment

by:Craig Lambie
ID: 20122298
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

ID: 20122402
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.

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

578 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