retrieve Datetime: resultset

Showing posts with label resultset. Show all posts

Wednesday, March 28, 2012

Return a resultset to XML?

Hello I am trying to pass a recordset to XML
Database: pubs
select * from titles for xml auto
but it returns me this
<titles title_id="BU1032" title="The Busy Executive's Database Guide"
type="business " pub_id="1389" price="20.0000" advance="5000.0000"
royalty="10" ytd_sales="4095" notes="An overview of available database
systems with emphasis on common business
royalty="16" ytd_sales="8780" notes="A survey of software for the naive
user, focusing on the 'friendliness' of each."
pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
Silicon Valley" type="popular_comp" pub_id="1389" pr
"psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
emotional stress in the modern world. Use of computer and nutritional aids
emphasized." pubdate="1991-06
I dont want that way
I would like more or less
<title>title1</title>
<type>type</type>
and so on.
ThanksHi
Look at the FOR EXPLICITY clause in books online and you may be able to do
what you require.
John
"Luis Esteban Valencia" wrote:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database Guide"
> type="business " pub_id="1389" price="20.0000" advance="5000.0000"
> royalty="10" ytd_sales="4095" notes="An overview of available database
> systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the naive
> user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
> Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
> ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
> emotional stress in the modern world. Use of computer and nutritional aids
> emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>|||"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database
> Guide" type="business " pub_id="1389" price="20.0000"
> advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> of available database systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the
> naive user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> loved ones from undue emotional stress in the modern world. Use of
> computer and nutritional aids emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>
select * from titles for xml auto, elements
--
Regards
JTC ^..^|||It doesnt return good. it seems to cut the strings
look at this
<titles><title_id>BU1032</title_id><title>The Busy Executive's Database
Guide</title><type>business
</type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
/price><
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> > Hello I am trying to pass a recordset to XML
> > Database: pubs
> >
> > select * from titles for xml auto
> > but it returns me this
> > <titles title_id="BU1032" title="The Busy Executive's Database
> > Guide" type="business " pub_id="1389" price="20.0000"
> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > of available database systems with emphasis on common business
> > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > naive user, focusing on the 'friendliness' of each."
> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > loved ones from undue emotional stress in the modern world. Use of
> > computer and nutritional aids emphasized." pubdate="1991-06
> >
> >
> > I dont want that way
> > I would like more or less
> > <title>title1</title>
> > <type>type</type>
> >
> > and so on.
> >
> > Thanks
> >
> >
> >
> select * from titles for xml auto, elements
> --
> Regards
> JTC ^..^|||It is only Query Analyzer. You can configure where it cut a column (max is 8000). All xml is one
column in QA.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in message
news:%23eMjeSlgFHA.3436@.tk2msftngp13.phx.gbl...
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
>> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
>> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
>> > Hello I am trying to pass a recordset to XML
>> > Database: pubs
>> >
>> > select * from titles for xml auto
>> > but it returns me this
>> > <titles title_id="BU1032" title="The Busy Executive's Database
>> > Guide" type="business " pub_id="1389" price="20.0000"
>> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
>> > of available database systems with emphasis on common business
>> > royalty="16" ytd_sales="8780" notes="A survey of software for the
>> > naive user, focusing on the 'friendliness' of each."
>> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
>> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
>> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
>> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
>> > loved ones from undue emotional stress in the modern world. Use of
>> > computer and nutritional aids emphasized." pubdate="1991-06
>> >
>> >
>> > I dont want that way
>> > I would like more or less
>> > <title>title1</title>
>> > <type>type</type>
>> >
>> > and so on.
>> >
>> > Thanks
>> >
>> >
>> >
>> select * from titles for xml auto, elements
>> --
>> Regards
>> JTC ^..^
>|||Hi
If you still have problems cutting the tags then put in dummy data values
that contain a carriage return. See http://tinyurl.com/e4rla
John
"Luis Esteban Valencia" wrote:
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribiÃ³ en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> > "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> > news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> >
> > > Hello I am trying to pass a recordset to XML
> > > Database: pubs
> > >
> > > select * from titles for xml auto
> > > but it returns me this
> > > <titles title_id="BU1032" title="The Busy Executive's Database
> > > Guide" type="business " pub_id="1389" price="20.0000"
> > > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > > of available database systems with emphasis on common business
> > > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > > naive user, focusing on the 'friendliness' of each."
> > > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > > loved ones from undue emotional stress in the modern world. Use of
> > > computer and nutritional aids emphasized." pubdate="1991-06
> > >
> > >
> > > I dont want that way
> > > I would like more or less
> > > <title>title1</title>
> > > <type>type</type>
> > >
> > > and so on.
> > >
> > > Thanks
> > >
> > >
> > >
> >
> > select * from titles for xml auto, elements
> >
> > --
> > Regards
> > JTC ^..^
>
>

