SQL 2000 DTS And Excel

Nov 16, 2007

Question in 2 parts...

(1) Forgive me for asking the obvious first... is this the correct forum to post a MSSQL2K DTS question? If not then can you point me to the form 'cause I couldn't find it. If the post is okay here then read on, please.

(2) DTS Question -- Note: the package runs once per day

I've created a package that does a transform to an Excel worksheet. Works like a charm. However, I want new data to write over existing data. Now, new data is appended. I worte a VBS ActiveX script to clear the contents of the Excel file first but that caused a new problem, i. e. the package throws an error saying that column names couldn't be found. Made a change to keep the column names... well that didn't work either.

So, how can I overwrite spreadsheet data with new data?

Any and all help will be deeply appreciated.

thanks in advance.

-- Garibaldi

View 1 Replies

Excel Report Export Causes Excel 2000 To Crash On Print

Dec 5, 2007

When I open the spreadsheet in Excel 2000, it works fine. When I try to print, it crashes Excel. In testing, I narrowed it down to the Header/Footer, because it also crashes when I go to Page Setup and click on the header/footer tab.

However, I can print the same spreasheet from Excel 2007.

Am I just dealing with a "you need to upgrade all your clients" situation, or is there a known issue with certian formatting that is passed out with reports that is not supported by older versions of Excel?

I am using Reporting Services 2005 SP2 to serve up the report that is exported to Excel.

Any assistance is appreciated.

View 3 Replies View Related

TSQL + VBA Excel 2003 - Importing Data From MS Excel 2003 To SQL SERVER 2000 Using Multi - Batch Processing

Sep 11, 2007

Hi,
I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000.
The string I need to import is composed by 5 different several blocks and looks like:

Code Snippet

CommandLine01 = "USE mydb"
CommandLine02 = "SELECT Block ..."
CommandLine03 = "GO
ALTER TABLE Block...
GO"
CommandLine04 = "UPDATE Block..."
CommandLine05 = "SELECT Block..."

The detail of the SQL string is at:
http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

I am trying to implement OJ's suggestion:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1
to use multi - batch processing to import the string to SQL SERVER, something like:

Code Snippet
Dim SqlCnt, cmd1, cmd2, cmd3
'set the properties and open a connection

cmd1="use my_db"
cmd2="create table mytb"
cmd3="insert into mytb"

SqlCnt.execute cmd1
SqlCnt.Execute cmd2
SqlCnt.Execute cmd3

Below is the code (just partial) I have, and I need help to complete it.
Thanks in advance,
Aldo.

Code Snippet
Function TestConnection()
Dim ConnectionString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet

ConnectionString = "Driver={SQL Server};Server=myServer;Database=myDBName;Uid=UserName;Pwd=Password"
ConnectionString.Open

CmdLine01 = " USE " & myDB
CmdLine02 = " SELECT ACCOUNTS.FULLNAME FROM ACCOUNTS" ...

CmdLine03 = "GO
ALTER TABLE Block...
GO"

CmdLine04 = "UPDATE Block..."
CmdLine05 = "SELECT Block..."

RecordSet.Open CmdLine01, ConnectionString
RecordSet.Open CmdLine02, ConnectionString

ConnectionString.Execute CmdLine01
ConnectionString.Execute CmdLine02

'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next

ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet

'Close ADO objects
RecordSet.Close
ConnectionString.Close
Set RecordSet = Nothing
Set ConnectionString = Nothing

End Function

View 7 Replies View Related

SQL Query In Excel 2000 Using VBA

Apr 1, 2004

Using VBA, I have created a Sybase SQL query. I need to edit the query to read a date from a cell in an Excel worksheet.

Any tips on how to indicate that instead of a set date I can refer to a worksheet cell?

Any help is greatly appreciated.

Brandon