Concatanete new line

Hi,
I need to store data in XML format in the database. When the data is retrieved and printed onto a file, say, it should be automatically printed in a file format i.e. each seperate line on a new line. How can this be done? Can we append a newline character to each line before storing it as a record in the database?/If so, how can this be done?

Thanks in advanceYou can add a Carriage Return and Line Feed to a string as follows:

'your string' + CHAR(13) + CHAR(10)sqlsql

Complicated Task Please Help

Hi All,
I have a requirment to pull a query that would result in something like the
following:
[Service Line] | [POCount] | WEnd-04-02-2005 | WEnd-04-9-2005 |
WEnd-04-016-2005 | WEnd-04-26-2005
MNS 15 3
2 10
etc...
I create a temp table and dump the data into it and find the Wending by
using a function.
My problme is that I can't get it to group like indicated above.
Below is my entire code, excluding the Function.
Please Help... I'm desperate :)
John.
CREATE PROCEDURE dbo.usp_RPT_OPW_FLASH
@.startdate SmallDateTime,
@.enddate SmallDateTime
AS
SET NOCOUNT ON
BEGIN TRANSACTION
---
--Create Temporary Table To Hold the data
---
Create Table #tmpOPW_FLASH (
[Service Line] VarChar(50),
[Total Of PONumber]INT,
RC DateTime,
RS DateTime,
CycleTime Decimal(18,2),
WEnding DateTime
)
---
--Insert Data into temporary table for DATE_ORDERED (Issued)
---
INSERT INTO #tmpOPW_FLASH([Service Line], [Total Of PONumber], RC, RS)
SELECT T1.DataSource AS [Service Line], COUNT(T1.PoNumber) AS [Total Of
PONumber],
T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
FROM OPW T1 INNER JOIN
(SELECT PoNumber
FROM OPW
GROUP BY PoNumber
HAVING COUNT(*) > 1) T2 ON T1.PoNumber = T2.PoNumber
WHERE (T1.ReqSubmitDate BETWEEN @.startdate AND @.enddate)
GROUP BY T1.DataSource, T1.REQCreateDate + CAST(T1.REQCreateTime AS
DATETIME), T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME)
---
--UPDATE CycleTime and WEnding Column
---
UPDATE #tmpOPW_FLASH
SET CycleTime = DateDiff(hh, RC, RS),
WEnding = DBO.usf_WEnding(RS)
----
SELECT [Service Line], [Total Of PONumber], WEnding
FROM #tmpOPW_FLASH
GROUP BY [Service Line], [Total Of PONumber], WEnding
DROP TABLE #tmpOPW_FLASH
COMMIT TRANSACTIONI think that you wont need your Temp table although i cant understand your
intention, but these update can be done in an inline statement, because you
are only calling a function to do this. Please post some DDL and smaple data
which can be read a bit better then the pasting of your last post, and well
fix it together :-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"John Rugo" <jrugo@.patmedia.net> schrieb im Newsbeitrag
news:uAbrsS1SFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I have a requirment to pull a query that would result in something like
> the following:
> [Service Line] | [POCount] | WEnd-04-02-2005 | WEnd-04-9-2005 |
> WEnd-04-016-2005 | WEnd-04-26-2005
> MNS 15 3 2
> 10
> etc...
> I create a temp table and dump the data into it and find the Wending by
> using a function.
> My problme is that I can't get it to group like indicated above.
> Below is my entire code, excluding the Function.
> Please Help... I'm desperate :)
> John.
>
> CREATE PROCEDURE dbo.usp_RPT_OPW_FLASH
> @.startdate SmallDateTime,
> @.enddate SmallDateTime
> AS
> SET NOCOUNT ON
> BEGIN TRANSACTION
> ---
> --Create Temporary Table To Hold the data
> ---
> Create Table #tmpOPW_FLASH (
> [Service Line] VarChar(50),
> [Total Of PONumber]INT,
> RC DateTime,
> RS DateTime,
> CycleTime Decimal(18,2),
> WEnding DateTime
> )
> ---
> --Insert Data into temporary table for DATE_ORDERED (Issued)
> ---
> INSERT INTO #tmpOPW_FLASH([Service Line], [Total Of PONumber], RC, RS)
> SELECT T1.DataSource AS [Service Line], COUNT(T1.PoNumber) AS [Total Of
> PONumber],
> T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
> T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
> FROM OPW T1 INNER JOIN
> (SELECT PoNumber
> FROM OPW
> GROUP BY PoNumber
> HAVING COUNT(*) > 1) T2 ON T1.PoNumber = T2.PoNumber
> WHERE (T1.ReqSubmitDate BETWEEN @.startdate AND @.enddate)
> GROUP BY T1.DataSource, T1.REQCreateDate + CAST(T1.REQCreateTime AS
> DATETIME), T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME)
> ---
> --UPDATE CycleTime and WEnding Column
> ---
> UPDATE #tmpOPW_FLASH
> SET CycleTime = DateDiff(hh, RC, RS),
> WEnding = DBO.usf_WEnding(RS)
> ----
> SELECT [Service Line], [Total Of PONumber], WEnding
> FROM #tmpOPW_FLASH
> GROUP BY [Service Line], [Total Of PONumber], WEnding
> DROP TABLE #tmpOPW_FLASH
> COMMIT TRANSACTION
>|||Your approach is too procedural. Create a table of reporting periods:
CREATE TABLE ReportPeriods
(period_name CHAR (20) NOT NULL PRIMARY KEY,
period_start_date DATETIME NOT NULL,
period_end_date DATETIME NOT NULL,
CHECK (period_start_date < period_end_date));
Then write something like:
SELECT service_line, po_cnt,
CASE WHEN OPW.some_date BETWEEN R1.period_start_date and
period_end_date
AND R1.period_name = ''WEnd-2005-04-02'
THEN 1 ELSE 0 END AS wend-2005-04-02,
etc.
FROM ReportPeriods AS R1, OPW
WHERE ... ;
In short, think in terms of joins and not computations.
You are also making the classic Newbie error of trying to format the
data and the column headers in the backend. The whole point of a
tiered architecture is that you do this only in the front end.
Also, please post DDL, so that people do not have to guess what the
keys, constraints, Declarative Referential Integrity, datatypes, etc.
in your schema are. Sample data is also a good idea, along with clear
specifications.|||/* general this is the type of data and structure I am working with.
I need to show the data as indicated before below:
Basically I did this on the Applications Front end and sent in a Dynamic SQL
string. I know there must be a way to do this via a procedure; I'm just
learning the more advanced stuff now.
One of my current delemas is getting the WEnd items to be grouped
columniarly as show below.
*/
CREATE TABLE OPW (
DataSource VarChar(50),
PoNumber VarChar(22),
ReqCreateDate datetime
ReqCreateTime varchar(10),
ReqSubmitDate datetime
ReqSubmitTime varchar(10),
NamePrep varchar(240))
INSERT INTO OPW ('MNS', PW02939, '04/01/2005', '11:39:58', '04/04/2005',
'12:30:00', 'JOHN')
INSERT INTO OPW ('MNS', PW02939, '04/01/2005', '11:39:58', '04/04/2005',
'12:30:00', 'BILL')
INSERT INTO OPW ('MNS', PW02937, '04/03/2005', '11:39:58', '04/07/2005',
'12:30:00', 'JOHN')
INSERT INTO OPW ('MNS', PW02934, '04/20/2005', '11:39:58', '04/23/2005',
'12:30:00', 'BOB')
INSERT INTO OPW ('MNS', PW02937, '04/03/2005', '11:39:58', '04/07/2005',
'12:30:00', 'JOHN')
INSERT INTO OPW ('MNS', PW02933, '04/17/2005', '11:39:58', '04/20/2005',
'12:30:00', 'JOHN')
/*
Service Line PO Created By Total Of PONumber 04/02/2005 04/09/2005
04/16/2005 04/23/2005 04/30/2005
EVPN MURPHY, ANTOINETTE J 4 4
MNS ADELER, LISA 13 11 2
MNS ARAGON, JENNIFER 59 35 24
MNS BERIG, CARI 22 12 10
MNS KIRBY, DANIEL 101 76 25
MNS KRENKEL, LISA R 60 60
MNS LEONARDELLI, JOHN MICHAEL 143 40 103
MNS MADSEN, SHANE 24 14 10
MNS MURPHY, ANTOINETTE J 3 3
MNS PYELL, CLAUDETTE 11 11
MNS SCARBOROUGH, MERNA 80 10 70
MNS WHALEY, LEE 6 6
*/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OwD31a1SFHA.580@.TK2MSFTNGP15.phx.gbl...
>I think that you wont need your Temp table although i cant understand
>your intention, but these update can be done in an inline statement,
>because you are only calling a function to do this. Please post some DDL
>and smaple data which can be read a bit better then the pasting of your
>last post, and well fix it together :-)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "John Rugo" <jrugo@.patmedia.net> schrieb im Newsbeitrag
> news:uAbrsS1SFHA.3544@.TK2MSFTNGP10.phx.gbl...
>|||Sorry i read for about 10 minutes you ddl and output (whch is formatted in a
strange way) and even now i dont know what you are going to do. I am
willing to help you but i cant understand your question. :-(
If this oist will last too long and i loose track of it, jst write me an
email to see wheter we can check it off ngposting and post the results back.
Jens Suessmeyer.
"John Rugo" <jrugo@.patmedia.net> schrieb im Newsbeitrag
news:%23RbaS61SFHA.900@.TK2MSFTNGP10.phx.gbl...
> /* general this is the type of data and structure I am working with.
> I need to show the data as indicated before below:
> Basically I did this on the Applications Front end and sent in a Dynamic
> SQL string. I know there must be a way to do this via a procedure; I'm
> just learning the more advanced stuff now.
> One of my current delemas is getting the WEnd items to be grouped
> columniarly as show below.
> */
> CREATE TABLE OPW (
> DataSource VarChar(50),
> PoNumber VarChar(22),
> ReqCreateDate datetime
> ReqCreateTime varchar(10),
> ReqSubmitDate datetime
> ReqSubmitTime varchar(10),
> NamePrep varchar(240))
> INSERT INTO OPW ('MNS', PW02939, '04/01/2005', '11:39:58', '04/04/2005',
> '12:30:00', 'JOHN')
> INSERT INTO OPW ('MNS', PW02939, '04/01/2005', '11:39:58', '04/04/2005',
> '12:30:00', 'BILL')
> INSERT INTO OPW ('MNS', PW02937, '04/03/2005', '11:39:58', '04/07/2005',
> '12:30:00', 'JOHN')
> INSERT INTO OPW ('MNS', PW02934, '04/20/2005', '11:39:58', '04/23/2005',
> '12:30:00', 'BOB')
> INSERT INTO OPW ('MNS', PW02937, '04/03/2005', '11:39:58', '04/07/2005',
> '12:30:00', 'JOHN')
> INSERT INTO OPW ('MNS', PW02933, '04/17/2005', '11:39:58', '04/20/2005',
> '12:30:00', 'JOHN')
>
> /*
> Service Line PO Created By Total Of PONumber 04/02/2005 04/09/2005
> 04/16/2005 04/23/2005 04/30/2005
> EVPN MURPHY, ANTOINETTE J 4 4
> MNS ADELER, LISA 13 11 2
> MNS ARAGON, JENNIFER 59 35 24
> MNS BERIG, CARI 22 12 10
> MNS KIRBY, DANIEL 101 76 25
> MNS KRENKEL, LISA R 60 60
> MNS LEONARDELLI, JOHN MICHAEL 143 40 103
> MNS MADSEN, SHANE 24 14 10
> MNS MURPHY, ANTOINETTE J 3 3
> MNS PYELL, CLAUDETTE 11 11
> MNS SCARBOROUGH, MERNA 80 10 70
> MNS WHALEY, LEE 6 6
> */
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OwD31a1SFHA.580@.TK2MSFTNGP15.phx.gbl...
>

Compilation error on store procedure

Hi all,
Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10
Syntax error converting the varchar value'The account type is already exist' to a column of data type int.
Here is my procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN @.message
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)
--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN @.message

