Return dataset in one column

Hi there

I have the following two tables

mainprofile (profile varchar(20), description)
accprofile (profile varchar(20), acct_type int)

Sample data could be

mainprofile
------
prof1 | profile one
prof2 | profile two
prof3 | profile three

accprofile
-----

prof1 | 0
prof1 | 1
prof1 | 2
prof2 | 0

Now doing a join between these two tables would return multiple rows,
but I would like to know whether it would be possible to return
acct_type horizontally in a column of the result set, e.g.

prof1 | profile one | [0,1,2]
prof2 | profile two | [0]

I could probably manage this with cursors, but it would be very
resource intensive. Is there a better way?

Regards,
LouisFor a one time data display or if this is used by a single application or a
report, you should consider retrieving the resultset to the client side,
leverage the display/presentation language's string manipulative features
and appropriately format the data there.

If this is more of a general requirement and used by several applications,
in certain cases it may make some sense to do it at the server using t-SQL.
For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx
--
Anith|||

Quote:

Originally Posted by

For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx

Thanks. In the end I decided to stick with using a CURSOR

Regards,
Louis

Return a value after insert the query

Hi!

create table testReturn
(
id int identity(100,1),
name varchar(10)
)

How can I return the value of identity column after inserting the value.

Dim objConn As SqlConnection
Dim SQLCmd As SqlClient.SqlCommand
Dim ds As New DataSet
Dim strsql As String

Try

objConn = New SqlConnection
objConn.ConnectionString = _
"Network Library=DBMSSOCN;" & _
"Data Source=localhost;" & _
"Initial Catalog=mydb;" & _
"User ID=userid;" & _
"Password=pass"

objConn.Open()

strsql = "insert into testReturn values ('a')"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)
Dim rowsAffected As Integer = 0
rowsAffected = SQLCmd.ExecuteNonQuery

Dim rv As String
rv = SQLCmd.Parameters(0).Value.ToString()
Response.Write(rv)

Catch ex As Exception
Response.Write(ex.ToString)
End Try

SeeHow do I get the IDENTITY / AUTONUMBER value for the row I inserted?

|||

strsql = "insert into testReturn values ('a'); select @.@.Identity"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)

dim Identiy as Object

Identity = SQLCmd.ExecuteScalar

|||

You need to put "Select @.@.identity" statement immediately after your insert query so it would return the ID value of the record inserted, using the preceding Insert statement.

Cheers
Ritesh

|||

Using @.@.IDENTITY is an vulnerable approach, since it returns the last inserted id of any table. If you are using SQL 2005 then you can use the OUTPUT clause.

SeeHow to get an Identity value with SQL Server 2005

|||

You can either use @.@.identity or scope_identity().

@.@.identity gives you the last generated identity value.

scope_identity() gives you the last generated identity value for current scope.

Just have a quick look at BOL for further understanding.

Hope this will help.

Return a type

