I'm building a DTS package to query some tables and export the result to my local c: drive in Excel format.
I have a batch file that stamps the date and copies the file to another location (in the same package). It appears that the process is trying to stamp/copy the Excel file before the file is finished being written to. Is there any way to have the package wait for the completion of the file before the batch file starts? I have inserted a wait command (30 secs to 30 mins) within the batch file to no avail. I can run the package without the batch file and it completes within 10 seconds. Thanks.
My access database downloads the last 6 months stock market data i.e close, high, low price. However, I want to show the results in Excel but only the last three values for each stock (as I have over 1000 stocks).
Need some help. We don't have any SSIS developers (and won't pay for any), so it has fallen to me to resolve. I have been given a few tSQL queries were the results set needs to be in excel format and sent be either exchange our our secure FTP systems to vaious recipients. The SQL is very straight forward, but I don't know how to set up the various components inside VisStudio to pull this off.
Anyone know of an "idiot-proof" white paper or tech doc, preferably with screenshots, on how to do this step by step? I've spent an hour on google and apparently am not using the correct "secret" word combination to find a link for something like this.
I've a question regarding manipulating a SQL query output so that I can copy the result set into Excel. The copy etc will be done via a .net application and is not where the problem is. The issue I am having is managing to return a result set in a tabular format which appears as I need it to in the excel.
The simplified down SQL result set looks like the following
I am trying to allow a couple of users to log in via SQL server authentication through MS Excel to run database queries on selected views. I have created a database role for these users and given select permissions to the views I want them to see and that works fine. The only problem is that they also see all of the sys views that they should not be able to see. When I try to deny select permissions to these sys views I receive an error message indicating that I cannot deny select permissions to the sys view (there are several of them). Is there a way I can hide the sys views for the database role? I am using SQL 2005 and SQL Server Management Studio.
I usually export the query using "Results To File" and save it as a csv. I think open MS Excel, open the csv file from Excel and then use the delimited wizard. Is there a quicker way with less steps?
I have a rather long SQL query to run with several date/time variables in it. The way I've done it in the past is to record a macro, paste the SQL into the query then afterwards turn the dates/times into variables by manually editing the code in the macro, so that next time it is run the SQL picks up the correct date/time and the correct data is retrieved. This has always worked okay in the past. However, the latest query I have is very long and complicated and I now appear to be unable to use the above method due, it appears, to the length of the query. It seems that the macro itself will only accept so many lines of an SQL query.
Please, does anybody know how to get round this problem?
Is it possible to save the query as an external query (.dqy) and also use variables within that query when a macro tries to run it? If so, how!? I've tried it, but it fails at the line ".Refresh BackgroundQuery:=False". With a general ODBC Error. I have put all of the necessary details in there, eg DSN etc, but no joy.
I've tried putting the SQL itself into several cells on a spreadsheet, altering the dates so that they a correct each time the query is run, concatenating the cells, turning the results into a variable and executing the SQL in a macro, but this also fails at the line ".Refresh BackgroundQuery:=False". Could this method be adapted?
Splitting the query into two shorter ones doesn't appear to be an option as there a loads of 'or' statements in it, several of which overlap. This would cause some results to be duplicated, which I would like to avoid!
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 '
I'm trying to query an excel file and I get a mistake. The query is as follows:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:ExcelFile.xls', 'select * from Sheet1')
and I get the following error message:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
How could I export the result from Query? I'd like to run the query and at the same time the result must be saved in Excel Worksheet. is this possible? How?
Hi,I am using a .dqy file to import some rows from a sql server 2003database to microsoft excel. Unfortunately, I have am importing morerows than excel can handle. Is there a way around this?It appears that there is no equivalent of the LIMIT(offset, number ofrows to return) function of mysql in tsql. Had there been anequivalent, I would have created multiple .dqy files, which would querythe database for pages of results as appropriate. There is an upperlimit to the number of records that there can be in the database, sothis would have been a feasible solution.Also, I must use .dqy files (or something equivalen) because thequeries are invoked from a website, and it is necessary to download theresults, sort/filter, etc. (in excel).Thanks for any suggestions.
I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;
-It has one worksheet in it named 'Add Revenue Accts'. -The name of the workbook is 'Revenue_to_All_Accounts.xls' -Its location is \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_Accounts
I have the linked server configured as follows;
-Linked Server; REVENUE_TO_ALL_ACCOUNTS -Provider; Microsoft Jet 4.0 OLE DB Provider -Data Source; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls -Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls;Persist Security Info=False
When I attempt the following query; SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')
The following message appears, and no results are returned;
[OLE/DB provider returned message: Unspecified error] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?
I set up an Excel spreadsheet for several users that queries a SQL Server. I provided the spreadsheet to the users and set it to refresh when ever it is open. The query is working fine. The problem is the users are prompted with a SQL Server login screen every time they open the file. The login screen shows the server name, the username and the password. All they have to do is press 'OK' and the screen goes away. Why are they being prompted with this screen? What can I do to eliminate the users from even seeing this window?
I also receive this window whenever I open the file and I created it on the workstation where i'm opening it.
I am just wondering if anyone has a code to send query results to a spreadsheet package eg. MS Excel. i) Automatically send the query result(s) to the package ii) open the spreadsheet package;
i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.
Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?
I am executing my query and writing it to an excel sheet by choosing "query" results to an excel sheet. It does not format them well. I have trimmed my fields too. There are about 10 fields in the database and I need to show them in the excel file all the ten fields adjacent to one another. Is there anyway I can format them other than the programming aspect.
Hi everyone. I have a problem with an append query runing from a small Access database and would very much appreciate some input.
The append query from Access is as follows:
INSERT INTO TransferTable ( Name, Surname, Company, Address1, Address2, [Town/City], County, PostCode, Phone, Fax, Email, [Client type] ) SELECT [Clients].[Name], [Clients].[Surname], [Clients].[Company], [Clients].[Address 1], [Clients].[Address 2], [Clients].[Town/City], [Clients].[County], [Clients].[Post Code], [Clients].[PhoneNumber], [Clients].[FaxNumber], [Clients].[Email Address], [Clients].[ClientType] FROM Clients WHERE [Name]=[Forms]![Client Form]!Name;
This works up to a point. What it does is send the selected data to the TransferTable which is a link table to a sheet in an Excel workbook.
So far so good.
Each time the query is run though, it inserts the selectd data on the line below the previous run, even if the information that was inserted last time was not saved. So on the first run the query will drop the desired information into row A2 of the TransferTable, then even if that is deleted and the changes to the workbook are not saved, the next time the query is run it goes down to row A3, then A4 and so on. no doubt this is the exact point of an append query, but it's really aggravating me.
What I'd like is a query that will get the data into the same row of the TransferTable each time it is run, since another sheet in the Excel workbook runs some calculations based on the data held in the cells of the TransferTable sheet.
I've tried the SQL editor in Excel, but my very limited knowledge has left me completely frustrated. Does anyone have any ideas how I can achieve this outcome before I go completely nuts ?
I don't mind if the query runs from Excel or Access.
I'm looking for a tool that can schedule stored procedures or queries and save the results to pdf or excel (preferrably both). SQL Server 2000, so 2005 reporting services are out, though if that has the capability I'd like to know because that might convince some of the powers that be to upgrade.
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?
I need to export the data directly using a query from sql server. This is just a temporary extract. Copy pasting the result in excel is giving mis-alignment.
All, Is there any way we can export the SQL Query result into an excel sheet, i tried the options 'results to grid, results to file' but nothing seems working.
I have an Excel 2007 file which contains values in specific cells like A23, D30 etc. I want to populate the values in these cells using SSIS packages into individual rows of an SQL table. How can this be achieved ?
Howdy. I'm trying to build a query that will take an Excel file andpull a few rows of data from a particular sheet. I'm having a problemwith my WHERE clause - I can can tell it to import WHERE a fieldmatches a value, or WHERE the field matches another value, but notboth.I've tried bunches of different variations, but can't get it to work.Is there any way to do this?Works:select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12from `RAF082604$`where ((F1 = 'Body'))Works:select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12from `RAF082604$`where F1 = 'Cash'Doesn't:select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12from `RAF082604$`where F1 = 'Body' OR F1 = 'Cash'Doesn't:select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12from `RAF082604$`where (F1 = 'Body' OR F1 = 'Cash')Doesn't:select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12from `RAF082604$`where ((F1 = 'Body') OR (F1 = 'Cash'))
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.
Im using this query to select ,calculate and format data like Refer here for more understanding:-
Select DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0) As RowDateTime, Avg(Meter1) As Meter1, Avg(Meter2) As Meter2, Avg(Meter3) As Meter3 From TableName Group By DateAdd(Hour, DateDiff(Hour, 0, RowDateTime), 0)
I want the output of the query to be written in the excel Sheet.
I am trying to create a "Loan Ledger Card" in SSRS which does the calculation up to report extracting time. I was able to create it on Excel (Click to download the Excel file) as we can calculate amounts for each installment based on Row and Column name. You can test it by putting any amount (Here it should be below 62000 as it is the total balance of loan) in "Amount Paid" yellow cell and see the effect.
I have made a selection in SQL (attached query and result in screenshot). It calculates the total paid amount by now on the first row, and all due amounts in other rows. Basically, It should first deduct the interest from first row, then principle, same process for second row and so on until it covers the whole paid amount.
SELECT '0' As RN ,memrepay.lnr ,'Repayment' AS Type ,SYSDATETIME() AS NOW ,SUM(memrepay.mprinc) As PrincPaid ,SUM(memrepay.mint) As IntPaid
[Code] .....
How can I do the similar calculation of Excel file in "SQL query" or "SSRS custom code"?