END
ELSE
BEGIN

COMMIT TRANSACTION
END

RETURN @.@.ROWCOUNT
END
GO

--execute the procedure
DECLARE @.message VARCHAR (40);
EXEC NewAcctTypeSP 'CDs', @.message;
I am not quite sure where I got a type converting error in my code and anyone can help me solve it?
(p.s. I want to return the @.message value to my .aspx page)
Thanks.

You should probably get rid of the RETURN @.@.ROWCOUNT line. It may be getting confused that sometimes you return a varchar and other times int (the rowcount).
Marcie|||Hi Marcie,
I have get rid of the @.@.RowCount, however, I didn't get value from my @.message, it just returned 'undefined'.
do u know why, and how can i fix it?
Thanks.|||Where is it undefined, from your code or still running your test proc?
Marcie|||

when I executed my .aspx page, I input a duplicated value in a field intentionally and since it should return an error message that I have defined in my dtore procedure, however, I just got 'undefined' instead of my error message...
so, here is my .aspx code:
public function SubmitClick (sender:Object, e:EventArgs) : void
{
if (Page.IsValid)
{
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));
var acctTypeDA : SqlDataAdapter = new SqlDataAdapter ("select * from AcctTypeCatalog", myConnection);

acctTypeDA.InsertCommand = new SqlCommand("NewAcctType", myConnection);
acctTypeDA.InsertCommand.CommandType = CommandType.StoredProcedure;
acctTypeDA.InsertCommand.Parameters.Add(new SqlParameter("@.acctType", SqlDbType.VarChar, 20)).Value = accountType.Text;