Hello All!
I would like to thank everyone for all the help, but.. (there is always a
but) i have another question.
I would like in my select a column displaying if the current line is a
Company or a Person, something like that:
SELECT *, "(Company or Person) As Type" FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
I anyone could help me, i raelly would appreciate it!
thanks,
Bruno N
CREATE TABLE [Person] (
[Id] [int] NOT NULL ,
[RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Person_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
CREATE TABLE [Customer] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Company] (
[Id] [int] NOT NULL ,
[CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Company_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]SELECT *,
CASE
WHEN Client.ID = Person.ID THEN 'Person'
WHEN Company.ID = Person.ID THEN 'Company'
ELSE ''
END
FROM
...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bruno N" <nylren@.hotmail.com> wrote in message
news:u31HkjMJFHA.3136@.TK2MSFTNGP15.phx.gbl...
> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>|||SELECT *,
Case When Person.ID Is Not Null then 'Person'
When Company.ID Is Not Null then 'Company'
When Customer.ID Is Not Null then 'Customer' ENd As Type
FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
"Bruno N" wrote:

> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
>|||SELECT ...,
CASE
WHEN Person.ID IS NOT NULL THEN 'Person'
WHEN Company.ID IS NOT NULL THEN 'Company'
END, ...
You seem to be missing the alternate key on Customer Name. IDENTITY
should never be the only key of a table. Also, your constraints don't
prevent the same entity being entered as both Cutsomer and Company.
David Portas
SQL Server MVP
--

Retriving data from SQL text field

I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?Use a parameter.|||SQL QA does not allow declaring local variables with text type. I am trying to pull the SQL text filed's all the text. Declaring or converting to varchar will limit it to 8000 characters only.

An example will be very handy..

Thxsql

Retriving an xml string stored in varchar(max)

I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code

dr = cmd.ExecuteReader();_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();Label1.Text = _xmlFile;

and this is what I get "12"
Maybe I missed something to get the whole XML StringWhat do you mean by getting "12"? It confused me...|||

mehdi_tn:

I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code

dr = cmd.ExecuteReader();
_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();
Label1.Text = _xmlFile;

and this is what I get "12"
Maybe I missed something to get the whole XML String

It seems to me you could do it like this:

_xmlFile = dr["XmlJoin"].ToString();

|||Thanks for answering, In fact I placed the retrived XMl in a label and the label showed "1"
When debugin I founded the whole XML in the variable. The problem was from the label try this :

Label1.Text="<;x>1</x>";// this will show 1

Bizarre this controlsql

Retrieving XML Data

I know that I can retrieve table data in XML format, but is there a way to
retrieve data that is STORED as XML in a column as if it was relational
data?Michael Bray wrote:
> I know that I can retrieve table data in XML format, but is there a way to
> retrieve data that is STORED as XML in a column as if it was relational
> data?
Use the nodes method on the XML column, see BOL:
<URL:http://msdn2.microsoft.com/en-us/library/ms188282.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

retrieving the BigInt value from the Identity Column after inserting

I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.

How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure.

Waht is the way to achive this please?

Marc

What I do is issue the two commands (the sql insert, and the sql select scope_identity) in the same execute separated by semi colon.

the trick is to set the parameter direction to output for the identity.

David H. has a good article.

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

|||

Thank you very much. that did it!

Marc

Retrieving selected gridview column values for SQLDatasource asp:controlparameters

Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@." at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):

<

asp:GridViewID="gvLoadEditProductPrices"runat="server"AutoGenerateColumns="False"AllowSorting="True"DataSourceID="SqlDataSource1"DataKeyNames="ProductID"><Columns><asp:CommandFieldShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"HeaderStyle-BackColor="white"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="Product"HeaderText="Product"SortExpression="Product"ReadOnly="True"/><asp:BoundFieldDataField="ProductCat"HeaderText="ProductCat"SortExpression="ProductCat"ReadOnly="True"/><asp:BoundFieldDataField="VarRate"HeaderText="VarRate"SortExpression="VarRate"/><asp:BoundFieldDataField="loadid"HeaderText="loadid"InsertVisible="False"ReadOnly="True"SortExpression="loadid"/><asp:BoundFieldDataField="loadamount"HeaderText="loadamount"SortExpression="loadamount"ReadOnly="True"/><asp:BoundFieldDataField="ProductCol"HeaderText="ProductCol"SortExpression="ProductCol"ReadOnly="True"/><asp:BoundFieldDataField="PageID"HeaderText="PageID"SortExpression="PageID"ReadOnly="True"/></Columns></asp:GridView>

and the sqldatasource's info:

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>"ProviderName="System.Data.SqlClient"SelectCommand="spGetLoadEditProductPrices"SelectCommandType="StoredProcedure"UpdateCommand="spUpdateProductPrices"UpdateCommandType="StoredProcedure"><UpdateParameters><asp:ControlParameterName="ProductID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter><asp:ControlParameterName="LoadID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter><asp:ControlParameterName="PageID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter><asp:ControlParameterName="ProductCol"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter><asp:ControlParameterName="NewRate"Type="Double"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter></UpdateParameters><SelectParameters><asp:ControlParameterControlID="ddlEstLoadsPerAcre"Name="LoadID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="txtEditType"Name="PageName"PropertyName="Text"Type="String"/></SelectParameters></asp:SqlDataSource>

TIA,

John

Nevermind...after hours of testing many different combinations and scenarios, I found that I had to add each field that the control parameter needs to reference in the gridview to the "DataKeyNames" property in the gridview. But, any field I wanted to be updated thru the gridview edit had to use the PropertyName="SelectedValue" as opposed to the PropertyName="SelectedDataKey.Values('fieldname')". I also had to use single quotes for the field name.

John

Retrieving rows with minimum values within a column

I am attempting to retrieve only the rows in a table that have the minimum
value of the adr_numb column for each occurrence of the adr_code column. Whe
n
I run the below query, it still returns all the rows within the table. Any
suggestions on how to restructure this query to obtain the correct results
would be appreciated.
Select *
From dbo.addr_tbl a
Where exists (Select Min(adr_numb)
From dbo.addr_tbl b
Where a.adr_code = b.adr_code)
Current rows:
adr_code adr_numb
4M3IWNFP51 1
4M3IWNFP51 2
9UAZZRD5U1 1
C0VCLF5001 1
C0VCLF5001 2
DKZKR1ZFH1 2
F9D599KH01 1
F9D599KH01 2
F9D599KH01 3
FAPCM71YH1 2
FAPCM71YH1 3
Desired Results:
adr_code adr_numb
4M3IWNFP51 1
9UAZZRD5U1 1
C0VCLF5001 1
DKZKR1ZFH1 2
F9D599KH01 1
FAPCM71YH1 2Select * from table where adr_numb in
(select min(adr_numb) from table group by adr_code)
Madhivanan|||Select adr_code,Min(adr_numb)
From dbo.addr_tbl
group by adr_code|||and if the table has more than the columns you had shown, it should go like
this
Select * from table a where adr_numb =
(select min(adr_numb) from table b where a.adr_code = b.adr_code)
P.S: Madhivanan, Can you check your query. I feel it might give an erronous
output.|||Try,
select *
from dbo.addr_tbl as a
where not exists (
select *
from dbo.addr_tbl as b
where b.adr_code = a.adr_code and b.adr_numb < a.adr_numb
)
go
AMB
"MACason" wrote:

> I am attempting to retrieve only the rows in a table that have the minimum
> value of the adr_numb column for each occurrence of the adr_code column. W
hen
> I run the below query, it still returns all the rows within the table. Any
> suggestions on how to restructure this query to obtain the correct results
> would be appreciated.
>
> Select *
> From dbo.addr_tbl a
> Where exists (Select Min(adr_numb)
> From dbo.addr_tbl b
> Where a.adr_code = b.adr_code)
>
> Current rows:
> adr_code adr_numb
> 4M3IWNFP51 1
> 4M3IWNFP51 2
> 9UAZZRD5U1 1
> C0VCLF5001 1
> C0VCLF5001 2
> DKZKR1ZFH1 2
> F9D599KH01 1
> F9D599KH01 2
> F9D599KH01 3
> FAPCM71YH1 2
> FAPCM71YH1 3
>
>
> Desired Results:
> adr_code adr_numb
> 4M3IWNFP51 1
> 9UAZZRD5U1 1
> C0VCLF5001 1
> DKZKR1ZFH1 2
> F9D599KH01 1
> FAPCM71YH1 2
>|||You almost had it, but you need to change the exists to equals.
When you use exists it is returning all rows where adr_code exists in at
least one other row in the table with a minimum adr_numb, which is true for
all rows expect those where adr_numb is null.
If you use equals, along with the "join" that you already have in your
subquery, you will only get back rows where the adr_numb is the minimum
value for each adr_code.
Select *
From dbo.addr_tbl a
Where adr_numb = (Select Min(b.adr_numb)
From dbo.addr_tbl b
Where a.adr_code = b.adr_code)
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:0B11BBE1-7345-45FD-9B82-7D2670596C69@.microsoft.com...
> I am attempting to retrieve only the rows in a table that have the minimum
> value of the adr_numb column for each occurrence of the adr_code column.
When
> I run the below query, it still returns all the rows within the table. Any
> suggestions on how to restructure this query to obtain the correct results
> would be appreciated.
>
> Select *
> From dbo.addr_tbl a
> Where exists (Select Min(adr_numb)
> From dbo.addr_tbl b
> Where a.adr_code = b.adr_code)
>
> Current rows:
> adr_code adr_numb
> 4M3IWNFP51 1
> 4M3IWNFP51 2
> 9UAZZRD5U1 1
> C0VCLF5001 1
> C0VCLF5001 2
> DKZKR1ZFH1 2
> F9D599KH01 1
> F9D599KH01 2
> F9D599KH01 3
> FAPCM71YH1 2
> FAPCM71YH1 3
>
>
> Desired Results:
> adr_code adr_numb
> 4M3IWNFP51 1
> 9UAZZRD5U1 1
> C0VCLF5001 1
> DKZKR1ZFH1 2
> F9D599KH01 1
> FAPCM71YH1 2
>|||Thanks, Jim. Worked great. Should have posted sooner as I have been trying t
o
resolve this for the last couple days.
"Jim Underwood" wrote:

> You almost had it, but you need to change the exists to equals.
> When you use exists it is returning all rows where adr_code exists in at
> least one other row in the table with a minimum adr_numb, which is true fo
r
> all rows expect those where adr_numb is null.
> If you use equals, along with the "join" that you already have in your
> subquery, you will only get back rows where the adr_numb is the minimum
> value for each adr_code.
> Select *
> From dbo.addr_tbl a
> Where adr_numb = (Select Min(b.adr_numb)
> From dbo.addr_tbl b
> Where a.adr_code = b.adr_code)
> "MACason" <MACason@.discussions.microsoft.com> wrote in message
> news:0B11BBE1-7345-45FD-9B82-7D2670596C69@.microsoft.com...
> When
>
>|||Thanks Omnibuzz
Madhivanan

Retrieving ntext column value skips values.

I have the unfortunate task of dealing with an ntext column. I have to updat
e
part of the contents but first I was just trying to display the contents in
Query Analyzer using a script from page 61-62 of the Guru's transact sql
book. Well, the script does print out a few characters, skip a few, print a
few, skip a few, . . . It appears that accessing an ntext column is quite
different than accessing a text column. BOL is not very helpful on this.
Also, READTEXT only displays a few characters at a time no matter what the
chunk size is set to, so I can't tell if the problem is Query analyzer or
something else. Does anyone have a source for useful info in dealing with
ntext?
thanks,
MichaelSnake wrote:
> I have the unfortunate task of dealing with an ntext column. I have
> to update part of the contents but first I was just trying to display
> the contents in Query Analyzer using a script from page 61-62 of the
> Guru's transact sql book. Well, the script does print out a few
> characters, skip a few, print a few, skip a few, . . . It appears
> that accessing an ntext column is quite different than accessing a
> text column. BOL is not very helpful on this. Also, READTEXT only
> displays a few characters at a time no matter what the chunk size is
> set to, so I can't tell if the problem is Query analyzer or something
> else. Does anyone have a source for useful info in dealing with
> ntext?
> thanks,
> Michael
Can you post the code you are running. There shouldn't be a problem
reading the data from an ntext column.
I tried a test using the pubs.pub_info table which I recreated as
pub_info2 using an ntext column. QA does have a display setting for the
max number of character per column to display. Or it could be that there
are line breaks in the ntext that are not displaying correctly in the QA
grid. Try using text output and see if that helps.
create table dbo.pub_info2 (pub_id char(4) not null, logo image null,
pr_info ntext)
go
ALTER TABLE [dbo].[pub_info2] ADD CONSTRAINT [UPKCL_pubinfo2] PRIMARY
KEY CLUSTERED
(
[pub_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pub_info2] ADD FOREIGN KEY
(
[pub_id]
) REFERENCES [publishers] (
[pub_id]
)
GO
insert into pub_info2 select * from pub_info
go
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info2 pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
select @.ptrval
READTEXT pub_info2.pr_info @.ptrval 0 25
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com|||>I have the unfortunate task of dealing with an ntext column. I have to
>update
> part of the contents but first I was just trying to display the contents
> in
> Query Analyzer
See http://www.aspfaq.com/2445 for some help on using UPDATETEXT.
I don't think you will need READTEXT to do what you want, see the following
(note though that it will create bogus carriage returns every 4000th
character, but existing control characters (CHAR(10,13,9 etc)) will still be
displayed correctly):
CREATE TABLE data
(
id INT UNIQUE,
txt TEXT
)
GO
SET NOCOUNT ON
DECLARE @.foo NVARCHAR(4000)
SELECT @.foo = REPLICATE('a',4000)
-- make one far > 4000 and a small one
EXEC('INSERT data(id,txt) SELECT 1,N'''+@.foo+@.foo+@.foo+@.foo+@.foo+'''')
INSERT data(id,txt) SELECT 2,N'foobar'
DECLARE
@.dLen INT,
@.nRows INT,
@.i INT,
@.rowID INT,
@.curLine NVARCHAR(4000)
SET @.rowID = 1 -- change this to see the other result
SELECT
@.i = 0,
@.dLen = DATALENGTH(txt),
@.nRows = (@.dLen / 4000) + 1
FROM data WHERE id=@.rowID
WHILE @.i < @.nRows
BEGIN
SELECT @.curLine = SUBSTRING(txt, (4000*@.i)+1, 4000) FROM data WHERE id =
@.rowID
PRINT @.curLine
SET @.i = @.i + 1
END
DROP TABLE data

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:
>

Retrieving data types is slow

Hello,

i'm using SMO to retrieve information from various databases. It works
well except for one thing. When I call the Column.DataType property to
get the SQL type of a column it takes a very long tine. I have a few
databases, each with some tables. There are about 75 columns I think.
If I just browse all columns with SMO and write each name if it not a
system one, it takes about 15 seconds (pretty much anyway). If I also retrieve the data type
it takes about one minute. Any ideas why? The server is a local one.

Thanksstarted reading Michiel's articles. The issue will be fixed shortly I think. Thanks anyway.

Retrieving Column Name and Value for each row using GetSchemaTable

I know I can iterate through the schema table using the following.

Can I grab the actual value of each field while looping through the rows and columns?

schemaTable = reader.GetSchemaTable();

foreach (DataRow myDataRow in schemaTable.Rows)

{

foreach (DataColumn myDataColumn in schemaTable.Columns)

{

Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn.ColumnName].ToString() );

}

}

An example is if one of the columns in the schema is called Firstname I would like to return:

Row 1

column name = Firstname

value= Bob

column name = Lastname

value= Smith

Row 2

column name = Firstname

value= Greg

column name = Lastname

value= Jones

What about:

int i=0;

foreach (DataRow myDataRow in schemaTable.Rows)

{

Console.WriteLine(string.Format("Row {0}", (string)(i++));

foreach (DataColumn myDataColumn in schemaTable.Columns)

{

Console.WriteLine(string.Format("Column Name = {0}",myDataColumn.ColumnName);

Console.WriteLine(string.Format("value = {0}" , myDataRow[myDataColumn.ColumnName].ToString());

}

}

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks this works for me.

Retrieving by months only

I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.

Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.SELECT * FROM yourTable WHERE
MONTH(ColumnName) = 9

Retrieving and Combining XML

I have a table in a SQL Server 2000 db that contains xml in one column.
I'd like to retrieve the xml from several records (10,000 actually) and
wrap them up into one xml document like this:
content of xmlData column:
<foo>
..
</foo>
Desired output:
<bar>
<foo>
..
</foo>
<foo>
..
</foo>
<foo>
..
</foo>
</bar>
I tried using FOR XML EXPLICIT, but that parsed all my tags to < and
>. How can I preserve the stored xml and output a single xml file
containing the stored xml for multiple records?
thanks
-ivan.FOR XML EXPLICIT was a good try. But you will need the !xml directive in
your column alias.
Eg,
select ... , xmlData as "element!1!row!xml" ... FOR XML EXPLICIT.
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns96FBA3DBC554BnewsNOSPAMgilly3com
@.207.46.248.16...
>I have a table in a SQL Server 2000 db that contains xml in one column.
> I'd like to retrieve the xml from several records (10,000 actually) and
> wrap them up into one xml document like this:
> content of xmlData column:
> <foo>
> ...
> </foo>
> Desired output:
> <bar>
> <foo>
> ...
> </foo>
> <foo>
> ...
> </foo>
> <foo>
> ...
> </foo>
> </bar>
> I tried using FOR XML EXPLICIT, but that parsed all my tags to < and
> >. How can I preserve the stored xml and output a single xml file
> containing the stored xml for multiple records?
> thanks
> -ivan.|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in
news:uZgb5Zq2FHA.3880@.TK2MSFTNGP12.phx.gbl:

> FOR XML EXPLICIT was a good try. But you will need the !xml directive
in
> your column alias.
> Eg,
> select ... , xmlData as "element!1!row!xml" ... FOR XML EXPLICIT.
> Best regards
> Michael
Thanks, that fixes my formatting problem, but I still had trouble
getting each record under a common root node.
My sql looked like this:
select
1 tag,
null parent,
[xmlData] [xRoot!1!xElement!xml]
from xmlTable
for xml explicit
this gave each record two parent nodes like this with no common root
node:
<xRoot>
<xElement>
<foo>
..
</foo>
</xElement>
</xRoot>
<xRoot>
<xElement>
<foo>
..
</foo>
</xElement>
</xRoot>
I want one parent node, and for that node to be the root of all the
records. I managed to make it work by adding a parent node in my
select, and eliminating extra nodes by using !xmltext, instead of !xml
like this:
select
1 tag,
null parent,
null [xRoot!1!!xmltext],
null [foo!2!!xmltext]
union all
select 2,
1,
null,
[xmlData]
from xmlTable
for xml explicit
This works, but it seems like a bit of a hack. Is there a more elegant
solution? If not, I'll just be happy this works as well as it does.
thanks
-ivan.|||In SQL Server 2005, you can use ROOT('myRoot') in the FOR XML clause.
In SQL Server 2000, your workaround works. Alternatively, there is a root
property on your connection that you can set in ADO, OLEDB, ADO.Net to get
the root element added on the client.
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns96FCA5F95596AnewsNOSPAMgilly3com
@.207.46.248.16...
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in
> news:uZgb5Zq2FHA.3880@.TK2MSFTNGP12.phx.gbl:
>
> in
> Thanks, that fixes my formatting problem, but I still had trouble
> getting each record under a common root node.
> My sql looked like this:
> select
> 1 tag,
> null parent,
> [xmlData] [xRoot!1!xElement!xml]
> from xmlTable
> for xml explicit
> this gave each record two parent nodes like this with no common root
> node:
> <xRoot>
> <xElement>
> <foo>
> ...
> </foo>
> </xElement>
> </xRoot>
> <xRoot>
> <xElement>
> <foo>
> ...
> </foo>
> </xElement>
> </xRoot>
> I want one parent node, and for that node to be the root of all the
> records. I managed to make it work by adding a parent node in my
> select, and eliminating extra nodes by using !xmltext, instead of !xml
> like this:
>
> select
> 1 tag,
> null parent,
> null [xRoot!1!!xmltext],
> null [foo!2!!xmltext]
> union all
> select 2,
> 1,
> null,
> [xmlData]
> from xmlTable
> for xml explicit
> This works, but it seems like a bit of a hack. Is there a more elegant
> solution? If not, I'll just be happy this works as well as it does.
> thanks
> -ivan.

Retrieving and Combining XML

I have a table in a SQL Server 2000 db that contains xml in one column.
I'd like to retrieve the xml from several records (10,000 actually) and
wrap them up into one xml document like this:
content of xmlData column:
<foo>
...
</foo>
Desired output:
<bar>
<foo>
...
</foo>
<foo>
...
</foo>
<foo>
...
</foo>
</bar>
I tried using FOR XML EXPLICIT, but that parsed all my tags to < and
>. How can I preserve the stored xml and output a single xml file
containing the stored xml for multiple records?
thanks
-ivan.
FOR XML EXPLICIT was a good try. But you will need the !xml directive in
your column alias.
Eg,
select ... , xmlData as "element!1!row!xml" ... FOR XML EXPLICIT.
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns96FBA3DBC554BnewsNOSPAMgilly3com@.207.46.24 8.16...
>I have a table in a SQL Server 2000 db that contains xml in one column.
> I'd like to retrieve the xml from several records (10,000 actually) and
> wrap them up into one xml document like this:
> content of xmlData column:
> <foo>
> ...
> </foo>
> Desired output:
> <bar>
> <foo>
> ...
> </foo>
> <foo>
> ...
> </foo>
> <foo>
> ...
> </foo>
> </bar>
> I tried using FOR XML EXPLICIT, but that parsed all my tags to < and
> >. How can I preserve the stored xml and output a single xml file
> containing the stored xml for multiple records?
> thanks
> -ivan.
|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in
news:uZgb5Zq2FHA.3880@.TK2MSFTNGP12.phx.gbl:

> FOR XML EXPLICIT was a good try. But you will need the !xml directive
in
> your column alias.
> Eg,
> select ... , xmlData as "element!1!row!xml" ... FOR XML EXPLICIT.
> Best regards
> Michael
Thanks, that fixes my formatting problem, but I still had trouble
getting each record under a common root node.
My sql looked like this:
select
1 tag,
null parent,
[xmlData] [xRoot!1!xElement!xml]
from xmlTable
for xml explicit
this gave each record two parent nodes like this with no common root
node:
<xRoot>
<xElement>
<foo>
...
</foo>
</xElement>
</xRoot>
<xRoot>
<xElement>
<foo>
...
</foo>
</xElement>
</xRoot>
I want one parent node, and for that node to be the root of all the
records. I managed to make it work by adding a parent node in my
select, and eliminating extra nodes by using !xmltext, instead of !xml
like this:
select
1 tag,
null parent,
null [xRoot!1!!xmltext],
null [foo!2!!xmltext]
union all
select 2,
1,
null,
[xmlData]
from xmlTable
for xml explicit
This works, but it seems like a bit of a hack. Is there a more elegant
solution? If not, I'll just be happy this works as well as it does.
thanks
-ivan.
|||In SQL Server 2005, you can use ROOT('myRoot') in the FOR XML clause.
In SQL Server 2000, your workaround works. Alternatively, there is a root
property on your connection that you can set in ADO, OLEDB, ADO.Net to get
the root element added on the client.
Best regards
Michael
"gilly3" <news@.NOSPAMgilly3.com> wrote in message
news:Xns96FCA5F95596AnewsNOSPAMgilly3com@.207.46.24 8.16...
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in
> news:uZgb5Zq2FHA.3880@.TK2MSFTNGP12.phx.gbl:
> in
> Thanks, that fixes my formatting problem, but I still had trouble
> getting each record under a common root node.
> My sql looked like this:
> select
> 1 tag,
> null parent,
> [xmlData] [xRoot!1!xElement!xml]
> from xmlTable
> for xml explicit
> this gave each record two parent nodes like this with no common root
> node:
> <xRoot>
> <xElement>
> <foo>
> ...
> </foo>
> </xElement>
> </xRoot>
> <xRoot>
> <xElement>
> <foo>
> ...
> </foo>
> </xElement>
> </xRoot>
> I want one parent node, and for that node to be the root of all the
> records. I managed to make it work by adding a parent node in my
> select, and eliminating extra nodes by using !xmltext, instead of !xml
> like this:
>
> select
> 1 tag,
> null parent,
> null [xRoot!1!!xmltext],
> null [foo!2!!xmltext]
> union all
> select 2,
> 1,
> null,
> [xmlData]
> from xmlTable
> for xml explicit
> This works, but it seems like a bit of a hack. Is there a more elegant
> solution? If not, I'll just be happy this works as well as it does.
> thanks
> -ivan.

Retrieving a datetime with a time of midnight (from a typical datetime)

Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.

A frequent method used is to (1) convert it to varchar using CONVERT with the 101 flavor and then (2) re-convert it back to datetime. Here are some examples:

Code Snippet

select convert(datetime, convert(varchar, getdate(), 101))
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select dateadd(day, datediff (day, 0, getdate()), 0)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select cast(floor(cast(getdate() as float)) as datetime)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

|||

Another way:

Code Snippet

select dateadd(d, datediff(d,0,[PostedDate]),0)

|||I used the dateadd method both of you suggested and it worked perfectly. Thank you very much.

retrieve special column with Sql Full Text Search

hi,
I'm using SQL Server to store files in image column (blob).
Previuosly I Used 'Index Server' with FileSystem To Search text in the files
by doing this i could retrieve special column like DocAuthor,Content, ...
I didn't find a way to do the same in SQL Server Full Text Search to
retrieve the
abstract of the file.
Is there a way to do this and extend the query to more "in file" data?
Any help would be greatly appreciated.
Nissim L
This feature is rumored to ship in SQL 2005. It is not present in SQL 2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nissiml" <nissiml@.discussions.microsoft.com> wrote in message
news:BBA52221-EAEF-428D-BAB1-D61AAE21AE65@.microsoft.com...
> hi,
> I'm using SQL Server to store files in image column (blob).
> Previuosly I Used 'Index Server' with FileSystem To Search text in the
files
> by doing this i could retrieve special column like DocAuthor,Content, ...
> I didn't find a way to do the same in SQL Server Full Text Search to
> retrieve the
> abstract of the file.
> Is there a way to do this and extend the query to more "in file" data?
> Any help would be greatly appreciated.
> Nissim L
>

Retrieve Recordsets alogn with column name

Hai ,
I want to get the column names along with the rows of a table as a record
set is that possible?
Thanks,
V.BoomesshWhat programming language ?
Jens Suessmeyer.
"Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
> Hai ,
> I want to get the column names along with the rows of a table as a record
> set is that possible?
> Thanks,
> V.Boomessh|||Boomessh,
Try this and HTH.Otherwise please provide ddl and sample data.
set nocount on
create table products(Product char(2),Num_Accounts int)
insert products values('P1',20)
insert products values('P2',21)
insert products values('P3',34)
insert products values('P4',56)
insert products values('P5',12)
select col1='product',product,col2='num_account
s',num_accounts from products
drop table products
"Jens Sü?meyer" wrote:

> What programming language ?
> Jens Suessmeyer.
> "Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
>
>|||Hai,
the stated query returns like
product P1 num_accounts 20
so there are 5 products, what i need is, like this...
products, num_accounts
p1 20
p2 21
p3...
..
..
So i need 6 (5 actual values + 1 coulmn name)rows along with the column name
.
i am using VB as my programming language.
Thanks,
V.Boomessh
"ZULFIQAR SYED" wrote:
> Boomessh,
> Try this and HTH.Otherwise please provide ddl and sample data.
> set nocount on
> create table products(Product char(2),Num_Accounts int)
> insert products values('P1',20)
> insert products values('P2',21)
> insert products values('P3',34)
> insert products values('P4',56)
> insert products values('P5',12)
> select col1='product',product,col2='num_account
s',num_accounts from produc
ts
> drop table products
> "Jens Sü?meyer" wrote:
>|||I am doing a program in VB. or is it directly possible in SQL by any SP etc.
.
"Jens Sü?meyer" wrote:

> What programming language ?
> Jens Suessmeyer.
> "Boomessh" <Boomessh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AFEB1A56-62BE-46E6-AF80-CEE2FA75570D@.microsoft.com...
>
>|||It shouldn't be necessary to get the column names back as a record as you
already have them if you are using VB6 or VB.NET. If you are using VB6 and A
DO,
then use the recordset object to cycle through the Fields collection retriev
ing
the name. If you are using VB.NET, then cycle through the Columns collection
on
the DataSet/DataTable to get the field names.
Thomas
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:F6A470AA-2FF8-4E33-B5D3-58668FBB14C1@.microsoft.com...
>I am doing a program in VB. or is it directly possible in SQL by any SP etc
.
> "Jens Smeyer" wrote:
>