by dsra » Thu, 27 Jul 2000 04:00:00
I am trying to export data from SQL 7.0 to Excel through
VB application.
I tried the sample given in SQL
Mssql7\devtools\samples\dts\dtsexmp3\
I was able to export between two DSNs of SQL database,
but failed to export from DSN of SQL to DSN of Excel.
Can some one help me out.
I tried to specify the XL file connection object without giving
the DSN but I couldn't find the "file connection" object in
DTS Package.
Thanks in advance.
dsrao
by Darren Gree » Sat, 29 Jul 2000 04:00:00
Create a package that does what you want, then use ScriptPkg to get theQuote:>Hi,
>I am trying to export data from SQL 7.0 to Excel through
>VB application.
>I tried the sample given in SQL
> Mssql7\devtools\samples\dts\dtsexmp3\
>I was able to export between two DSNs of SQL database,
>but failed to export from DSN of SQL to DSN of Excel.
>Can some one help me out.
>I tried to specify the XL file connection object without giving
>the DSN but I couldn't find the "file connection" object in
>DTS Package.
>Thanks in advance.
>dsrao
Q. What is ScriptPkg and where does it come from?
A. ScriptPkg is a small utility written in Visual Basic 6. It can
be found on the SQL Server 7 CD-ROM or on your PC, if you selected to
install the Development Tools. Run the self-extracting archive
\mssql7\devtools\samples\dts\dtsdemo.exe, then look in the new folder
"Designer".
ScriptPkg loads a package and generates Visual Basic code to re-build
the specified package. It is very useful in understanding the DTS object
model, as you can compare properties set in the designer with the code
produced. Using this knowledge you can then dynamically alter Task and
Connection properties at runtime making your packages more flexible.
The SQL Server 2000 DTS Designer has a new Location "Visual Basic File"
from the Save As dialog which performs the same function as ScriptPkg,
but produces neater code.
===
v1.00 2000.03.19
Applies to SQL Server versions : 7.0, 2000
FAQ Categories : DTS
Related FAQ articles : n/a
Related Microsoft Kb articles : Q239454
Other related information : n/a
Authors : Darren Green
--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/
1. Export data to excel from sql server using a sql query
Please can any one solve my problem ?
the problem is
I want to export data from my table in sql server to excel files
I have achieved this using this query
"select * into [excel 5.0;database=c:\abc.xls].[sheet1$] from
TabDetails"
but this works only with Access and not in sql
I Got some help Doing this
step 1 - Added a Linked Server using sp_addlinkedserver to EXCEL
step 2 - created a login using sp_addlinkedserverlogin
step 3 - fired the following query from query analyser
" SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data
Source="c:\abc.xls";User ID=Admin;Password=;Extended properties=Excel
5.0')...Sheet1 "
Now I am getting the error
could not find the INSTALLABLE ISAM
PLZ note :
I don't want to use DTS I want to use a SQl Query
sql server is installed on machine A and I am using the query
ananlyser of machine B and if I say "C:\abc.xls" then this C drive is
of machine A or machine B ????
Thanks for any help and sorry for trouble caused
Abhishek
3. DTS Package to Export to Excel (source query stmt unknown at runtime)
4. Help for relational DB needed
5. Export Data using DTS Import using BULK INSERT
7. Trouble exporting query result to Excel worksheet using DTS
8. Access ODBC with VB4 connect problem
9. using DTS to import Excel data into SQL Server 2000
10. Data Conversion From SQL Server to Excel File Using DTS
11. Exporting to Excel from DTS (using copyfromrecordset) causes Server Busy Dialog
12. Tagged data export using SQL/DTS
13. Crashes when Exporting data from SQL 7 to Access 97 using DTS