SQL DTS Query To Excel
Sep 19, 2005
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.
View 1 Replies
ADVERTISEMENT
Nov 11, 2007
Can someone please help?
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).
For example:
qDate qTicker qOp qHi qLo qCl qVo
08/11/2006 AAT.L 98 100 98 99 2361001
09/11/2006 AAT.L 98 98 98 98 378001
10/11/2006 AAT.L 99 99 97 97 66001
08/11/2006 VOD.L 198 100 198 199 2361020
09/11/2006 VOD.L 198 198 198 198 378020
10/11/2006 VOD.L 199 199 197 197 66010
08/11/2006 ARM.L 298 200 298 299 1236100
09/11/2006 ARM.L 298 298 298 298 317800
10/11/2006 ARM.L 299 299 297 297 66100
but instead it displays the last 6 months data for every stock to the point where it is unable to display anymore records in Excel.
Does anyone have any SQL code to resolve this?
Thanks!
View 3 Replies
View Related
Feb 6, 2008
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.
Thanks!
View 1 Replies
View Related
Mar 11, 2008
Hi all,
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
Section Supervisor TargetVal
Section1 sup1 10
Section1 sup2 11
Section2 sup1 8
Section3 sup1 5
Section3 sup2 2
What i want to get out ouf this is something along the lines of the following:
sup1 sup2
Section1 10 11
Section2 8
Section3 5 2
Could somebody give me any suggestions on how to do this; i had thought the PIVOT operator may work but i'm not sure how to use it in this case.
Any help on this would be appreciated.
Thanks.
View 2 Replies
View Related
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
View 1 Replies
View Related
Mar 21, 2008
Hi,
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.
Thanks in advance.
View 1 Replies
View Related
May 10, 2007
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?
View 3 Replies
View Related
Mar 2, 2003
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!
Please, please help me!!
TIA
tobyjuggler
View 4 Replies
View Related
Jul 20, 2004
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 '
exec master..xp_cmdshell @x
go
View 7 Replies
View Related
Apr 6, 2006
select * from employees
I want to export result set into excel sheet by writing a query, can i do that? any one can write a query for me
View 2 Replies
View Related
Mar 1, 2007
Hi everyone,
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: ].
I'm thanking any help that you can give.
Thanks,
Oscar.
View 7 Replies
View Related
Nov 17, 1998
Need to convert a sql query recordset to an ASCII tab deliminated to import into an excel spreadsheet.
Are there any work arounds for this.
Jah Red
jahred@globalmusic.com
View 1 Replies
View Related
Feb 20, 2015
I have been trying to find a way to export the results of a query to a csv for use in excel but cannot.
View 2 Replies
View Related
Feb 27, 2006
Hi,
I want to send the result of my query to an Excel file.How can I do it?
View 3 Replies
View Related
Jun 29, 2007
hi guys,
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?
Thank you very much.
View 5 Replies
View Related
Mar 8, 2006
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.
View 4 Replies
View Related
Dec 8, 2006
Hello,
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?
Thank you for your help!
cdun2
View 1 Replies
View Related
Jun 25, 2006
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.
Thank you for any input or suggestions.
View 4 Replies
View Related
Oct 27, 2007
Hi all,
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;
Thanks.
View 1 Replies
View Related
Nov 23, 2006
Hello everyone,
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?
thanks a lot.
View 4 Replies
View Related
Oct 2, 2007
Hello there,
how do i write a select query where i can pass parameter.for example
select * from employee where id = @empID
does not work when i use excel as my data source and type is OLE DB
View 1 Replies
View Related
Feb 5, 2004
Hi ,
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.
Thanks
View 1 Replies
View Related
Feb 27, 2003
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.
Thanks in advance to anyone who offers any input.
View 1 Replies
View Related
May 2, 2007
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.
View 4 Replies
View Related
Nov 16, 2013
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?
View 9 Replies
View Related
Mar 4, 2015
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.
View 1 Replies
View Related
Jun 12, 2008
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.
View 2 Replies
View Related
May 1, 2007
Hi,
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 ?
Cheers
Sam
View 1 Replies
View Related
Mar 11, 2008
hi all
how can i put select query result in excel sheet.
can any one help me
Regards
js.reddy
View 2 Replies
View Related
Jul 20, 2005
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'))
View 1 Replies
View Related
Aug 2, 2006
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.
View 4 Replies
View Related
Aug 2, 2006
Hi
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.
Your help will be highly appreciated.
View 13 Replies
View Related
Nov 4, 2015
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"?
View 2 Replies
View Related
