I have a question about using FOR XML and sp_makewebtask. This (see below) is a simplified example where I have a table with 3 columns, the two first columns hold varius data and the third column (Subdata3) holds duplicated data.
My question is: Is it possible to transform the hierachy of data (see "Prefered XML output" for details) when transforming from SQL to XML using either FOR XML EXPLICIT or by using some sort of more advanced sp_makewebtask template (or some other technique perhaps)? And if, how? Any help welcome!
Cheers,
Christian
----------------------
--- sp_makewebtask ---
EXEC sp_makewebtask
@outputfile = c: empoutput.xml,
@query = 'SELECT Subdata1, Subdata2, Subdata3 FROM Data WHERE Subdata3 = X FOR XML AUTO, ELEMENTS',
@templatefile ='c: emp emplate.tpl'
I have a job that we wrote in SQL 2000 that loops through and finds all the orders pending verification and distributes them via emal to 100+ sales people every night. Each email is sent to the individual sales rep. I did this using sp_makewebtask and attaching the html file to an email and using a WHILE statement to loop thorugh all pending entries in sql.
I recently learned that sp_makewebtask is not going to be around for the next release and that I should use RSS for all future development.
Question: How can I achieve the same result outlined above using RS? How can I dynamically create and distribute HTML files?
I know that RS is xml based, but some pointer on how/where to start would be much appreciated.
In my application (developed in asp.net 1.1), I am going to implement the Cache dependency mechanism. I want to automatically invalidate the Cache object, once any manipulations done on a particular table in SQL Server.
My application and DB is running in different system. I found in one of the site, to create a trigger on the table which in tern calls the sp_makewebtask System stored procedure and write output of the query into a file.
EXEC sp_makewebtask @outputfile = 'c: estcache.txt'@query = 'select top 1 Problem_Category_Code from SMD_Problem_Category'
But the 'C: estcache.txt' is pointing the local system in which SQL is running. I want to write a file which is available in remote system. I shared a folder from the system and made a mapped drive in the SQL system. (as z: drive)
I used the statements as : @outputfile = 'Z: estcache.txt' also as @outputfile = '\10.160.1.4wc estcache.txt'
But in both the cases I got the error as: Msg 16821, Level 11, State 1, Procedure sp_makewebtask, Line 131SQL Web Assistant: Could not open the output file.
But in one site it is mentioned that, we can use the UNC name if we want to write file in remote system. How can I run the SP?
Can anyone please help me to solve this problem?
I need some swift reply on this issue, as it is very urgent for me.
using SQL 6.5 sp_makewebtask in a stored procedure I want to create a press-info file. My query calls four fields, a datum field, two varchar fields and a text field, it's the field "PrText" in my example:
EXEC sp_makewebtask @outputfile='F:EntwicklungRedaktionZeitwenden27.ht m', @query='SELECT Datum, Ort, Headline, PrText FROM news WHERE ID = 27', @templatefile='F:EntwicklungRedaktionZeitwendenTem plates.htm'
My question: Am I limited to 255 characters in this case? After 255 characters the text is cut off and I have only a truncated text fragment.
Is there a possibility to get the whole text without using @blobfmt (I don't want to use this parameter because I want to have the datum data, the varchar headline AND the text in ONE HTML-file.)
I have tried the same as dynamic query in ASP where I have no problem to get the whole content of the text field, but I want to create a static file because this information will be read often, but won't be updated frequently and I don't want to charge the server resources to much.
Has anybody an idea? Any help would be appreciated.
Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as: 10001 10003 10009 54321 89043 etc Let's say I have a couple of hundreds of them. I need to be able to see them as -
I have been requested to take a number of columns and concatenate them into one column with a specifc format/layout. For example I need to take this, (noting all columns are of different datatypes:
col_1 col_2 col_3 col_4 col_5 col_6 col_7 PK #1 John Doe 123 Elm Street Anywhere NM 55660 #2 #3 etc..
...and create this on a different table (noting col_2 is of datatype "text": col_1 col_2 PK #1 John Doe 123 Elm Street Anywhere NM 55660 #2 #3 etc..
Can this type of formatting be done? I know how to concatenate data and use the convert function, but how do you add in CR-LF to get the proper formatting??
I am new to php/MySQL and am working on an online manual. The data is divided into Title and Content. The title is easy as it is one line but the content is meant to be in a number of paragraphs.
The Content field is currently set as "blob" and the text comes out as just one paragraph. Wha can I do to get paragraphs etc into the text?
I'm looking for solutions to a formatting problem. I have two stand alone textboxes on a report body that I would like to have display side by side with very little white space between them.
Here is my problem. Simple concatenation of values into 1 textbox would work if the font size and font family were the same for both textboxes; unfortunately this isn't the case. The font size and family for each textbox is different. Is there a richtext control I can place on the report and load with preformatted rich text?
Hi, How would I convert(or format) money data type, so the output will be like: 123,456.78 or 12,345.67 (In other words how would I insert a comma which separates hundred from thousand...)
I am working with extracting data from a SAP database, and I would like to perform some 'visual formatting' on the returned data. Because the SQL code that I am writing will be used in a custom Report it is not possible for me to use '3rd Party' formatting tools. Furthermore because the database is hosted on an MS SQL 2008 R2 RDBMS it is not possible to use the IIF() function.
Here is a sample of the data that I am getting back at the moment. URL...Here is how I would like the data to be returned (with the exception that the blue lines will ideally be blank cells). URL...
I have come to believe that I should be using the ROW_NUMBER() function, along with the OVER() function and possibly the 'partition by' keywords.Here is my original code -
Code: select td.ItemCode as 'Item Code' , td.Dscription as 'Item Description', td.Quantity as 'Order Qty' , titm.OnOrder as 'PO Qty' , th.CardCode as 'BP Code', th.CardName as 'BP Name' , th.DocNum as 'Sales Order Number' , tsp.SlpName as 'Sales Person' , twhs.WhsCode as 'Whs Code' , isnull(tbloc.BINLABEL, '') as 'BIN Label', isnull(cast(tbloc.QUANTITY as nvarchar(20)), '') as 'BIN Qty'
code]...
Notice above that I only see the 'Seq' and no other data. How can I go about modifying this code so that I see all of the data columns I desire? If I further 'refine' the above, by adding case statements and the individual columns that I want to see (as opposed to using the * wildcard) I get a long list of error messages.
I need to return a single column (Name.company) but I want the data returned to be viewed in multiple columns.
First is that possible? Second if so how?
My data looks like this:
University X University X - 1 University X - 2 University X - 3 College X College X - 1 College X - 2 College X - 3 Cmty College X Cmty College X - 1 Cmty College X - 2 etc.
Okay, I know this is a very simple question, but here it is.
I have a select statement that returns a value such as: 9902.7400000000.
In the select statement it just selecting Value. In the select statement, can I format the output to be with just two decimal places? I know I should know the answer to this and how to do it, but thank you for any help.
I have two questions : 1) Linking SQL Server Databases 2) Data Formatting like InputMask
1) I want to know if there is a way to link two seperate SQL server databases (it's tables) within the same server (local server). I created one Global database - with clients, etc. tables. The reason I did it like this is that I have to handle three seperate invoices databases, but using the same clients, etc. tables to get their information from. I used MS Access like this, linking Clients, etc tables from the Clients Database with the three other Databases. It worked fine but now I need some new features like setting the InvoiceNumber to something different, eg . 04/0001 for this year and then 05/0001 for next year. My Access databases can't change this numbers - it's just a autonumber I used. I know some Visual Basic code can make a change like perhaps adding a new column like InvYear and then adding it together with InvoiceNumber to get the Invoice Number, but I want to change it to SQL Server because I want to learn how SQL Server works. I am just starting out and not doing courses.
2) The other thing is I want to know if there is some feature in SQL Server to make data input easier, like a InputMask in Access where you can let a text column be formatted as you want. Eg. for a telephone number, it can store it in the format you made; like (011) 123 4567, not like you typed it : 0111234567.
I need to export some Database data into a text file. My Query looks like this:
SELECT Category1, Category2, Category3 FROM dbo.tbl1 WHERE Category1 = 'JP-4' AND Category2> 4;
This works fine to get the data, however there is some html formatting in the table entries such as
`<p>,</p>, ,</br>` etc.
So ideally I need to remove those when exporting the data to the text file. I've tried to do it with a simple replace query but that didn't work. I've also got an issue with line splits and would need to remove the ( ).
The Data format is something like this:
Category1: JP-4 Category2: 4 Category3:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p> <p>amet, consectetur, adipisci velit</p> Category4:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p>
I got it to work like this with the replace function:
SELECT REPLACE(REPLACE("PHOTOGRAPHS",'<p>',''),'</p>','') FROM dbo.khia_tbl WHERE Category1= 'JP-4' AND Category2> 4;
But the issue is that I've got 15 columns in total and that I need to do it for several different tags for each column so , </br>,
as well as "" and different spaces so that would be a lot and I thought there must be a better/more efficient way of doing it...
'17686568 - Bill Statement - 11/16/2006 - Stm. Date - 10/27/2006'
Above is the data string that I am trying to pull the information from. Here is the function I'm currently using:
cast(substring(c.itemname,charindex('Bill Statement - ',c.itemname)+18,10) as varchar)
...which gives me what i want most of the time, but beacuse the date is not equally formatted through out the database and the date can look like 5/4/2012 and using the above formula it will show up as 5/4/2012 -
So my question is how can i trim off the dash part when the data shows up like 5/4/2012 - ?
I have a report that has ten pages (essentially ten different reports). Each page has one, large main chart and then three smaller charts stacked on top of each other off to the right. The layout is in landscape. When I render the report in Reporting Services, the layout looks fine. If I export it to Adobe, it is also fine. However, when the report is emailed as a PDF attachment, the main chart on each page is completely missing. Has anybody experienced something simliar? I was having issues with the layout, and decreasing the height of each page fixed everything, but created this new problem. I am using Adobe 7.0. Thank you.
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query: SELECT  DISTINCT  dbo.sectn_dept.sectn_sc AS Customer, MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month, dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year, dbo.incident.incident_ref PM_ref, dbo.product.product_n "Product",
[Code] .... Â Â Result: Need to Display [Total Service Time] in below Format:
Hello all,I have a strange problem that i need some advice on. I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCIt returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecordsWHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'ORDER BY FILENO DESCDoes anyone have an idea how to solve this? Thanks.Richard M.
I'm working as a consultant for a project and I have been having constant issues with the in house "database guru" about stored procedure formating.
He insists I use a format similar to this:
create procedure getUser @UserId int = NULL as select UserName , UserPassword , UserRole from Users join UserRole on User.ID = UserRole.ID where User.ID = @UserID and User.Active = 1
where as I prefer this format:
create procedure getUser ( @UserId int = NULL ) as select UserName, UserPassword, UserRole from Users join UserRole on User.ID = UserRole.ID where User.ID = @UserID and User.Active = 1
Now normally I don't argue about style issues but I find his format confusing and it bothers me that it senselesly wastes lines make the stored procedure longer than necessary.
I'd love to get some comments on this issue or reference to any discussions on issues such as this.
Hello - Does anyone know why SQL Server Reporting Services formatts SQL weird? -- If I type a SQL statement in notepad or notepad++ and then paste it in Reporting Services it will change the whole structure and "expand" it out to three or four times its size. Thanks Adam
I need to show my SUM of the 2 columns added in the query below formatted as currency. Is this possible? SELECT SUM(QVSTDN + QVNONC) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV I tried: SELECT CONVERT(varchar(12), SUM(QVSTDN + QVNONC) , 1) AS Total FROM INVOICE_TBL WHERE QVORDN = @QVORDN AND QVINV = @QVINV But this does not format it as currency. Any input would be helpful.
I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005. How could I write a sql statment to put make this show up as 07/29/05?
I am trying to output data from SQL Server to an email body message with a little formatting (just to make it legible) Using this SQL statement: select top 1 'Name', app_first_name+' '+app_last_name [name] ,'Submission Date', submission_date ,'Address', app_address+', '+app_city+', '+app_state+' '+app_zip from vw_bpa_export where app_email = dave@email.com order by submission_date desc the resulting output displays this in the email body "Name","Brian Vogler","Submission Date","2008-01-30 07:45:00","Address","6285 Harrow Trace, Norcross, GA 30092 " but what I want is the text to look more like this: Name Brian VoglerSubmission Date 2008-01-30 07:45:00Address 6285 Harrow Trace, Norcross, GA 30092 Is there a way to do this in the SQL statement (without HTML tags)?
In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"' Isn't this a standard VB function and why does it produce an error. How do I fix? Thanks
I have values in my database that I'd like to display as $xxx,xxx.xx. Some of these values use the money datatype and others use float.
I'm building a string in a stored procedure that is eventually passed to Visual Basic. I want to format these values in the above format in the string that is passed. I've searched all through MSN and I couldn't find anything related to VB's FORMAT function in SQL Server.
Does Transact-SQL have any functions that could format these values for me?
I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this
select distinct(companyname),max(calldate) from tablename
what I want to see is the latest call made to a customer for each company but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?
