Hey all,
I think I am asking the impossible but hey I`ll ask anyway! I`m trying to find a way to dump the results of a sp when it`s run. After searching archives reading up in bol and then some, the closest I got was to use cmdexec to run isql /Usa /P /Q "exec sp_myproc" /i "myprocrslts.txt".
Now this is the outcome I want except I have to schedule this to run and I need it to run any time the sp gets called. How can I modify the sp to dump straight to file or trigger the task?
TIA,
Simon Mackey
Database Devlpr
mailto:s.mackey@bne.catholic.edu.au
When I send my query results to a file in SQL Server Management Studio, how come I'm seeing the following in Notepad++? FH TEST "FH" which I thought should be in a CHAR(2) data column is there but "TEST" seems to start in Column 6...not column 3 as I would have expected. I was expecting... FHTEST.
Is there any automated way to direct the results from a query in Query Manager to a file. I know I can select it from the Query drop down menu. For those familiar with Oracle, I am looking to do the equilavent to "Spool <fn>"
Im running a query using isql from the command line and outputting the results to a file. Ideally I'd like to have the file in csv format so that it can be used by a client to import the results into their system.
I can run a query and output the results to a file but get i dont get the csv format I require. I can separate the columns using /s parameter but I also additional space within the column fields.
I know it is possible to create the csv format using the SQL query tool and ISQL_w but I was wondering if there was some parameter that I can call that will format the output like the format options in ISQL_w allows me to do.
Is there a way in SQL Server 2000 to output your query results to a file (example comma-delimited) in the SQL CODE ITSELF? I know you can check the option under Tools->Options->Results but I want to have it do it in the SQL code. Thanks Eric
I want to do a SQL sentence that saves the results from the select in a .txt file. I get the results as a long list on the screen, and it repeats the headers several times, so it takes quite some time to copy the results out of there. It would be better if the result was put as a list directly in to a text file.
I am trying to bcp out a views result into a text file. And the records in the text file should be comma seperated and each record should be enclosed in quotations. For tab delimited you use in the bcp.Can anyone help what needs to be used in the bcp to get the results in the txt file as specified
I have a query (select * from table1) and I'd like to have the results of this query sent to a text file instead of the results windows when I run it from Query Analyzer.
I want to write a small program (maybe just a stored procedure) that will save the results and column names of a simple query to CSV file. This will then be copied onto disk and distributed to user who'll use Excel or a similar package to open the CSV file.
Any suggestions as to the best way to achieve this? I was considering using a view and then BCP but I have problems then when trying to open the CSV file in excel (or other spreadsheet package). Doesn't like the datatypes and so on.
Is there a simple way to do this? And is it possible to save the results in such a way that Excel will choose the right datatypes for the columns. (not convert varchar's like '000122' to numbers.)
select *,substring(stafflog,15,11) as test into #t1 from dbo.Customers where stafflog like '%armagh%' go select left(stafflog,4) as Staff,count(left(stafflog,4)) as Total from #t1 where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime) group by left(stafflog,4) go select Title,Address1,Address2,Town,County,Postcode,TelephoneDay,TelephoneWork,TelephoneEvening, MobileTelephoneNo,Contact,Mail,Telephone,Terms,StaffLog from #t1 where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime) go drop table #t1 go
i need this query to be scheduled to run at a certain time every day and the results to be put in a text file.
is there an easy way to do this or what should i be looking at doing to get it to work
After running a query (from the Query Builder) in SQL Server 2008 sometimes I can right-click on the results pane and "Save results as CSV file", other times it's not an option. After running a query for 24 hours (several million record results) I can't seem to do anything with the results. I have my settings:
Options | Query results | SQL Server | Default Destination for Results
set to "Results to File" and a path entered, but it doesn't work. Is there wording I can add to the end of my SQL statement such as "TO FILE xxx.csv" or something?
When exporting the results of a query to a file any values that are null are displayed as NULL in the file. Is there anyway to have them be blank instead? I looked around in the Studio but couldn't find anything that would do this globaly.
is there a way to print query results from mgt studio into a file with headings and formatting? When I "save as a" file, export file, spreadsheet, the results dont have headings and semi decent formatting.
I have written a query that I need to save results of. Is there some statement that I can place at the top of my query (like we can do in Oracle through spool command) to specify the directoryfilename to save query results to? Eventually I need to schedule this to run daily via a DTS package.
Please let me know if you've come across this before?
so i wrote this script and its pretty sweet (tara helped me before on this one) but now, for some STRANGE reason and its been reported by users the following:
the script for example dumps "first name, last name"
when we save the results as a CSV it looks good in notepad...
"first name, last name"
but we kept getting some kind of stange issues when we tried to dump that CSV data into another program to use... so i opened the CSV file in command prompt to see what it really looks like and it is actually "f i r s t n a m e , l a s t n a m e" with all these unnecessary spaces! why is it saving the CSV with spaces in it when my script is nothing of the sort? it was working fine last week, no changes were made to the script but now the CSV's are rendering those weird spaces.
I have a stored procedure that outputs an XML result. As it is right now, I will periodically go into SQL Server Mngmnt Studio and run the procedure, then save the XML output to an XML file that my website uses. The procedure takes too long run (it is recursive through the entire data set) to have the website call the procedure on its own, and wait for the results.
My question is this: Can I create a trigger that will run this stored procedure and save the results to a file? Is that possible? The trigger would call the procedure any time a specific table is updated (which will be a rare occurrence), then save the file to the path provided so the website users could always have the most up-to-date information without having long wait times caused by long waits for the procedure to run.
Any advice on how best to accomplish this will be appreciated. Thanks in advance.
I have created a DTS package which transfers data from a SQL 2000server table to a text file. Each time the package is executed thedata in the text file is replace with the new data from the currentexecution of the package. Can DTS append new text to a text file andnot replace it each time the package executes. Is there an option orseeting for this? I havent seen one. Can anyone help me out on this.Thank you,Brett
I have a dynamic database that will be periodically queried to selectthe data from a blob field. This blob data field is text of a variablelength. The data will be selected using an id field and a date range.There will be multiple blob fields returned that I would like to outputinto a txt file in a local folder.I have the blob fields showing up as text in the field and not areferring link. Can someone point me to an output to text solution?Thanks
I have posted this on previous forums, but the below code is a VB.net CLR stored proc that will accepts two parameters, a SELECT.... FOR XML query and a file path, and saves the resulting xml result into a file specified by the parameter. After compiling the VB code into a .dll than the T-SQL code to import the assembly, create the CLR proc and test it is included underneath. Hope some will find this useful
Code Snippet 'Requires the Trial or Release version of Visual Studio .NET 2005 Professional (or greater). Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Xml
Partial Public Class outputxml <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString) 'Create the variables to hold the values that are supplied by the parameters 'input by the stored procedure Dim xmlDoc As New XmlDocument() Dim output As SqlPipe = SqlContext.Pipe() Try 'Load the result set into the XmlDoc Variable and then save the results in the 'path provided by the stored procedure. The values are provided by the 'input parameters of the stored procedure xmlDoc.LoadXml(XmlData.Value) xmlDoc.Save(Filename.Value) Catch ex As Exception 'If an error occurs catch the message and pipe it back to SQL output.Send(ex.Message.ToString) End Try End Sub End Class
T-SQL CODE BEGINS HERE
/*Alter the database to set trustworthy on in order
to allow the ability to set the assembly to external
Hello, I have a DTS package that copies data from a fixed width file and inserts it into a SQL Server 2000 table. These files are generated daily, and usually contain about 200k records.
Sometimes, the format of the file between two days can differ slightly. I can't tell if the problem lies with the length of the record string, or the string terminator. The record string is supposed to begin with 'D1'. One file (as viewed in Notepad) will contain the records each in its own row, and each row will start with 'D1'. Another file will show that the first record starts with 'D1', but then the next record does not begin on a new line. Instead the next record continues at the end of the previous record. In this case, 'D1' is preceded with an unrecognized character as if it to indicate a carriage return.
Anyway, in SSIS, when I configure the row width of the fixed width file in the flat file connection manager to 386, and the beginning of all the records are all on their own row, the data processes without a problem. When the records do not all begin on their own row, the data will process fine if I change the row width to 385.
I don't have to compensate for this in DTS. Between SSIS and DTS, both flat file connections are configured with a row delimiter of LF, and no text qualifier. Why will SSIS and DTS process these files differently?
Hello, We have some queries that are long and intensive. We have thought about running the queries and storing the data in a text file for lookup from our website.
Example: Our online store only displays items that are in stock so when a user selects a category a query runs and grabs only items that are in stock and then displays them. There could be thousands of items the query needs to sort through before displaying the items that are in stock. What if we ran this query once every hours an stored the results in a txt file? The asp page would then go to the text file to grab the results instead of having to run the query every time a user selects a category. Will this speed up the site by not having to query every time? Would this be a correct way to eliminate queries that run thousands of times a day?