var myParm : SqlParameter = acctTypeDA.InsertCommand.Parameters.Add("@.message", SqlDbType.VarChar, 40);
myParm.Direction = ParameterDirection.Output;

<%--try to get value from @.message and assign it to the variable --%>
var msg : String = acctTypeDA.InsertCommand.Parameters("@.message").Value;
msgLabel.Text = msg;

BindGrid();

}
else
{
msgLabel.Text = "The Page contains error!"
}
}
my store procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
SET @.message = 'Insertion Successful!'
COMMIT TRANSACTION
END
RETURN
END
GO
I have tested my procedure, it works fine in SQL server, however, don't know why I couldn't get the value from @.message on my .aspx page.
Any idea?
Thanks

|||What language is that? You won't be able to get the value of your OUTPUT parameter until right after the command has executed, something like an ExecuteNonQuery line.
Marcie|||hi Marcie,
I used JScript to create my page with using the store procedure. (JScipt is very similar with C#)
I have reviewed the tutorial from the ASP.net and it doesn't have any ExecuteNonQuery statement on the following example:

<script language="JScript" runat="server">
publicfunction GetEmployees_Click(sender : Object, e : EventArgs) :void
{
var myConnection : SqlConnection =new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("NWString"));
var myCommand : SqlDataAdapter =new SqlDataAdapter("SalesByCategory", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.CategoryName", SqlDbType.NVarChar, 15));
myCommand.SelectCommand.Parameters("@.CategoryName").Value = SelectCategory.Value;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.OrdYear", SqlDbType.NVarChar, 4));
myCommand.SelectCommand.Parameters("@.OrdYear").Value = SelectYear.Value;
var ds : DataSet =new DataSet();
myCommand.Fill(ds, "Sales");
MyDataGrid.DataSource=ds.Tables("Sales").DefaultView;
MyDataGrid.DataBind();
}
</script>

So, should I use the ExecuteNonQuery statement to get my output parameter? any example you could show me for getting back the output parameter value with using store procedure(it's ok if that's written in VB or C#)?
i have tried to find some material about this problem, but can't get any of it ...
Thanks again.
|||In the example you show here, the Command gets executed in the myCommand.Fill line, your code didn't have anything like that.
Since you have your command object set up as the InsertCommand of a DataAdapter object, I believe your command would automatically fire when the .Update method is called.
Marcie|||The problem is that return parameters in T-SQL arealways integers. That is whay you got the compilation error, and why you are not getting the value you expect in your client-side code.
Even though you have @.message defined as an output parameter, the RETURN @.message is causing you to error out.|||

Thanks pjmcb,
I already got my problem fixed.