Return a resultset to XML?

Return a resultset from a Stored Pro

Hello all,
I want to be able to be able to return a resultset from a Stored Procedure.

Something like :

CREATE PROCEDURE LSNOnAJob
@.MyJobNo AS INT,@.MyLsn VarChar(10) OUTPUT

AS

SELECT @.MyLsn = dbo.TSample.ISmpShortCode
FROM dbo.TJob INNER JOIN
dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN
WHERE (dbo.TJob.IJobN = @.MyJobNo)
GO

I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.

I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.would this work for you or do you need the results returned in an output variable?

CREATE PROCEDURE LSNOnAJob
@.MyJobNo AS INT
--,@.MyLsn VarChar(10) OUTPUT

AS

SELECT dbo.TSample.ISmpShortCode
FROM dbo.TJob INNER JOIN
dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN
WHERE dbo.TJob.IJobN = @.MyJobNo
GO|||I need the results returned back to an Access DB|||Just use a pass through query and EXEC the sproc...

Tuesday, March 20, 2012

Retrieving entire columns from a resultset

Hi,
I would like to know if there is anyway of retreiving an entire column of
data from a resultset into say an array or a vector.
e.g. If the resultset contains columns ID (int), Name(varchar), age(int)
then I'd like retrieve the ID values from each tuple in one go into say an
int array or a vector.
(I am using a mysql database and a mysql ODBC driver to connect to it)
Thanks,
Mithila
It sounds like you may be able to use what's called "bulk row fetching".
What language, version, libraries, etc. are you using?
In article <F6874AB5-D7C4-4B22-A675-FAA2F3039DE0@.microsoft.com>,
MithilaP@.discussions.microsoft.com says...
> I would like to know if there is anyway of retreiving an entire column of
> data from a resultset into say an array or a vector.
> e.g. If the resultset contains columns ID (int), Name(varchar), age(int)
> then I'd like retrieve the ID values from each tuple in one go into say an
> int array or a vector.
> (I am using a mysql database and a mysql ODBC driver to connect to it)
|||Hi,
I am using C++ (compatible with visual studio 6), mysql odbc driver 3.51,
mysql 4.0.12.
Thanks,
Mithila
"Scot T Brennecke" wrote:

> It sounds like you may be able to use what's called "bulk row fetching".
> What language, version, libraries, etc. are you using?
> In article <F6874AB5-D7C4-4B22-A675-FAA2F3039DE0@.microsoft.com>,
> MithilaP@.discussions.microsoft.com says...
>
|||Are you using MFC and a class derived from CRecordset to perform the
ODBC? If so, you can use the built-in support for "bulk record field
exchange" in your application. If not using MFC, you can still use the
same methodology that MFC uses and call the ODBC functions directly, if
you look at the MFC source code and the DBFETCH sample.
In article <35887A32-F785-4841-A46A-202CA49B19B3@.microsoft.com>,
MithilaP@.discussions.microsoft.com says...[vbcol=seagreen]
> Hi,
> I am using C++ (compatible with visual studio 6), mysql odbc driver 3.51,
> mysql 4.0.12.
> Thanks,
> Mithila
> "Scot T Brennecke" wrote:

Retrieving entire columns from a resultset

Hi,
I would like to know if there is anyway of retreiving an entire column of
data from a resultset into say an array or a vector.
e.g. If the resultset contains columns ID (int), Name(varchar), age(int)
then I'd like retrieve the ID values from each tuple in one go into say an
int array or a vector.
(I am using a mysql database and a mysql ODBC driver to connect to it)
Thanks,
MithilaIt sounds like you may be able to use what's called "bulk row fetching".
What language, version, libraries, etc. are you using?
In article <F6874AB5-D7C4-4B22-A675-FAA2F3039DE0@.microsoft.com>,
MithilaP@.discussions.microsoft.com says...
> I would like to know if there is anyway of retreiving an entire column of
> data from a resultset into say an array or a vector.
> e.g. If the resultset contains columns ID (int), Name(varchar), age(int)
> then I'd like retrieve the ID values from each tuple in one go into say an
> int array or a vector.
> (I am using a mysql database and a mysql ODBC driver to connect to it)|||Hi,
I am using C++ (compatible with visual studio 6), mysql odbc driver 3.51,
mysql 4.0.12.
Thanks,
Mithila
"Scot T Brennecke" wrote:

> It sounds like you may be able to use what's called "bulk row fetching".
> What language, version, libraries, etc. are you using?
> In article <F6874AB5-D7C4-4B22-A675-FAA2F3039DE0@.microsoft.com>,
> MithilaP@.discussions.microsoft.com says...
>|||Are you using MFC and a class derived from CRecordset to perform the
ODBC? If so, you can use the built-in support for "bulk record field
exchange" in your application. If not using MFC, you can still use the
same methodology that MFC uses and call the ODBC functions directly, if
you look at the MFC source code and the DBFETCH sample.
In article <35887A32-F785-4841-A46A-202CA49B19B3@.microsoft.com>,
MithilaP@.discussions.microsoft.com says...[vbcol=seagreen]
> Hi,
> I am using C++ (compatible with visual studio 6), mysql odbc driver 3.51,
> mysql 4.0.12.
> Thanks,
> Mithila
> "Scot T Brennecke" wrote:
>

Friday, March 9, 2012

Retrieving an Answer from Mulitple ResultSet statements

I have several ResultSet Querys/Statements within my page. An example
of the code looks like this:

ResultSet rs1 = stmt1.executeQuery("SELECT right('
' + '$' + convert(varchar,SUM(ActivePrim),1), 15) AS 'ActivePrim',
right(' ' + '$' + convert(varchar,SUM(KGAP),1), 15) AS
'KGAP', right(' ' + '$' +
convert(varchar,SUM(PrimaryRepo),1), 15) AS 'PrimaryRepo', right('
' + '$' + convert(varchar,SUM(WeeklyTotal),1), 15) AS
'WeeklyTotal' FROM Intranet..InsuranceStats WHERE EmployeeName =
'Jamie' and Date BETWEEN '01/01/04' and '01/31/04'");

What would be the correct way to retrieve each result set? I
currently have it as the example below. But this doesn't allow for
each result set to be displayed separately.

<td valign=top><b>Active Primary:</b><%= ActivePrim %></td
Any help would be greatly appreciated.

Catherineclequieu@.nuvell.com (Catherine) wrote in message news:<a0edee50.0404051011.1235b95c@.posting.google.com>...
> I have several ResultSet Querys/Statements within my page. An example
> of the code looks like this:
> ResultSet rs1 = stmt1.executeQuery("SELECT right('
> ' + '$' + convert(varchar,SUM(ActivePrim),1), 15) AS 'ActivePrim',
> right(' ' + '$' + convert(varchar,SUM(KGAP),1), 15) AS
> 'KGAP', right(' ' + '$' +
> convert(varchar,SUM(PrimaryRepo),1), 15) AS 'PrimaryRepo', right('
> ' + '$' + convert(varchar,SUM(WeeklyTotal),1), 15) AS
> 'WeeklyTotal' FROM Intranet..InsuranceStats WHERE EmployeeName =
> 'Jamie' and Date BETWEEN '01/01/04' and '01/31/04'");
> What would be the correct way to retrieve each result set? I
> currently have it as the example below. But this doesn't allow for
> each result set to be displayed separately.
> <td valign=top><b>Active Primary:</b><%= ActivePrim %></td>
> Any help would be greatly appreciated.
> Catherine

You'll probably get a better answer to this in an ASP forum.

Simon

Wednesday, March 7, 2012

Retrieve the version of SQL Server from within a user defined function

EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.

I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?

have you tried

select serverproperty('ProductVersion') as character_value

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can use @.@.Version within a user defined function. I tested the following code in both SQL 2000 and SQL 2005. Hope this helps.

Alter Function dbo.VersionNumber()

Returns int

Begin

Declare @.Temp VarChar(1000)

Declare @.Output Int

Select @.Temp = @.@.Version

Set @.Temp = Replace(Left(@.Temp, CharIndex('-', @.Temp)-1), 'Microsoft SQL Server', '')

If IsNumeric(@.Temp) =1

Set @.Output = Convert(int, @.Temp)

Else

Set @.Output = 0

Return @.Output

End

Select dbo.VersionNumber()

|||

serverproperty works very nicely, thanks.

I had tried @.@.VERSION, which worked, but not pleasant. Now changed to serverproperty

retrieve Datetime

Wednesday, March 28, 2012

Return a resultset to XML?

Return a resultset to XML?

Return a resultset to XML?

Return a resultset from a Stored Pro

Tuesday, March 20, 2012

Retrieving entire columns from a resultset

Retrieving entire columns from a resultset

Friday, March 9, 2012

Retrieving an Answer from Mulitple ResultSet statements

Wednesday, March 7, 2012

Retrieve the version of SQL Server from within a user defined function

Followers

Blog Archive

About Me