(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?
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.
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:
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"
i have a spreadsheet with 4 workfiles and i want to import into sql. i want to make one workfile into one table in sql how do i go by doing this? and spreadsheet has a lot of macro too thanks
We're running SQL Server 2000 and our client is using Excel 2000. When a report is exported to Excel they can't read them. Excel 2003 works fine. We're both on SP4
I was wondering if anyone knew how I could export data from SQL Server to a specific Excel worksheet with an Excel file, such as "Sheet2" or "Sheet3" for example, instead of dropping the data automatically in to Sheet1.
I am wondering if there is a solution for our current issue i.e. we can't export the query report from SQL report services into 2007 Excel. We have no problem with Excel 2003.
Hi all,In MS SQL Management Console I can right-click on any Table and I havethe option All Tasks > Export Data where I can export the table toExcel. In a View however this isn't there. I have many views I wantto simply export to Excel, but the only way I've found to do it iscreating an ODBC connection to the MS SQL database from MS Access,linking the Views to Access Tables, and exporting from Access. surelythere's someway to export a View to Excel within MS SQL easily likeexporting a table...Thanks ---Alex
Hi,I have a need to process a column of data and get information from SQL2000 and return it to the Excel Spread Sheet.The data will be a list of order numbers in column 1. I need to lookup each order number and return infomration relating to the order fromSQL.Can anyone point me to the right process?thanks in advance,
I need to import some excel files in the Database via DTS. The problem is, that I don't know the names of the Excel Sheets (there are multiple sheets in the excel file). Is it possible to get the names of those sheets? (via activeX or SQL Query or anything, but from a DTS package)
i have a table in sql 2000 db and want to import data from excel sheet in to the table. my table = Table1 excel file = data.xls is there a simple method where i can import data from the sheet into the existing table?
I am trying to import an Excel file - when I pick the file I get the message "Could not open file for reading. Close any other application that may be locking the file." I have verified that the file is not open - I have even rebooted the machine - still the same message - what am I doing different? Please advise. Thanks
I want to import MS Excel data into MS SQL 2000 and I am programming this in VB 6.0. I am referring the article http://support.microsoft.com/kb/316934/EN-US/ for this. I can able to import Excel data successfully into SQL database table. But the problem is, the order of exported data in SQL table is not matching that of MS Excel data. All the rows were jumbled, which made it totally unusable.
Please guide me how to Import Excel data into MS SQL in its original order only. Any other method other than what I am following is present; please suggest me to keep the original order.
Data for Source Column 15 'Notes' is too large for the specified buffer size. How do I get around this, I can see some of the notes entries are beyond 255 chars so I changed the destination datatype to textI have never seen this error when importing before. What do I do?
I am trying to create several Excel sheets using SQL 2000 views like so:
Select * INTO [Excel 8.0;Database="C:spreadSheetsaNew.xls"] FROM [aView].
When I try and execute this in my app I get the following - Specified owner name 'Excel 8.0;Database=c:spreadSheetsaNew.xls' either does not exist or you do not have permission to use it.
If I use the above Select statement with an OLEDB connection it works.
I am using Imports System.Data.SqlClient, instantiating a new SQlConnection object, opening the connection, etc..
I have a report that I am trying to export to excel. The export seems to work but the data in the spread sheet is incomplete. The document map is completely exported but the work sheets are not. The links in the document map stop being links after the first occurrence of the lowest level detail. All the data is visible in Report services and all the data exports to PDF. There are 5 levels and 124 pages in the report in the report. I have other reports on the same server that are larger (7 levels and 512 pages) and they work just fine. They export to excel with no problem. Any ideas or input would be very appreciated. Ayla
when i export to excel reports having a drill-down, in the excel file i don't have the possibility to drill down data. i have memory that once it was possible. does anyone know how to help me? is it possible that a service pack or any updating could have change configurations?
Hi -- I have a different problem than most people with a Reporting Services 2000 report rendering to Excel 2003.
My report has 5 different subreports. I want them to appear in Excel as 5 separate tabs. When rendered through the browser (using Report Manager), the page breaks are correct -- there are 5 pages.
When exported to Excel, the first report is on a separate tab, and the other 4 are jammed together on one tab.
Each report consists of between 1 and 4 tables. The last table on each report has the "page break after this table" checked.
The same report deployed to SSRS 2005 page breaks the way I expect in Excel. In SSRS 2000, it doesn't.
How to upload excel data to sql server 2000 thorugh .net application. I want like one upload button should be there,we have to browese corresponding excel file and then we need to upload to database. before uploading it has to ask appending or replace everything in table. both options appending and replacing shoulb be there. How to upload ? any sample code is there plz give me.
I was trying to export a report which contains a number format. When I do that, all the numbers in excel will have a green small tag beside it saying "Convert from Number to Text".
Is there anyway that I can change the format to a number when I export it to excel?
I am doing a project where I heed to extract data from a table in a sql server and then automatically create a bar chart with this data preferrably in PowerPoint (or excel). I was thinking myself that it may be done in vba/macro added to a button in powerpoint. If anyone knows how to do this i would be very grateful or if anyone know a useful website could they post it up.
I would like to know recommendations on automate the following:
1. I want to know how to take data from various sql server 2000 data queries and load the data onto excel spreadsheets in an automated method. Some of the data loaded into the excel spreadsheets load detail data and some load data into pivot tables.
Is there any way that the data can be taken from sql server to the excel spreadsheets using odbc connections?
2. Some of the data taken from sql server 2000 database is loaded into pdf files. Is there any way this process can be automated?
I have a Sales report with 3 lists each being nested inside of the other and they also each contain a Matrix. Top list is for Region, followed by District and then State. I have page breaks setup so that each region appears on it's own page, followed by each District summary appearing at the top of the page with a breakdown on that page for that Districts states. In total the report exported to PDF or printed runs around 19 pages. This report when exported to PDF or printed works fine.
When I export it to excel I get a full document map on the first worksheet. I then get 10 worksheets of information breaking off the last 9. The document map links reflect the absence of this data by not actually being links from that point on. If I remove page breaks so that the number of pages created are less than 10 excel worksheets the export works fine and my document map links are created.
So does the export to Excel have a limitation on the number of Worksheets that can be created? In my case it appears to happen at 10 each time. Oh and by the way I've attempted this in VS.2003 and 2005 and SQL Reporting Services 2000 and 2005 for the same reports.
Hi guys, When I thought everything is okay with this script, I got a new problem... I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000. in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results. The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR: run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors. Thanks in advance, Aldo.
Below the code:
Code Snippet Function QuerySalesAging() '-------------------------------------------------------------- 'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library '--------------------------------------------------------------
Dim ConnString As New ADODB.Connection Dim RecordSet As New ADODB.RecordSet
'Report Criterias Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange ' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay. ' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table TemporaryTableName = "CTE" ' The table is a regular one CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..." CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName CmdLine03 = CmdLine03 & " FROM ..." CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")" CmdLine03 = CmdLine03 & " ORDER BY ..."
Trying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
