Output Column names in each Row along with the row Value

Hello,

I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?

eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?

Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3

Thank you! :-)

YasOn Jun 29, 3:09 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?
>
eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?
>
Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3
>
Thank you! :-)
>
Yas

Not sure what you are actually looking at

create table #temp (col1 int, col2 int , col3 varchar(10))
insert into #temp values (1,10,'abcd')
insert into #temp values (2,20,'efgh')

select col1, ' col2:'+cast(col2 as varchar(10))+ '-col3:'+col3 as
col23
from #temp

drop table #temp|||On 29 Jun, 14:27, M A Srinivas <masri...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 29, 3:09 pm, Yas <yas...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?

>

Quote:

Originally Posted by

eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?

>

Quote:

Originally Posted by

Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3

>

Quote:

Originally Posted by

Thank you! :-)

>

Quote:

Originally Posted by

Yas

>
Not sure what you are actually looking at
>

Hi, thanks for the response. Basically I want to have a query in a DTS
package that outputs to a text file with each line containing 1 row of
results.
What I would like to have is on each line before a given value is
written the column header for that value is also written. the file is
a semicolon delimmeted file so it would look something like this...

col1Name;value1;col2Name;value2;col3Name;value3... .etc

so...
FirstName;firstNameValue;SecondName;secondNameValu e;Occupation;occupationValue..etc

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?
especially as I want to do this in a DTS package Export Data which
esentially has a create table type command to ouptut to a text file
and throws errors that destination column name not defined.

Thanks :-)

Quote:

Originally Posted by

create table #temp (col1 int, col2 int , col3 varchar(10))
insert into #temp values (1,10,'abcd')
insert into #temp values (2,20,'efgh')
>
select col1, ' col2:'+cast(col2 as varchar(10))+ '-col3:'+col3 as
col23
from #temp
>
drop table #temp

|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?

That would indeed be the way to do it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 29 Jun, 23:38, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas (yas...@.gmail.com) writes:

Quote:

Originally Posted by

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?

>
That would indeed be the way to do it.

Thanks! :-)

out-of-range smalldatatime value

I am trying to insert a rown into a table with this line in my program :

INSERT INTO MYTABLE (Usuario, Datahora, COD_PROG, Tabela, TipoMov, Registro, Campo, ValAnt, ValAtu, Motivo, Sequencial) VALUES (23, '06/28/2007 19:53:45', '002101', 'Contribuinte', 'A', '1006626', ' ', 'Isentou Tx 2a Via 062007', ' ', ' ', ' ')

and I am receiving this message :

The conversion of char data type to smalldatatime data type resulted in an out-of-range smalldatatime value.

Thanks

Hello

I suppose that the smalldatetime has the value 06/28/2007 19:53:45

what is the language used for the server ?

the format of the date is corresponding to month/day/year ( american format ).Maybe it's the origin of the problem.

For the french format ( dd/mm/yyyy ), you have an error ( 28 does not correspond to a month )

Have a good day

|||

The smalldatetime datatype does NOT include seconds. But it truncates the seconds. -That isn't the problem.

I suspect that your server is expecting a date in the form of 'dd/mm/yyyy', and you are providing 'mm/dd/yyyy'.

If you changed the date format on the INSERT data to the ISO standard of 'yyyy/mm/dd', you wouldn't have any problem.

|||Thank you, that's ok now.

out-of-range datetime value

Hi,

I have a page working with not problem since 3 years, and now that we've moved our database from a SQL Server 2000 to SQL Server 7 and i receive this error everywhere a date is used.

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error append on this line "While myReader.Read()" .. what can i do to solve this problem ?

Thank you

This may be caused by date format that differs from the SQL2000 instance to SQL7.0 instance. Try to change the DATEFORMAT option like this (can be set to mdy,ydm,and ymd) and test again:

SET DATEFORMAT mdy
GO

You can take a look at this link about converting to datetime type:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_27c5.asp?frame=true

sql

out parameter

hello all,
i am trying to get a returned value from a SP using an out parameter.
my code is in C# and i need some help (pls).
be greatfull if you send an example of the SP and the C# code that execute
it and collect the returning parameter value.
thanks a lot, Ran.
I don't think this has a C# example but it does explain OutPut parameters:
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Ran Y" <ranyc@.012.net.il> wrote in message
news:OjMSGU$PEHA.3456@.TK2MSFTNGP11.phx.gbl...
> hello all,
> i am trying to get a returned value from a SP using an out parameter.
> my code is in C# and i need some help (pls).
> be greatfull if you send an example of the SP and the C# code that execute
> it and collect the returning parameter value.
> thanks a lot, Ran.
>
|||[posted and mailed, please reply in news]
Ran Y (ranyc@.012.net.il) writes:
> i am trying to get a returned value from a SP using an out parameter.
> my code is in C# and i need some help (pls).
> be greatfull if you send an example of the SP and the C# code that execute
> it and collect the returning parameter value.
You did not specify which .Net Data Provider you are using, so I am
assuming SqlClient. Furthermore, the code I have around is VB.Net,
so you will need to transliterate in to C# on your own:
Dim p As SqlParameter = New SqlParameter
p.ParameterName = "@.outparam"
p.DbType = SqlDbType.Int ' For instance.
p.Direction = ParameterDirection.InputOutput
p.Value = DBNull.Value
cmd.Parameters.Add(p)
cmd.ExecuteNonQuery ' Or .Fill or an .ExecuteReader loop
The value of @.outparam is now in p.Value.
There are a couple of variations on how you can create the
parameter, see the online documentation for this.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Out of Range Error When Updating A Decimal Value

