Automating A Query And The Saving Saving Of Subsequent Results
Dec 13, 2007
Hi Guys,
I am trying to automate a basic task using SQL Server 2005 Express.
Currently I have a query script that I run and then save the results as a CSV file. I need to do this on a daily basis and so I am looking to find out how best to go about this. There are a multitude of third party tools that claim to be able to do this - can anyone recommend this or enlighten me of the best way to set up this automation.
I'm trying to do something which I hope can be accomplished relatively simply.
I have a report similar to bank statements let's say. When run, it currently prints out each person's statement into one file, with page breaks sepearating each person's statement. What I need to do, is when the report is run, save each person's report into a seperate file for the purpose of emailing to them later.
I could easily modify my report to just output for one particular person, but I'm not sure if there's a way to "bulk render" all the reports and have them saved to sepearate files.
I should also add that I'm using an MS Access Data Project (ADP) as the front end to my app - connected to a SQL Server 2005 DB. I currently display the reports by embedding a web browser object into an Access form and rendering the report via HTML.
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've built a very strong form engine for one of our web apps. It's more featuristic that the TFS Work ITem Tracking.. and more complicated. In order to get the complexity It requires over 50 tables to support the 20+ fields I've created for the users.
The problem I have now is doing REports and Filtering on these tables.
Like TFS, it has querying... but I have a problem with storing the query. I have two columns right now:
ItemQuery TaskQuery.
The TaskQuery unions the task query and the ItemQuery and I can do sorting etc. on that. Without any constraints on the filter (say they jsut want to select column headers) the query can exceed 10,000 characters.
If someone wanted to have a constraint: I want to check if the CascadingDropDown "Strategic Plan" was ever "Maximize Potential" (or some other management term like that). That constraint sub-query is 500 characters.
Meaning, if someone put 20 constraints on a filter like that, I already have 10,000 characters.
Average Query without constraints: -I have my ItemQuery which contains the "columns" Query: 6,000 characters. -I have my TaskQuery which contains the "Columns" Query: 6,000 characters.
Which turns into:
SET @ItemQuery = (SELECT ...) SET @TaskQuery = (SELECT ...) EXEC(@ItemQuery + ' ' + @TaskQuery)
But now that I add constraints to those, I'm hooped.
I can create another column: ItemConstraint and TaskConstraint... but what if users create massive constraints and the query exceeds 8,000 characters, the Max a VARCHAR can hold...
I can't use TEXT as you can't create local variables of type TEXT in a stored procedure (DECLARE @Sample TEXT)... so I'm forced to use VARCHAR(8000).
I would like to save a query plan (estimated or actual)created in Query Analyzer -- paste it into a document,or simply print. It doesn't seem to be possible toselect and copy the Execution Plan window, and printingit creates microscopic gibberish which is a waste ofpaper. Is it possible to do this?Set showplan_text is of limited help for the SP I'mlooking at -- while analyzing the SP, it reads aheadand complains that a temp table created inside the SPdoesn't exist (yet) and exits. Using Ctrl-K to capturethe query plan allows the SP to complete, but saving theplan is the problem.Thanks,Jim Geissman
Can u help me out in the following two scenario/problems.
1. I want to save the query results (which is generated within execute sql task) to file (which does not exists & should be created at run time). is it possible?
2. Can I define the file name, in Flat File connection Manager, dynamically (by using some variable)?
I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
I have a UDF --------------------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UDF_AlphaNumeric]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[UDF_AlphaNumeric] GO
CREATE function UDF_AlphaNumeric (@string nvarchar(max)) returns nvarchar(max) AS -- select dbo.UDF_AlphaNumeric('a[]#b`c,;"1$%^2"£!3') begin while @@rowcount > 0 select @string = replace(@string, substring(@string, patindex('%[^0-9a-zA-Z]%', @string), 1), '') where patindex('%[^0-9a-zA-Z]%', @string) <> 0
if @string = '' select @string = null
return @string end
---------------------
After creating the function I open a new query window, put in the below two calls to the function, and run them
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
Please i need to save records using DataSet. I created the connectionstring as a global variable. In th button_Click event i have this : Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim InsAdapter As New SqlDataAdapter Dim StrInsert As String = "insert into Products(CategoryId,ProductName,ProductCode,ProductPrice,) values(@CategoryId,@ProductName,@ProductCode,@ProductPrice)" Dim ObjComm As New SqlCommand(StrInsert, ObjConn) ObjComm.Parameters.Add("@CategoryId", SqlDbType.Int).Value = drpCategory.SelectedValue ObjComm.Parameters.Add("@ProductName", SqlDbType.NVarChar, 30).Value = txtName.Text ObjComm.Parameters.Add("@ProductCode", SqlDbType.NVarChar, 10).Value = txtCode.Text ObjComm.Parameters.Add("@ProductPrice", SqlDbType.Int).Value = txtPrice.Text InsAdapter.InsertCommand = ObjComm End Sub But on clicking the button, the record is never saved. Please help
I am currently migrating my application from Access to SQL. It looks like it's working good but one thing I cannot understand is why when I have created a varchar(40) field and after I insert a field that is less then 40 characters long, the rest of the string is filled with the leading spaces. I have also create an nchar(40) that did not solve it ether. For example: the text is "Hello World". The text is 11 characters long but the data saved to the table is: "Hello Wolrd" + 29 spaces. Am I doing something wrong?Thanks for your help in advance.
When I open a DTS package, save it as a bas file, make changes, close the package and save it when prompted, where are my changes saved?
The answer appears to be nowhere. When I reopen the package it's back to it's original setup. When I check the bas file it's as it was when I originally saved it.
We have a table with 3.5 million records in it. We have been forced to add two fields and set indexes on those fields. But when we try to save the changes it times out. When we try to generate a change script it also times out. Is this a memeory issue? Any ideas on whats causing this or how to fix it woudl be greatly appriciated.
Hi, all.This might be a silly question... but I am very new to programming inSQL so please bear with me :)So. I'm using MS SQL Server 2005 Management Studio Express. I have atable that was created via an existing .sql file. Included were abunch of stored procedures. I went in to re-format these procedures.(They were written in haste by another programmer. It is my task to goback and improve their readabilities.) However, I am having difficultyfiguring out how to save the changes to these stored procedures. Thusfar, I have been individually saving each stored procedure in itsown .sql file. This is obviously not the way to go... but it's atemporary solution (so as not to lose the changes I have made todozens of procedures).Can someone please explain to me how I commit these changes to thedatabase?Thanks,Allie
Using vb 2005 express I have my grid veiw on the form and a icon to click to save my work.
But is there a passage of code I can write to pop up a message box to remind me to save my work when I close my form. Similar to ms office forms when you close they ask you if you want to save any changes.
Is it better to have users that upload pictures have them saved to SQL server or to a folder? I have a site where users enter things that they own (and each item has a unique id) and now I would like to add the functionality to add a picture. I am sure I should use the fileUpload control, but should this save to a folder created for each user or to the SQL record itself? Or possibly a separate SQL table just for pictures?Thanks for the help.
Hi, I want to save date into SQL server pls help me how to save this date into server. its give me syntax error that converting datetime to character string. and also I want to save date only in mm/dd/yy format in a sql not including time. The following code I am using sub SaveEvent(sender as object, e as EventArgs) Dim dt As DateTime = DateTime.Parse(eventdate.Text) lbl.text=dt dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ") dim cmd as new SQLCommand("insert into events values('" & dt & "','" & desc.text & "')",con) con.open() cmd.executenonquery() con.close() end sub
Hello Im having a little problem getting some values from a database..... What I need is to get the value of the keyfield of the last entry introduced, I know how to to do the SQL statement for this, the problem is that i dont know how to save this value in a variable. I need to save this value in a variable in the session object or in a hidden field, and then use this variable in another different page. That's way i need to save this ID value, later in the other page I introduce data in a different table and one of the fields has to be this ID value which I have as a foreign key in the table so I need to use the same value in all the rows i introduce(Id value). If you know how to accomplish this let me know, many thanks, Sergio
In my system, I generate invoices as PDF for my clients and I need to email them and save them on the database. What is the best approach for achieving that?!
Shall I save the files path in the SQL server 2000 as ( varchar ) and save files in a folder in the server or there is another approach?! is there any recommendation or advice about the procedure to send emails shall i save them first then send them or ...!??
Hi All 2 post in a row first time in ages so i must be getting better but i get stuck on crappy little things like this hopefully I have data in the following format 4.1399999999999997 4.2400000000000002 5.4800000000000004 5.1799999999999997 6.7699999999999996 i want to select the data as such 4.13 4.24 5.48 5.17 6.76 I keep chasing my tale on this one around and around any one got a simple idea i am missing, you would not believe how much time i have spent on this. Cheers Phil
database file is not full and not limited to growth. As a matter of fact batch transactions are done but our proble is very slow. 28 records has been successfully save after 45 mins. Before it's just 5 minutes. Then some other process are not working. Is there anything that I must do? We have 460 tables in our software using SQL server 2000 and our application is using VB 6.0
Im not really sure if this is the right thing to do. But i want to save a copy of the html from my invoice to sql so that i can keep a history of the invoices in case their are changes done to them. Anybody know what would be the best way to do this?
Hello,I use SQL Server 2005 Express and I would liketo save on db of mine...Have I only to save the .mdf and .ldf data fileor I need to stop some service or other programs?Can I use some utility to schedule this?ThanksM.
If you use the command UPDATE <tablename> SET <fieldname> = ' ... ' toupdate a CHAR or TEXT field there are some characters that can causeproblems such as a single quote. Are there any other type of charaters likethat?TIA
i created two tables. I placed both tables in a single form. When i run the application and enter some data in both table only data entered in first table is saved when i press the save button. As such when i run the application again and browse through the records of the first tables i cannot see the respective data in the second table though there is a relationship between both tables.
My install of SQL Server 2005 runs like a dog. The SQL Management Studio splash screen lasts for 10 minutes, then when the Studio window appears it takes another 5 minutes to initialize.
I have SQL server installed on a Germane Server running Windows Server 2K, SP4. It has quad Xeon 2.8 GHz processors with 2GB RAM. 4 300GB drives in a RAID 5 configuration.
I have created 2 databases which I would like to save, uninstall SQL, then reinstall it. (I didn't install it the first time.)
SAVING PROBLEM Now i know that my database is really saving but it is being saved on the copy database in bin folder of the project folder. But i want it to be saved on databse of the project folder. Everytime I execute the program, the database on the project folder copies itself to the bin folder which overwrites the updated database.
I agree with Antoine (Software Design Engineer, Visual Studio Data Design-time) that SSE database has 2 copies (http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx). He or she suggested to set the property of the databse to "Copy Never", on my SSE it's "Do not copy". i cant find "Copy Never", i supposed its the equivalent. When i executed the program i got an error "An attempt to attach an auto-named database for file C:[app_path]addressbook.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share". I tried her/his 2nd suggestion, leave the data file outside the project and create a connection to it in Database Explorer, but I got the same error message.
Someone from other website suggested to delete the SQLExpress folder in "c:Documents and Settings[user name]Local SettingsApplication DataMicrosoftMicrosoft SQL Server DataSQLEXPRESS" but this didnt solve the issue.
I just started using Visual Studio 2005. How do you actually save your queries to use again? Also, is there a way to export results in a user friendly format?
Ok. So I have this ASP.NET page and I've programmatically taken a report from the report server and rendered it in PDF. Now I would like to take this a step further and save the report as a pdf document on the local machine.
So at this point I have a byte array representing the document, now how would I save this as a pdf on the local machine? I'm unaware of an ASP Response method to allow this and I'm unaware of a SSRS ReportingService method, but as I said I'm unaware...
