I have this script which outputs a combination of financial data. I have recently joined a table that includes a narrative and when I run the query, it runs perfectly. However, when I copy and paste the output into Excel, not all the data is showing. After the process of elimination, it is because Excel doesn't like the narrative column and not all data is copied across which is very annoying. If I remove the narrative column, then all the data copies over correctly.
OLE DB source which calls a stored proc that returns a result set
data conversion
Excel destination I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.
I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.
What am I missing or doing wrong? Thanks in advance
We are about to implement SSRS reports (SQL Server 2012), and have found one issue we don't like. The report looks great when we open it in pdf format. However, when we open it in Excel format, we want it to be the raw data, not look like the pdf report. I tried the simpl headers, and that removed the page header/footer. However, the report format was still in play.In other words, we would like the report when opened in Excel, to look just like it does when we open it as a CSV.I have tried to modify the config file a couple of times. Here is my latest attempt:
Essentially I have set Visible = "false" for the Excel type, and tried to duplicate the CSV, changing the name to make it look to the user like it is "Excel". This fails. I checked the event viewer, and I see two entries.. The RSReportServer.config file has been modified. (Information)The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.I literally took the file, opened it in Notepad, pasted in my changes, from another notepad window, and tried to run the report. I did not see EITHER of the two names I expected, using the OverrideNames tag. As I said earlier, I was able to affect the outcome, by setting SimpleHeaders to true (or however it is identified). That did work in that the page header and footer was removed.
I am using a sql command to produce outputs having 3 columns Before running the sql I am spooling the output in .xls format But when I open the .xls file (i.e. the spooled file) data of all the 3 columns appear in a single column with blank spaces between them Is there a way by which I can ensure that each output column of the SQL appears in a seperate column in the excel Sheet
I need to get the output of a SSIS package in an Excel. I have made a Stored Procedure for getting the output, since the output has variable number of columns hence i have made a table through the stored procedure which is dynamically created when the procedure is executed. Now i have a table in SQL with the output data.
In my report I have used the "jump to url" for the report output but when I export this to excel I get a column with the correct data and also the url.
Is there anyway of exporting the data without the url?
I want my users to be able to view the report and click on the column the want to URL jump to but also be able to export just the raw data without the URL?
I am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.
the command I am using is this.
declare @x varchar(300)
set @x = 'bcp "select * from Northwind..orders" queryout c: est.csv -S local -U sa -P passwd -c -C RAW -t "," -r '
Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?
Also an easier question: What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)
I would like to create a SQL job that runs a basic query monthly and saves the results as a .xls file and emails the results to a user. I've been unsuccessful with sp_send_dbmail in that the output file is always a single column. Any suggestions?
I use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
I created an Excel Source and used a query to get the data,i.e
SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]
The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?
I'm a newbie to SQL. I'm using SQL Server 2012 on my local machine and I need to find a way to output my queries as Excel files. I came across these codes for Interactive SQL (what is intercative SQL by the way?) but they don't work in the SQL query window:
SELECT * FROM SalesOrders; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls); DBQ=c: estsales.xls; READONLY=0' INTO "newSalesData";
It seems the "OUTPUT" command is not a valid command. I really liked that piece of code (may be because it is so simple and carries over very few lines)! Do we have something similar for SQL Server 2012 that can do the job?
Deaa group,I am using SQLServer 2000 in an XP Sp2. I would like to do thefollowing:I have a program running on a database server that generates some datawhich are loaded to the database. This program is used in a webapplication, invoked by some java program and JSP scripts. (I amfrontend illiterated.)The question is, is it possible to write a stored procedure to generateoutput in excel spreadsheet? So that user could call this procedureand get spreadsheet output on the client side.Any pointer to a solution would be immensely apprecaited.thanks,charia
INSERT INTO RCSAdvantage VALUES('123','Mike','Bhatt','12/12/2003','123','RCSA')
INSERT INTO RCSAdvantage VALUES('TM123','Mike','Bhatt','12/12/2003','456','TRIMICRO')
INSERT INTO RCSAdvantage VALUES('INR234','Mike','Bhatt','12/12/2003','890','INSIGHT')
INSERT INTO RCSAdvantage VALUES('INR234','John','Bhatt','12/12/2003','890','INSIGHT')
I needed to run following cursor and get the result exported to excel file. But Cursor retrives two resultset and while exporting to excel spreadsheet , it is the only first resultset without second resultset. How can it be exported to excel as a single resultset combined of first and second one.
I need to create a query (SQL 2000) that renders a formatted excel (xml or xls) file for each row that is outputted.
The details, I have a Campaign table that contains information for Auto and Life "Leads" and the data is submitted by telemarketers directly into the database. I need to render a file for each line, and it would be good if It were an Excel XML or XLS file, because that's what we've been using for a while.
Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).
I have a relatively simple task - create an excel spreadsheet with 3 columns of data - Id, Description and Sales. ID and Description are text, sales is int.
So my SP aggregates and creates my resultset in my OLE DB Source in the Data Flow. It proceeds to the Excel destination, and that all seems fine. My issue is that the data is being written as text. Looking at the excel destination in Advanced editor: the Excel Destination Input, Input columns are formatted as I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and DT_I4 for the Sales. Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.
The target excel spreadsheet is being created from a template file. That template file has header columns. The target column for the Sales has the entire column formatted to NUMBER (0 decimals). Yet to now avail.
When I check the spreadsheet, the column has retained the cell formatting, and I have a 'I' pop-up to inform me that 'someone' has inserted text data into the number column (even though the data IS number).
Since the SP spits out INT, it isn't a case of receiving a text value, imho. While trying to change the external column data type in the advanced editor, SSIS is quite happy to let me change the value for the Sales output to DT_I4, apply, and ok. Then, when I open it immedaitely aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't handle it, at least tell me when I try and change it. don't let me change it, and then revert back without telling me! Grumble grumble...
I'm writing an SSIS package to run a series of select statements and save the results of each to a sheet in an Excel file. An indirect configuration file with these entries is used:
At the start of the package this setup will happen.
1) copy ExcelTemplateFilePath to ExcelFilePath
2) Set User::ReportFilePath to ExcelFilePath
Creating a script task to do this is not a problem. However the task fails on validation of the tasks that write the select results to the Excel file sheets. I'm thinking the validation fails because the Excel file is not copied before the validation is done.
How can I arrange for the setup script to run and copy the Excel file before validation?
Is there is a better way or am I just doing this wrong?
When we export data mining output including dates through Reporting Services to an Excel spreadsheet, Excel 2007 subtracts 4 years from the dates. Example, the date 11/16/2006 appears in Excel 2007 as 11/16/2002. How should this be handled? Workaround, bug fix, patch,etc.? Thanks, Sam
I have set up an export package in SSIS and I would like to assign a file name based on the value in a table on my SQLServer. For example, my file name might be Monthly Reporting Package 20070731.xls where 20070731 is a value in my date table. I can retrieve my date by the following select statement: "SELECT Date FROM DecodeTables.dbo.Date". Can I set up a variable to look up this value and then concatenate it into my file name?
I created a report in Reporting Services which was working. Then I filled in the "No Rows" section in the properties of a tablix in the report. Now it shows the No Rows message even if there is data. If I open the Shared Dataset that is used in the report and open Query Designer and run the query I get data.
Agent State Exposure Insured Name Rogers Inc MA 100,000 John Smith SAN Group RI 200,000 Jim Morrison SAN Group RI 100,000 Jimi Hendrix 123 Agency MA 300,000 Mickey Mouse Rogers Inc MA 50,000 Mike Greenwell
I want to be able to read the file and create new excel files for each Agent listed. So for Example, the above file would create 3 separate files since there are 3 different Agents listed. Each Agent file would contain the same information from the original file. The name of the file would be somethign like AgentName.xls...So the SAN group file would have this:
Agent State Exposure Insured Name Rogers Inc MA 100,000 John Smith SAN Group RI 200,000 Jim Morrison SAN Group RI 100,000 Jimi Hendrix
Here is my code. Basically what I am doing is selecting from the database based on the current user. The ReaderResults.Text label is not showing the info pulled from the database, and the ReaderError.Text label is not showing an error. I also tried putting the ReaderResults.Text label inside the loop and that didnt work. Any suggestions? 'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")Dim currentUserID currentUserID = Context.User.Identity.Name.ToString() Label1.Text = currentUserID Dim selectSQL As String selectSQL = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')"Dim cmd2 As New SqlCommand(selectSQL, con) Dim reader As SqlDataReaderDim CompanyKey Try con.Open() reader = cmd2.ExecuteReader()Do While reader.Read() CompanyKey = reader("CompanyKey").ToString() Loop reader.Close()Catch err As Exception ReaderError.Text = "Error selecting record." ReaderError.Text &= err.Message ReaderResults.Text = CompanyKey Finally con.Close() End Try
The ReaderResults.Text is not returning a value. I am not sure what is going on because the table and all columns are full, Label1.Text shows the current username of the user loggedon, and no error is showing up in ReaderError.Text. Anyone have any ideas? Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true") 'Job1 InfoDim currentUserID currentUserID = Context.User.Identity.Name.ToString() Label1.Text = currentUserID Dim selectSQL1 As String selectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')"Dim cmd1 As New SqlCommand(selectSQL1, con) Dim reader As SqlDataReaderDim CompanyKey 'Job1 Select Try con.Open() reader = cmd1.ExecuteReader()Do While reader.Read() CompanyKey = reader("CompanyKey").ToString() Loop reader.Close()Catch err As Exception ReaderError.Text = "Error selecting record." ReaderError.Text &= err.Message ReaderResults.Text = CompanyKey Finally con.Close() End Try
I have a Report Parameter Non-queried list for all months (Labels January, Februari... and Values 01,02...). When I pick a month in my report all works fine But I would also like to have data for the complete year (via an All selection) How should I get this done . Any help appreciated Edwin
IS that possible to get teh output of a execute sql task to excel destination.I have query which will comapre the data difference between two databses. It will comapre all tables in both databses and list out the difference in data by each table. I need to run this query using SSIS and need to get the output to a excel sheet...I have used the data flow task to run this query but my query is giving some error when used with data flow task. So i have used excecute sql task and need to write teh out put to a excel sheet.
i am looking for code to show the last entry of a database that has a unique field. for eg.. i have columns AREA, NAME, NUMBER. i need to show the last entry for each unique area. so if there was 5 seperate areas, the table would show the last five entries for each area. anyone help?
I have a linked server that was built on PostgreSQL database connection. When I try to query one of the tables in the linked server it is giving attached error.
I am making the switch from using MSACCESS to do required reporting to using Reporting Services to accomplish the same tasks.
A requirment I have is that whenever a graph (chart) is created, it contain the underlying data in a connected and aligned data table. MSACCESS allows this by selecting the chart options and then "show data table".
Is this ability included in Reporting Services in MS Visual Studio 2003 .NET? If so, how can I enable it? I currently can only enable the legend.
Currently we are using:
SQL Server 2000 (sp3a) Reporting Services (sp2) Visual Studio .NET 2003