I am trying to update a database that has decimal(4,1) in a field. It can hold 100, but when I change a value in a gridview to 100 and update it, I get the error below. Any reason wny?

Parameter value '100.0' is out of range.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.ArgumentException: Parameter value '100.0' is out of range.

Source Error:

Line 1841: int returnValue;
Line 1842: try {
Line 1843: returnValue = command.ExecuteNonQuery();
Line 1844: }
Line 1845: finally {

Source File: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs Line: 1843

Stack Trace:

[ArgumentException: Parameter value '100.0' is out of range.]
 System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) +4085
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +11

5
 StatisticsDALTableAdapters.StatisticsTableAdapter.UpdateStatistics(Nullable`1 ID, Nullable`1 PlayerID, Nullable`1 FG, Nullable`1 TTP, Nullable`1 FT, Nullable`1 RPG, Nullable`1 DEFR, Nullable`1 OFFR, Nullable`1 APG, Nullable`1 SPG, Nullable`1 BPG, Nullable`1 Turnovers, Nullable`1 PPG, String SYear, String EYear, String StatisticsType) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\myprofile\45e0ea66\e9c9d282\App_Code._wfrgryw.27.cs:1843
 YourHoopSpace.BLL.StatisticsBLL.UpdateStatistic(Int32 ID, Int32 playerID, Nullable`1 fg, Nullable`1 ttp, Nullable`1 ft, Nullable`1 rpg, Nullable`1 defr, Nullable`1 offr, Nullable`1 apg, Nullable`1 spg, Nullable`1 bpg, Nullable`1 turnovers, Nullable`1 ppg, String sYear, String eYear, String statisticsType) in f:\MyProfile\App_Code\BLL\StatisticsBLL.cs:128

Is this the problem?

int returnValue;

Try decimal in your code. 

|||

HI~

How do you pass the parameters?

Here is sample code:

string str_test = TextBox1.Text; SqlConnection conn =new SqlConnection("Server=T-YOUNGF\\SQLEXPRESS;Database=pubs;uid=sa;pwd=pwd"); SqlCommand cmd =new SqlCommand("update decimal_test3 set column1=@.column1", conn); cmd.Parameters.AddWithValue("@.column1", 100.0); conn.Open();int i = cmd.ExecuteNonQuery(); Response.Write(i.ToString() +" row(s) has updated"); conn.Dispose();

And table:

create table decimal_test3 (column1decimal (4,1));

|||The problem was on my update I needed a decimal of (4,1) instead of (3,1) which I changed in the table not the stored procedure. Why does it need 4 instead of 3 to store 100?|||

decimal of (4,1) can hold upto 999.9

decimal of (3,1) can hold upto 99.9

So 100 is invalid.See decimal and numeric for details.

|||Thank you. I looked at that page, but really couldnt figure it out but now I know.sql

Out of range datetime value error when inserting using select...union

Hi all,
I am getting this error when insert values from one table to another in
the first table the values are varchar (10). In the second they are
datetime. The format of the data is mm/dd/yyyy to be easily converted
to dates. The conversion in this case is implicit as indicated in SQL
Server documentation. Here is my query:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description
UNION
SELECT program_id + 100000, program_name, start_date, end_date,
campaign_id AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.programs
UNION
SELECT execution_id + 200000, execution_name, start_date,
end_date, program_id + 100000 AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.executions
UNION
SELECT wave_id + 300000, wave_name, start_date, end_date,
mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL AS
ListID
FROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executions
WHERE mysqlfactiva.dbo.waves.execution_id =
mysqlfactiva.dbo.executions.execution_id

I am referencing programs table two times. If I just select this all I
get all data I need. When doing insert I get a message:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. The statement has been terminated.

If I execute just first part of the query before first union, I insert
data fine:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description

As soon as I use union I get the above error. This is very strange
since even when I execute the query using first union where the dates
come from the same table 'programs' I get the error. Why I can insert
from programs first time and can's second time?

Any help will be appreciated.

Thanks,
Stan(suslikovich@.yahoo.com) writes:
> I am getting this error when insert values from one table to another in
> the first table the values are varchar (10). In the second they are
> datetime. The format of the data is mm/dd/yyyy to be easily converted
> to dates. The conversion in this case is implicit as indicated in SQL
> Server documentation. Here is my query:

Note that the interpretation is dependent on the settings for DATEFORMAT
and LANGUAGE.

> INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
> ListID)
> SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
> mysqlfactiva.dbo.campaigns.campaign_name AS Name,
> MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,
> MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
> NULL AS ParentID,
> NULL AS ListID
> FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs

