SQL Server 2012 :: Converting Query Output To HTML Format
Mar 23, 2015
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.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
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.
View 6 Replies
ADVERTISEMENT
Mar 16, 2014
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
View 2 Replies
View Related
Feb 28, 2014
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'
View 3 Replies
View Related
Apr 27, 2015
I wanted to implement the feature to send the email in HTML format from SQL. Which option will be more easy and less resource consumption? Like
sp_send_dbmail or sp_OAMethod or anything else?
View 2 Replies
View Related
Apr 17, 2014
I need to display the output of a table in a specific format, I have attached the sample screenshot and code for reference.
I tried with pivot but does not seems to be working.
View 2 Replies
View Related
Mar 21, 2008
Is there any way can I get the 'SELECT' query result in table format? I want to email the query result so that they can read it very easily.
Thank you,
Gish
View 15 Replies
View Related
Dec 12, 2014
I have sample data like 'J.S.xxxx','J.xxxx.'
Want to separate the . in the middle of word with single space and display results in following way.
J. S. xxxx
J. xxxx
Need the code in optimized way.
View 3 Replies
View Related
Jan 29, 2014
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...
View 5 Replies
View Related
Jun 18, 2015
I have below table:
IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints
--===== Create the test table with
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
INSERT INTO #cs
([Year], [ Week], [ Month], [C#],[Dept],[Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, D1,I1,T1, 5 UNION ALL
SELECT 2015, 14, 4, 452,d1, I1, T2, 5 UNION ALL
SELECT 2015, 14, 4, 63, d1, I1, T1, 6 UNION ALL
SELECT 2015, 14, 4, 9, d1,I2, T1, 7 UNION ALL
SELECT 2014, 14, 4, 187, D1,I1,T1, 5 UNION ALL
SELECT 2014, 14, 4, 451,d1, I1, T2, 5 UNION ALL
SELECT 2014, 14, 4, 62, d1, I1, T1, 6 UNION ALL
SELECT 2014, 14, 4, 10, d1,I2, T1, 7 UNION ALL)
and i want to have a table in below format: (query for it)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
View 9 Replies
View Related
Aug 20, 2015
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
View 6 Replies
View Related
Apr 21, 2015
Using below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
View 2 Replies
View Related
Jan 6, 2015
I have the following query that supposes to merge multiple result in a single one and put it into a temporary table:
SELECT DISTINCT [AlphaExtension],
STUFF((SELECT A.[NoteText] + '< BR />' FROM #temp A
WHERE A.[AlphaExtension]=B.[AlphaExtension]
FOR XML PATH('')),1,1,'') As [NoteText]
FROM #temp B
GROUP BY [AlphaExtension], [NoteText]
It is working fine unless by a simple detail. If you look at the second line of the query you will see that I am stuffing together a < BR /> tag (break line) because the contents of the field is going to be spitted directly to the screen and I want that the multiple results be displayed in different lines.
OK, the issue is that it is stuffing & lt ; BR / & gt ; instead < BR /> and therefore the browser is displaying the tag instead to break a line.
View 2 Replies
View Related
Sep 3, 2015
I am using FOR XML to generate the HTML and following is my query. I am using td { white-space:nowrap;} for the columns to used no wrap. But i want two columns which need to be wrapped. i have tried lots of options.
SET @tableHTML =
N'<H3><font size="4"> Board - Items </font></H3>' +
N'<table border="1" ><font size="1"> <head><style TYPE="text/css"> td { white-space:nowrap;} </style> ' +
N'<tr bgcolor=#4b6c9e><font size="2" color=white><th> ID</th>
<th> A</th>
<th> B </th>
[Code] ...
From the above code i want G and H columns to be wrapped.
View 3 Replies
View Related
Jul 15, 2015
I'm struggling to modify the T-SQL query below to return the results as XML.
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119, 122);
The XML result document should have the following structure:
<Root>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
</Root>
View 2 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Oct 8, 2014
I have someone who is sending me .htm documents, with a table in them, and I was wondering if there is a way to import the data from those tables into a SQL table, probably using an SSIS Package.
View 4 Replies
View Related
Dec 22, 2006
Hi
I'm trying to output HTML stored in the database as litreal HTML in a table on a reportviewer report. Currently any thing such as <strong>yyy</strong> is encoded as literal text with the source reading <strong>yyy<strong> whereas I want it to output as yyy. Any help would be appreciated.Thanks
Zaggle
View 4 Replies
View Related
Sep 1, 2015
I have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;
[code="CASE WHEN isdate( arc.UserField1 )= 1 then
CAST( arc.UserField1 as DateTime )
ELSE
CAST( '01/01/1900' as Datetime )
END"] [/code]
The results are ;
Date Result
29/09/08 1900-01-01 00:00:00.000
09/06/15 2015-09-06 00:00:00.000
18/03/13 1900-01-01 00:00:00.000
09/10/14 2014-09-10 00:00:00.000
as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?
View 9 Replies
View Related
Dec 19, 2007
Hi,
I have a set of csv files and a set of Format Specification files for each of the csv files. I need to convert the csv files into another format of csv files as specified in the Format Specification files. All the columns of the input csv files do not have a mapping with the columns of the output csv files. How can I achieve this using SSIS ? This is an urgent requirement. Please reply asap. Thanks.
View 1 Replies
View Related
Feb 24, 2003
Does anyone know where I can find a freeware Rich Text to HTML ActiveX Control?
View 1 Replies
View Related
Jan 21, 2008
Hi Friends,
I am new to reporting services . In Excel reports we can create pivot tables and manipulate data easily. Is it possible to create the pivot table in HTML output in SQL Server 2005 without changing the existing procedure or function? I wonder if there is any drag and drop facility to do the same in HTML report. Hope , some one might have dealt with this.
Regards,
Bhushan
View 1 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
Aug 19, 2015
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.
View 5 Replies
View Related
Nov 23, 2000
Hi to all,
I use sqlmail to send mails to clients, but I must send a report mail in HTML format...
is possible ????
this is my code, that return a simple string and not a HTML page when I open the mail:
------------------------------------------------------------------------
------------------------------------------------------------------------
set nocount on
declare @message varchar(8000)
declare @tempmsg varchar(8000)
declare @attach varchar(255)
declare @msg_id varchar(255)
declare @subject varchar(255)
declare @status int,
@originator varchar(255),
@cc_list varchar(255),
@date varchar(255),
@originator_address varchar(255)
Set @message='<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">'
Set @message=@message + '<!-- -->'
Set @message=@message + '<HTML>'
Set @message=@message + '<HEAD>'
Set @message=@message + '<TITLE>Novità di Microsoft Italia - Anno 2 Numero # 20</TITLE>'
Set @message=@message + '<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>'
Set @message=@message + '<META content="MSHTML 5.00.2920.0" name=GENERATOR>'
Set @message=@message + '</HEAD>'
Set @message=@message + '<BODY>'
Set @message=@message + '<TABLE bgColor=#ffffff border=0 cellPadding=8 cellSpacing=0 width=600>'
Set @message=@message + '<TD vAlign=top width=220>'
Set @message=@message + '<FONT face=Verdana,Arial,Helvetica size=2 style="FONT-FAMILY: Verdana; FONT-SIZE: 11px">'
Set @message=@message + '<IMG alt="Software
gratuito" border=0 height=50 src="http://www.microsoft.com/italy/newsletter/images/sw.gif" width=180>'
Set @message=@message + '<TABLE border=0 cellPadding=0 cellSpacing=0>'
Set @message=@message + ' <TD>'
Set @message=@message + '<FONT face=Verdana,Arial,Helvetica size=2 style="FONT-FAMILY: Verdana; FONT-SIZE: 11px">'
Set @message=@message + '<A href="http://www.microsoft.it/italy/adv/jump.asp?cod=0020_wordup"> Word 2000 - Aggiornamento: protezione della stampa unione </A>'
Set @message=@message + '</FONT>'
Set @message=@message + '</TD>'
Set @message=@message + '</TABLE>'
Set @message=@message + '</BODY>'
Set @message=@message + '</HTML>'
EXEC master.dbo.xp_sendmail @recipients = 'emiliano.cecchetti@tei.ericsson.se',
@subject = 'Report',
@message = @message
---------------------------------------------------------------------------------
Thanks for any help,
Emiliano.
View 4 Replies
View Related
Jan 9, 2007
In the DTS, using SEND MAIL TASK option !
I want to send the message in html format. When i receive in outlook 2003, it should display in html format.
How to do this ?
View 1 Replies
View Related
Apr 21, 2015
SELECTÂ
  CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
  CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate   04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
View 8 Replies
View Related
Mar 17, 2015
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
View 0 Replies
View Related
Aug 19, 2015
I have a table Test123 having three column EmpID,AttribName,AttribValue.
run the below query to generate table structure and data.
Create Table Test123(EmpID int,AttribName varchar(50),AttribValue varchar(50))
insert into Test123 values(1,'Name','X')
insert into Test123 values(1,'Age',50)
insert into Test123 values(1,'Salary',1000)
insert into Test123 values(2,'Name','Y')
insert into Test123 values(2,'Age',30)
insert into Test123 values(2,'Salary',2000)
insert into Test123 values(3,'Name','Z')
insert into Test123 values(3,'Age',35)
insert into Test123 values(3,'Salary','One Hundred')
And I want output in below format.
AttributeValueType
=================
AGE | NUMERIC
NAME | ALPHABET
SALARY | ALPHANUMERIC
==================
View 6 Replies
View Related
Mar 26, 2014
I have two dates '2014-25-03 01:02:03' & '2014-26-03 01:02:03' (yyyyddmm).
I need the difference between two dates in hh:mm:ss format.
I tried following query
declare @mindate datetime='2014-03-25 01:02:03'
declare @maxdate datetime='2014-03-26 02:03:04'
select cast(
(cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
+ datepart(hh, @maxDate - @minDate) /* hours */
as varchar(10))
+ ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
+ ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */
This works fine but as you can see the date I have used in the query is in yyyymmdd format.
The query does not work with yyyyddmm format
The expected answer is =25:01:01.
View 5 Replies
View Related
May 13, 2015
We are facing an issue where the report rendering for specific report parameters is failing with an exceptionÂ
Throwing Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: , Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.OnDemandReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.ReportingServices.Rendering.SPBProcessing.Tablix.TablixContext.CalculateDetailCell(PageItem topItem, Int32 colIndex, Boolean collect, PageContext pageContext)
[code]....
The server is running in Native mode. We tried restarting the services and also verified the disk space. Neither of them worked. The ExecutionLog3 table in the "ReportServer" database shows a rrenderingError as the report execution status. Report rendering with Excel format works fine.We enabled verbose logs and they are shared here. URL....
View 7 Replies
View Related
Mar 2, 2014
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.
View 3 Replies
View Related
Jun 9, 2014
I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. Here's the data I'm working with:
create table #data (myXMLData xml)
insert into #data(myXMLData) values (
'<?xml version="1.0" encoding="utf-8"?>
<order>
<Product Index="e1e2c499-f9...etc...." ProductID="12" >
<Attribute Name="Paper...etc..." />
[Code] ....
Here's the working XPath query and results:
select data.mynodes.value('@Name', 'varchar(50)') Name
from #data
cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes)
Results:
Name
43001PBOX
[Code] .....
Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.
select myXmlData.query('
for $drop in /order/Product/Drops/Drop
for $Name in $drop/Area/@Name
where $drop/@Number=1
return data($Name)
')
from #data
Results:
43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001
How to build a FLOWR-based query that produces the same result as the pure XPath one?
View 1 Replies
View Related
Mar 19, 2015
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
View 9 Replies
View Related
