SQL Server 2012 :: How To Go To Next Line In Output And Print A Table
Aug 13, 2014
I've the following T-SQL block that I'm executing where I want to print the following output in next line but not sure how to do this:
This is printing everything in one line:
SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
How do I do this:
SET @BODYTEXT = ' Dear ' + @fname +
',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
Also how can I create a table in this variable, something like this:
(TABLE) LIST THE COURSE CODE, COURSE NAME , EMPLOYEE ID, EMPLOYEE NAME
(Course Name) (Last Completed) (Now due in Month/year)
My T-SQL code:
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
,@fname varchar(100)
[Code] ......
View 2 Replies
ADVERTISEMENT
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
Sep 9, 2015
I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation
Is there any way to print all the 3 INSERT stmt.
IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'
[code]....
View 4 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
Sep 21, 2015
I have been given a request at work that requires us to run the SQL scripts that are held in a report configuration table and output the results as .csv or.xls files in a desired folder with a file name that is also specified within the report config table.
An example of the table is as per script below with column A being the desired file name to be created and Column B contains the script that will be executed.
Ideally I require a script that i can drop into a Stored Proc that will run down each row in the table and export and create each rows results as a separate file in a desired folder.
------------------------------------------------------------------------------------------
-----SAMPLE TABLE SCRIPT -----------------------------------------------------------
------------------------------------------------------------------------------------------
/****** Object: Table [dbo].[Test_Table_PS] Script Date: 21/09/2015 09:14:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_Table_PS](
[File_Name] [nvarchar](100) NULL,
[Code] ....
View 7 Replies
View Related
Sep 21, 2015
I want print state of running query to output, because my query need long time to run.But print statement dos not work correctly!
Note: I cannot use "go" in my query!
/* My query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'
[code]...
View 2 Replies
View Related
Sep 21, 2015
I want print state of running query to output, because my query need long time to run. But print statement dos not work correctly!!
Note: I cannot use "go" in my query!
/* My Query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'
[Code] .....
View 1 Replies
View Related
Mar 5, 2015
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.
View 2 Replies
View Related
Mar 9, 2007
i have to print Go statement in new line through isql command
my isql command is
isql /Q -SServername -dDatabasename -Usa -PPassword -h-1 -n -q "set nocount on Select ' grant execute on ' + name + ' to user go ' from sysobjects where type = 'U' and name not like 'dt%'" -o SFGRants.sql >> Deploy.log
and out is like
select grant execute tablename to user go
but i want in different format like
select grant execute tablename to user
go
like that
thanking in anticipation
View 1 Replies
View Related
Oct 21, 2014
I convert EBCDIC file to text file at C:orderorder.txt. There are about 1400000 rows. The length of line is 1500.
How to insert entire line into one column of the table? (only one column, named as [LineDetail])
View 1 Replies
View Related
Jun 5, 2014
I have a process to rollover prior quarter data to new quarter in a table.
For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
4,'today',2014,2
5,'tomorrow,2014,2
6,'friday',2014,2
Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )
Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.
Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like
1,4
2,5
3,6
I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.
View 9 Replies
View Related
Mar 20, 2015
I'd like to know how to get windows events script below into a SQL Server Table.
Get-WinEvent -LogName application -MaxEvents 200 | where {$_.LevelDisplayName -eq "Error"}
View 1 Replies
View Related
Apr 15, 2014
To get the results of a stored proc into a table you always had to know the structure of the output and create the table upfront.
CREATE TABLE #Tmp (
Key INT NOT NULL,
Data Varchar );
INSERT INTO #Tmp
EXEC dbo.MyProc
Has SQL 2012 (or SQL 2014) got any features / new tricks to let me discover the table structure of a stored procedure output, - i.e treat it as a table
EXEC dbo.MyProc
INTO #NewTmp
or
SELECT *
INTO #NewTmp
FROM ( EXEC dbo.MyProc )
View 9 Replies
View Related
Dec 21, 2007
For certain reason, I set nocount off in the proc procABC.
I need to collect all the counters(Ins/Upd/Del) and
print statment on the isql/w screen to a .txt file.
How could I capture all the running info as a daily log file?
thanks
David
View 1 Replies
View Related
Dec 29, 2006
We are experiencing a problem where a server report is rendered in the
ReportViewer control successfully but when the user chooses to print the
content, the print output is incomplete (ex. only 18 of 23 pages are printed,
etc.).
This is happening consistently and has been reproduced for multiple
different reports. It does not happen if the same report is printed via the
Report Manager web interface.
I have also found that if I switch to Print Layout mode and then initiate
the print from the ReportViewer that the problem does not occur. This is our
current workaround, but it would be nice to know what is causing this and
have it fixed.
Has anybody else seen this? Is this a known issue with my current version
(SQLRS 2005 with SP1 applied).
Thanx in advance.
View 3 Replies
View Related
Mar 2, 2015
I have SQL Server 2012, and I'm trying to execute a bcp with a queryout and it is not working with line break, I will send you an example of a simple query my real case is a very long query that contains line breaks but this example behaves the same:
DECLARE @StoredProcedure AS NVARCHAR(4000) ,
@sql AS NVARCHAR(4000)
SET @StoredProcedure = N'"SELECT *
from clarity.[dbo].[ACCESS_DEP]"'
[code]...
Of course that is not the solution as I have a very long query that requires line breaks.
View 1 Replies
View Related
Sep 14, 2015
Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')
[Code] .....
View 3 Replies
View Related
Jan 20, 2007
I am using the following code to query a DB and output an Excel Spreadsheet. For some reason, the first line is always missing.Any help would be greatly appreciated.============================================1 reader = cmd.ExecuteReader()
2 If Not reader.Read() Then
3 Return False
4 Else
5 For i = 0 To reader.FieldCount - 1
6 strLine += reader.GetName(i).ToString & Chr(9)
7 Next
8 objStreamWriter.WriteLine(strLine)
9 strLine = ""
10 With reader
11 While .Read
12 For x = 0 To .FieldCount - 1
13 strLine = strLine & reader.GetValue(x) & Chr(9)
14 Next
15 objStreamWriter.WriteLine(strLine)
16 strLine = ""
17 End While
18 End With
19 End If
View 2 Replies
View Related
Feb 8, 2004
I was nosing around the documentation of the IBuySpy store and noticed this line:
---------------------------------------------------------
This stored procedure accepts a ProductID as an input parameter and returns product information to the caller in the form of output parameters. A one line recordset is not used in order to gain a performance increase. It is used primarily on the product details screen.
----------------------------------------------------------
Can anyone shed some light on if this is a best practice when retrieving a single row from the db? Is the performance gain very noticable? I mean it makes sense and all, I just see a lot of examples using a datareader when there is more than 1 column, and ExecuteScalar() when there is one.
I just wrote an application that might benefit from this if its worth the trouble of changing a couple methods in my data layer calls to ouput parameters.
Thanks in advance for any input!
View 7 Replies
View Related
Apr 23, 2015
I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string. Â For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.
Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.
View 4 Replies
View Related
May 21, 2015
How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.
CREATE
TABLE [dbo].[MRMReadings](
[SiteIncomeID] [int] IDENTITY(1,1)
NOT NULL,
[SiteID] [nchar](5)
NOT NULL,
[Code] ....    Â
Is it possible to return the data in the following format merging 2 lines of data into one output:
 SiteID  ReadStartDate ReadEndDate    ReadStartIncome    ReadEndIncome
L0020Â Â Â Â 19/05/2015 05:00Â 20/05/2015 05:00Â Â Â 85.98Â Â Â Â 145.98
L0101Â Â Â Â 19/05/2015 22:07Â Â Â 20/05/2015 22:14Â Â Â Â Â Â Â Â Â 1,936.08Â Â Â Â Â Â 1,438.89
L0102Â Â Â Â 20/05/2015 21:16Â Â Â 19/05/2015 21:48Â Â 143.65Â 243.5
I am using SQL 2008 R2.
View 12 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
Oct 7, 2014
I have a sql query that gets the count of exams held in each month.
Below is the code that I have used.
select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid
The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.
I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.
I tried using case but it does not work.
View 6 Replies
View Related
Nov 19, 2013
I need a query which results the output as comma separated
for example ,
select name from tablename;
say for example this produces the following,
Name
Jack
John
Smith
I need the follwoing,
Jack,John,Smith
View 2 Replies
View Related
Jan 22, 2014
Assume i have 3 tables
Person(personname,age)
Children(childname,personname)
car(carname,personname)
A persone can have multiple cars
A person can have multiple children
Its not possible to display the results in SQL rows and columns.
If i try to it will give
PersonName Carname Childname
Sachin Audi C1
Sachin Maruti C1
Sachin Audi C2
Sachin Maruti C2
Instead of writing seperate queries the application wants to receive an xml output as follows
<person>
<pname>sachin</pname>
<car>audi</car>
<car>bmw</car>
<cname>c1</cname>
<cname>c2</cname>
<person>
How to get this output ?
View 1 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
Oct 21, 2014
Assume I built a stored proc (dbo.testproc) that will return the OUTPUT parameter @RandomInteger.
I could pass a specific value for the parameter...
EXEC dbo.testproc 7
Or I could return a value from the proc...
DECLARE @ReturnInteger
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger
But I want to do both which, if this actually worked, might look like this...
DECLARE @RandomInteger
SET @RandomInteger = 7
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger
I want to pass a specific value and return the result from the proc. Do I need to use two parameters for this?
View 7 Replies
View Related
Nov 6, 2014
I'm looking at various methods for deleting duplicate rows. Among the alternatives, one works just fine but gives me results that make me go?.
Consider this script:
declare @t table (a int, b int, c int, d int, e int)
insert into @t (a, b, c, d, e) values
(1, 2, 3, 4, 5),
(3, 4, 2, 3, 4),
(1, 2, 3, 4, 5)
select a,b,c,d,e, rn = row_number() over (
partition by a,b,c,d,e
[Code] ....
The code works -- that is, the duplicate row is deleted. However the output clause returns:
abcdern
123451
So....why? Why does the output clause show that the row with rn=1 was deleted, when the where clause stipulates rn > 1?
View 9 Replies
View Related
Feb 20, 2015
I would like to create a function that take a value and run some logic and output the value
I have a table like this
Table A
value
*
001
004.00
3.0
1.22
Logic I want to run is
The value that you are passing is numeric and numeric with only decimal 0 value, and then convert it to integer otherwise leave as it is
So if I run a query something like this
Select value, fn_convertointerger(value) as converted_value from TableA
I will get
Value converted_value
* *
001 1
004.00 4
3.0 3
1.22 1.22
2.02 2.02
4.000 4
Jkil& Jkil&
How can I create a function like this to convert specific numeric value?
View 9 Replies
View Related
May 6, 2015
I have following XQuery:
declare @xmldoc as xml
select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>'
select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
View 2 Replies
View Related
May 25, 2006
Hello,
I created a SSIS package that has a flat file manager whose connection string is a package variable, the reason is that there is a foreachfile container loop to loop thru CSV format files in a directory and load them to a sql table.
The package execution is working in the designer studio as well as in the management studio-- a copy was saved to a sql 2005 server where a sql job is deployed to run the package. However when I ran the job, it claims successful but doesn€™t do anything in reality. If I checked the box failing the package upon validation warming, the job will fail with the error: the command line parameters are invalid. The command line looks like below:
/SQL "Package" /SERVER FTLQSQL03 /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
One thing that I think maybe wrong is on the data source tab of the job step GUI, the flat file manager€™s connection string is blank, compared to other connection managers having related values.
Does anyone know how to make the job working?
Thanks in advance
I
View 3 Replies
View Related
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
View Related
May 19, 2008
Hi All,
I have a simple SSIS package that runs a query on the db and outputs a fixed width flat file. I have all my column widths defined and in the connection manager i can preview the output. Everything looks great. All the fields fall where they should and each record is on it's own line.
When i run the SSIS program and then go open my text file with a text editor the ouput is all on the same line. I have tried changing my file format from fixed width to ragging right and adding a row delimiter but that doesn't work either. I feel like i'm missing something small here. It could even be an issue w/ my text editor (although i've tried to open the text file in multiple editors). In the flat file connection manager I have my file defined to be 187 characters long, So figure every 187 characters it should output a new line (it should add the carraige return right?).
Has anyone encountered an issue like this?
Any help would be much appreciated.
View 4 Replies
View Related