So the data type of mysqlfactiva.dbo.programs.start_date is varchar(10)?

Excuse me, if I am nosy, but a MIN on that columns appears somewhat
funny to me. If there is a date in early January you will use that
date, no matter which year it is in?

Better would be

MIN (CASE WHEN isdate(start_date) = 1 THEN start_date END

> As soon as I use union I get the above error. This is very strange
> since even when I execute the query using first union where the dates
> come from the same table 'programs' I get the error. Why I can insert
> from programs first time and can's second time?

In the first query, you are only taking the first value, in the
second query you are taking all the values. This query should give
you the bad data:

SELECT * FROM mysqlfactiva.dbo.programs.start_date
WHERE isdate(start_date) = 0 OR isdate(end_date) = 0

By the way, I seem recall that MySQL has a very liberal view on what
is a good date...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Out of memory while querying causes wrong data to be returned

Running AS 2000, SP4.
A couple of times while querying a cube, one member has got the wrong data displayad (the value are to low).
No matter if I reload the query afterwards, restarting the query application etc, the same faulty value still gets displayed for that member.

After I restart analysis services the value is correct again. No other changes, exact the same query etc.
While analysing the event viewer I see that analysises services has run out of memory before this strange behaviour (MSSQLServerOLAPService - "The server is out of memory").

Could this out-of-memory error has caused the wrong value to be returned, and the somehow the value has got cached on the analysis server so it's not fixed until the server has restarted?
This is the only theory I have, and it's important to figure out the cause. Users are getting worried if they can't trust the data.

The server is dedicated to analysis services and has 3GB memory and /3GB switch enabled. Memory conservation treshold is 2700mb and Minimum allocated memory is set to 1536mb.
The query tool is an asp-application using ADOMD, but the same faulty value was returned while trying the query in MDX Sample App.

First of all try installing SP4 http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5

Second, if problem persists contact customer support and have them looking into your problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Sorry, misprint, I am already running sp 4.|||

There isn't much to recommed in this case.

Have your problem looked at by the customer support ( the doctors) :)

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

OUT and OUTPUT - Stored procedure parameters.

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET-

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @.Product varchar(40)

, @.MaxPrice money

, @.ComparePrice money OUTPUT

, @.ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;

-- Populate the output variable @.ListPprice.

SET @.ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);

-- Populate the output variable @.compareprice.

SET @.ComparePrice = @.MaxPrice;

GO

USE

DECLARE @.ComparePrice money, @.Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@.ComparePrice OUT,

@.Cost OUTPUT

IF @.Cost <= @.ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

-

Partial Result Set

-

--Product List Price

-

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

Well, OUT and OUTPUT keywords are synonyms, just as INT and INTEGER are.|||

Really?

AAAAAAAARGH it's all clear now. I can't see why in the example they mix and match however though - it just brings confusion into the equation.

No point in answering the other part of my Q: I 'm sure the example iterates throught the execution of the SP, but doesn't show it.

Thank you Sergey.

OSQL Unicode Insert of ^F (Hex 06) from Registry Multi-String Value is Duplicating in DB

I'm using a Unicode sql script imported using OSQL. One of the values
we are attempting to insert is a Registry Multi-String value by passing
a string to a stored procedure. These Multi-String values appear to be
delimited by a Hex 06 (^F) character. When I import this character,
embedded in a string preceeded by an N, i.e

N'something something2 something3'

I end up with TWO of this character in the db. I get :

something something2 something3

Any help figuring out why or how to fix this? We MUST use Unicode due
to extended character sets, so NOT using Unicode is NOT a solution.Seems to be an encoding issue. We are using SQLXML to retrieve the
data initially. That seems to be UTF-8 encoding the data which creates
an xml stream that contains an odd escape sequence. This escape
sequence is not then being translated into the correct unicode
representation in the insert scripts (which are created by transforming
the xml). We are going to try Unicode encoding on the SQLXmlCommand
object and setting the xml to encoding=UTF-16 to see what happens.

osql -b question!

the help document said that

"osql -b will specifies that OSQL exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0."

my question is : how can I get the DOS ERRORLEVEL.

example:

C:\>isql -E -b -Q"backup log pubs to disk='C:\adsfasd.tmp'"

the result:

Msg 4208, Level 16, State 0, Server YANG, Line 1
当恢复模型为 SIMPLE 时，不允许使用 BACKUP LOG 语句。请使用 BACKUP DATABASE 或用
ALTER DATABASE
更改恢复模型。
Msg 3013, Level 16, State 1, Server YANG, Line 1
BACKUP LOG 操作异常终止。

It's in chinese language,means
"can not use BACKUP LOG when the restore model being SIMPLE,you can use ALTER DATABASE or BACKUP DATABASE to change the restore model.
Msg 3013, Level 16, State 1, Server YANG, Line 1
BACKUP LOG aborted"

where is DOS ERRORLEVEL?? How can I get it??

thankstake a look at the server errorlog or the backupserver errorlog for details|||See KB#39585 (http://support.microsoft.com/default.aspx?scid=kb;en-us;39585).

-PatP