I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y' ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10)) END AS 'System Verified'
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Table2HTML] ( @TABLENAME NVARCHAR(500), @OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98 Unclosed quotation mark after the character string '</table></body><'. Msg 102, Level 15, State 1, Line 98 Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
I have created SSRS report which has many overlapping objects, the output in PDF format seems to good but in word format it is not giving the required output.
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
I would like to get the next recent Event Based on the current date time, It should be like below format...Suppose today is 2014-12-17 and Current Time is 07:30 AM The expected result should be
NextEvent ------------ Upcomming Event : Event 4 - This Morning 10:45 AM
Specifically i am looking for a Format Like Below
If we have the event by TODAY, The result should be in below format This Morning (12:00 AM to 12:00 PM) This Afternoon (12:00 PM to 4:00 PM) This Evening (4:00 PM to 7:00 PM) Tonight (7:00 PM to 11:59 PM)
I am required to send an XML file of our clients to head office in Belgium for comparison against a database of known undesirables. The data is in a legacy system with a custom database so I have created an SSIS package that extracts the tables I need into SQL Server and have developed a program that reads from a text source and creates the XML then Secure FTPs it to Hong Kong who will handle it from there.
My problem lies in actually extracting enough data to avoid too many false positives. The scanning will check name, identity (passport number, etc.), town/city and country. We don't hold an identity number and the town/city and country are buried in free format fields. A quick analysis of the 419,000 records shows that the spelling is terribly unreliable, too. In most cases country has not been entered because the clients are local and even when they are overseas, sometimes only the city has been entered. That is often misspelt, too e.g. Kuala Lumpar or Melboure.
The addresses are held in 3 equal length fields called Address_1, Address_2 and Address_3. There's no guarantee that I will find the town/city or country in any particular one of these fields. In some cases, the street number and name are in Address_3 because the first two hold a company name and a C/O line.
So I'm not going to fret over the ones where the address information is nonsense or missing but I would like to try and extract valid country names and town/city names, where present and this is where I get stuck. I'm from a COBOL programming background and although I'm loving getting used to the power of SQL, I'm still a bit stumped when I come across a problem like this probably because I keep thinking of the solution in procedural terms.
I have a feeling that the solution will be to create two separate reference tables, one of towns/cities and the other of countries. I would then somehow search the 3 fields looking for those keywords and if found, entering them in the appropriate part of the output text file to represent town/city and/or country. I did also think about destringing to find the separate words but that doesn't help where the name consists of two words such as NEW ZEALAND.
I would love to hear from anyone who has dealt with a similar problem and has a neat solution to this using SQL.
I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.
SELECT DISTINCT CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END + CONVERT(CHAR(1), '') + (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber)) + CONVERT(CHAR(1), '') + (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table: Customer, Type, Amount Customer1, Revenue, -100 Customer2, Cost, 60 Customer1, Revenue, -200 Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group): Revenue Cost Total Customer1 -100 60 -40 Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ): Revenue Cost Total Customer1 100 60 40 Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
I am downloading a webpage as a text file in order to read a specific string to assign it as a variable/parameter in order to create an output file name. I would like to know how would I be able to look for a specific string and output as another variable for the rest of the package.
2015 Conforming Loan Limits ------------------------------------------------------------------------ o _Loan Limits for Calendar Year 2015--All Counties _[XLS] </DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL_FLAT.xlsx>_ , _[PDF] </DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL.pdf>_ o _List of 46 Counties with Increases in Loan Limits for 2015
[Code] ...
To explain it a more better way, I have a sample webpage text here. I should be searching for "FullCountyLoanLimitList" appended by the current year (like FullCountyLoanLimitList2015) and copy the entire file name in the text file and assign it to another variable so that I can download that specific file using WebClient connection.
I want to create a txt file from a table i have. I have the data in the correct formats but i want to include some padding around my selected four columns.
I am trying to format the result output from my query so that it looks like currency. Does anyone know how to do this? Here is my very simple select statement: SELECT Field_Value FROM tbl_all_results
I was thinking some kind of cast/convert would do the trick but I can't seem to get anything to work.
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 have the following Query which gives the output in XML format. select * from <Table_Name> For XML auto I wanted to store this output in a .Xml file at my local machine. I would prefer to have a proc when called generates the xml and stores it on the local m/c. Any ideas ? Thanks,
I manually build my url for my reports. In the url I build, I can specify the output format I would like it to automatically use when the report opens. However, it always first opens the dialog box to either Save, Open, or Cancel. Is there anyway to bypass this and just open it in the format I have coded in the url? Thanks.
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)
Hi All,Currently the query returns 2006-03-27 00:00:00, can I make it output03/27/2006, I want to truncate the time, and replace the hyphens withforward slashes. Any ideas?Thanks In Advance,~CK
I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.
The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?> <FUNDS> <AMRGX> <NAME>AMERICAN GROWTH D</NAME> </AMRGX> <AHERX> <NAME>AMERICAN HERITAGE FUND</NAME> </AHERX> <AMRVX> <NAME>AMERICAN INVESTORS GROWTH FUND</NAME> </AMRVX> . . . </FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)
The closest I have been able to get so far is: <FUNDS> <SYMBOL>AMRGX</SYMBOL> <NAME>AMERICAN GROWTH D</NAME> </FUNDS> <FUNDS> <SYMBOL>AHERX</SYMBOL> <NAME>AMERICAN HERITAGE FUND</NAME> </FUNDS> . . .As you can see (hopefully) I am able to get the data I need but cannot get: (1) the FUNDS tag(s) to be the very highest level/root. nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.
Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?
I want my SYMBOL tag to be the actual SYMBOL for the stock fund.
Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.
Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!
A while back I wrote a .NET assembly with a COM wrapper to provide an interface between Report Services and legacy MS Access applications. Basically, it returns the byte arrays from the .render method (output format emf) to be written into an Access image control.
This worked flawlessly on any 8.5"x11" RS report we threw at it on the first two Report Services I tried it against. When we set up a third Report Service at another facility, the same reports were coming out with the physical dimension metrics on the emf as10"x13", so the Access version of the report is clipped significantly on the edges.
I trapped the output into a System.Drawing.Image object in the .NET assembly and put a watch on it. The two servers that work as I expected show HorizontalResolution and VerticalResolution as 96.0946, while the one that doesn't has these values as 81.2799. The PhysicalDimension property shows 21542.3672 x 27939.4316 on the working servers and 25531.25 x 33031.25 Otherwise, the properties of the images seem to be identical. The PhysicalDimension values I see are completely consistent with the size units of 0.01 mm, as the the emf documentation indicates. The resolution of 96 is consistent with the "Windows default" values of 96 dpi. The ratio of the resolutions is correct for an 85% scaling factor for the "misbehaving" Report Service.
I have not been able to find any reason for the differences between the Report Services, and I have not been able to figure out how to control it. I tried setting DpiX and DpiY as 96 in the DeviceInfo for the render call with no change in the behavior of either server.
Can anybody enlighten me on what is happening and what I need to do to get consistent behavior between the servers? Or why the DpiX and DpiY settings for the emf rendering extension don't seem to work?
SELECT membername, outputval case when choice = 0 then outputval else null end as outputval from MyDatabase group by membername, outputval
how to format outputval: if outputval < 40000 format outputval as: 5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc. if outputval >= 40000 format it as a scientific.