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 data from 2 views by the value in a textbox

i have a textbox which a user enters a numeric value

i want it to use SqlDataSource and check if the value exists in any of the tables.

in my text box the users would enter starting from '100000' or '200000'

i want it to check the view that starts the # with '100000' and 2ed view starts '200000'

With this i can check in one of the tables and make the selection.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"

SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber)">

<SelectParameters>

<asp:ControlParameterControlID="txtReport"Name="ReportNumber"PropertyName="Text"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

How can i make this possible ?

i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?

You'll need to do a conditional if in your select command:

IF @.ReportNumber > 10000 BEGINSELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber) END ELSE BEGINSELECT [ReportNumber] FROM [AppraisalSummaryTHEOTHERTABLE] WHERE ([ReportNumber] = @.ReportNumber) END

Retrieving XML from a TEXT field.

(SQL Server 2000, SP3)
Hello all!
I *know* that this question has probably been asked and answered a number of times, but I
don't think I crafted the appropriate search terms for Google and sqlxml.org. Please bear
with me.
If I have a relatively simple, but large, XML document stored as a TEXT column in a table,
is there any easy way to take all table rows (of which there aren't many) and somehow call
OPENXML with that TEXT column to process the XML documents therein, and essentially
produce a "native" SQL Server result set based on that XML?
Thanks for any help you can provide!
John PetersonThanks for the help, Michael!
Out of curiosity, do you have a pointer to a sample of your Proposal #1? That sounds like
an interesting approach (though, perhaps "expensive" -- but this would be for a function
that's not heavily used in our system).
Thanks for any additional help you can provide!
John Peterson
"SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in message
news:eYhTw%23SVDHA.2344@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2000 has the limitation that you cannot define variables of type
> TEXT/NTEXT. There are two expensive workarounds, one of which may not
> continue to work in Yukon (although in Yukon you do not need that workaround
> anymore):
> 1. Pass the data out of the server and back in as a stored proc parameter of
> type NTEXT/TEXT using the sp_OA stored procs.
> 2. Use nested EXECUTEs and a cursor to copy the XML into a string literal
> argument to sp_xml_preparedocument.
> The second one is the one that may not work anymore.
> Best regards
> Michael
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > I *know* that this question has probably been asked and answered a number
> of times, but I
> > don't think I crafted the appropriate search terms for Google and
> sqlxml.org. Please bear
> > with me.
> >
> > If I have a relatively simple, but large, XML document stored as a TEXT
> column in a table,
> > is there any easy way to take all table rows (of which there aren't many)
> and somehow call
> > OPENXML with that TEXT column to process the XML documents therein, and
> essentially
> > produce a "native" SQL Server result set based on that XML?
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||I dont know any easy way...
The way I solve it was to call a DTS that pases the text data to a stored
procedure as a parameter.
"John Peterson" <j0hnp@.comcast.net> escribió en el mensaje
news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> I *know* that this question has probably been asked and answered a number
of times, but I
> don't think I crafted the appropriate search terms for Google and
sqlxml.org. Please bear
> with me.
> If I have a relatively simple, but large, XML document stored as a TEXT
column in a table,
> is there any easy way to take all table rows (of which there aren't many)
and somehow call
> OPENXML with that TEXT column to process the XML documents therein, and
essentially
> produce a "native" SQL Server result set based on that XML?
> Thanks for any help you can provide!
> John Peterson
>|||Since I had a hard-drive fail on me that contained my sample data, I
currently do not have a sample. Sorry.
The HD is in recovery and if I get the data back (and I remember this thread
after my summer vacation), I will check...
Maybe somebody else has some examples (see also other related threads such
as "Text in Stored Procedure").
Best regards
Michael
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23V9l7CWVDHA.2040@.TK2MSFTNGP10.phx.gbl...
> Thanks for the help, Michael!
> Out of curiosity, do you have a pointer to a sample of your Proposal #1?
That sounds like
> an interesting approach (though, perhaps "expensive" -- but this would be
for a function
> that's not heavily used in our system).
> Thanks for any additional help you can provide!
> John Peterson
>
> "SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in
message
> news:eYhTw%23SVDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > SQL Server 2000 has the limitation that you cannot define variables of
type
> > TEXT/NTEXT. There are two expensive workarounds, one of which may not
> > continue to work in Yukon (although in Yukon you do not need that
workaround
> > anymore):
> >
> > 1. Pass the data out of the server and back in as a stored proc
parameter of
> > type NTEXT/TEXT using the sp_OA stored procs.
> > 2. Use nested EXECUTEs and a cursor to copy the XML into a string
literal
> > argument to sp_xml_preparedocument.
> >
> > The second one is the one that may not work anymore.
> >
> > Best regards
> > Michael
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > (SQL Server 2000, SP3)
> > >
> > > Hello all!
> > >
> > > I *know* that this question has probably been asked and answered a
number
> > of times, but I
> > > don't think I crafted the appropriate search terms for Google and
> > sqlxml.org. Please bear
> > > with me.
> > >
> > > If I have a relatively simple, but large, XML document stored as a
TEXT
> > column in a table,
> > > is there any easy way to take all table rows (of which there aren't
many)
> > and somehow call
> > > OPENXML with that TEXT column to process the XML documents therein,
and
> > essentially
> > > produce a "native" SQL Server result set based on that XML?
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>

retrieving long texts from msqql

When I SELECT text FROM mytable, I don't retrieve all the data in that
column. When I take a look directly inside the database, I can see
that there exists more data than I get when running the select query.
How can I get all the data retrieved?

obscurrHi,

If you're using Query Analyzer, it truncates output at 255 characters.
Don't worry about it, the output is all there. Tricky microsofties.
-- Louis|||But if I want to put it in a textarea on my php page, the whole thing
doesn't show. How am I able to do that?

obscurr

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0311220909.67236875@.posting.google.com>...
> Hi,
> If you're using Query Analyzer, it truncates output at 255 characters.
> Don't worry about it, the output is all there. Tricky microsofties.
> -- Louis

If that is your issue, you can increase the number of characters
displayed in QA, to a maximum of 8192 (Tools - Options - Results).

Simon

retrieving data from SQL2000

I am trying to retrieve data that has been inserted into the database but only half the text is being displayed.
The field is varchar(8000)

In the Enterprise Manager I can view the data, then I copy and paste it into MS Word and everything is there.
851 Characters with spaces.

However, when I enter the SQL code into SQL Query Analyzer only half the text shows, about 257 characters are displayed with spaces.

When I display the information on the webpage only half the text is displayed (running IIS/PHP 5)

I don't know why this is doing this and not sure what I can do to display the full text.

Please help.

This is Query Analyser setting...

In QA window go to Tools -- Options -- Select "Result" tab -- Change "Maximum Charater Per Column " to whatever setting u want

Madhu

|||Thanks, this resolves the first part of my question however, when I display the information on the web it is still being truncated. If there are any ideas that would be great. Thanks!|||

check the variable size if any ... it may be used as Varchar (4000)

Madhu

Retrieving data from a plain file

Dear all,
I'd need to retrieve data from a text file located in a folder from a SELECT
statement. How could I do such thing? Is it possible obtain all the data and
then show them on the screen or better, import them to a SQL table'
Best regards,
EnricIt is better to import the file using BCP/BULK INSERT/DTS etc. if you need
that data in various queries.
Otherwise, you can directly query the text file data too. Here are some
examples:
http://www.users.drew.edu/skass/sql/TextDriver.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:B53B3A9D-8DDC-434B-AEF9-19F69D3B4DA4@.microsoft.com...
Dear all,
I'd need to retrieve data from a text file located in a folder from a SELECT
statement. How could I do such thing? Is it possible obtain all the data and
then show them on the screen or better, import them to a SQL table'
Best regards,
Enric|||Create a linked server to the text file and use a Schema.ini file to describ
e
the structure of the data:
http://msdn.microsoft.com/library/d...r />
_6a44.asp
http://msdn.microsoft.com/library/d...ma_ini_file.asp
This enables you to query the text file directly, as if it were a SQL table.
ML|||If your ADO.NET application needs to display records from a text file, then
you can just open the file directly using the Microsoft Text Driver. There
is no need to go through SQL Server.
Dim cn As New OdbcConnection("Driver={Micros_oft Text Driver
(*.txt;*. csv)};DefaultDir=d:\websites_\jpg\dataim
port;")
Dim cmd As New OdbcCommand ("select * From cfr.csv")
If you need the data in SQL Server, then you can use DTS to import it.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:B53B3A9D-8DDC-434B-AEF9-19F69D3B4DA4@.microsoft.com...
> Dear all,
> I'd need to retrieve data from a text file located in a folder from a
> SELECT
> statement. How could I do such thing? Is it possible obtain all the data
> and
> then show them on the screen or better, import them to a SQL table'
> Best regards,
> Enric

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 from db then write to text file

basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.

i read a thread here which is similar to what i am trying to do but it was in xml format..

hope someone anwers me...i really need help!

thanks!

I'm not sure what the difference is between the thread you mentioned and what you want to do. I would expect that the challenging thing is to retrieve the fields that were just saved. Once you have the information, changing it to the appropriate format is potentially tedious, but not difficult.

Do you have a reference to the thread?

Retrieve data from text field containing XML data

I have the following data in a SQL2k data field:
<History data="<?xml version="1.0" encoding="utf-16"?>
<ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<column id="Availability" v="U" o="A" />
<column id="LastModificationAgentID" v="59930" />
</ColumnsList>"/>
I'm trying to retrieve the data with Query Analyzer using the following quer
y:
SELECT *
FROM OPENXML(Column, N'/ColumnsList/Column')
WITH (ID varchar(40), v varchar(100), o varchar(100))
Am I at all on the right track with this? This is my first attempt at
working with XML and SQL together.
Thanks in advance.I am a bit . Your XML is not a well-formed XML document.
Also, I would recommend that you look at OpenXML samples in Books Online.
Basically, OpenXML uses a handle to refer to the XML document that it gets
from sp_xml_preparedocument (which parses the XML) and needs to be released
by sp_xml_removedocument.
If your XML data in the column is larger than 8k, you will have an
implementation restriction in SQL Server 2000 on variables not allowing
NTEXT/TEXT types. This makes it harder to use OpenXML on already stored XML
data (I recommend to use OpenXML on the data when it is being passed to a
stored proc before it gets stored in a table).
I would also recommend to look at SQL Server 2005, that have much better
support for your scenario (look at the XML datatype, XQuery and the nodes()
method).
Best regards
Michael
"JAdams" <JAdams@.discussions.microsoft.com> wrote in message
news:E8F3E463-2071-41EA-91E1-49E9E5D39510@.microsoft.com...
>I have the following data in a SQL2k data field:
> <History data="<?xml version="1.0" encoding="utf-16"?>
> <ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <column id="Availability" v="U" o="A" />
> <column id="LastModificationAgentID" v="59930" />
> </ColumnsList>"/>
> I'm trying to retrieve the data with Query Analyzer using the following
> query:
> SELECT *
> FROM OPENXML(Column, N'/ColumnsList/Column')
> WITH (ID varchar(40), v varchar(100), o varchar(100))
>
> Am I at all on the right track with this? This is my first attempt at
> working with XML and SQL together.
> Thanks in advance.
>

Retrieve data from text field containing XML data

I have the following data in a SQL2k data field:
<History data="<?xml version="1.0" encoding="utf-16"?>
<ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<column id="Availability" v="U" o="A" />
<column id="LastModificationAgentID" v="59930" />
</ColumnsList>"/>
I'm trying to retrieve the data with Query Analyzer using the following query:
SELECT *
FROM OPENXML(Column, N'/ColumnsList/Column')
WITH (ID varchar(40), v varchar(100), o varchar(100))
Am I at all on the right track with this? This is my first attempt at
working with XML and SQL together.
Thanks in advance.
I am a bit confused. Your XML is not a well-formed XML document.
Also, I would recommend that you look at OpenXML samples in Books Online.
Basically, OpenXML uses a handle to refer to the XML document that it gets
from sp_xml_preparedocument (which parses the XML) and needs to be released
by sp_xml_removedocument.
If your XML data in the column is larger than 8k, you will have an
implementation restriction in SQL Server 2000 on variables not allowing
NTEXT/TEXT types. This makes it harder to use OpenXML on already stored XML
data (I recommend to use OpenXML on the data when it is being passed to a
stored proc before it gets stored in a table).
I would also recommend to look at SQL Server 2005, that have much better
support for your scenario (look at the XML datatype, XQuery and the nodes()
method).
Best regards
Michael
"JAdams" <JAdams@.discussions.microsoft.com> wrote in message
news:E8F3E463-2071-41EA-91E1-49E9E5D39510@.microsoft.com...
>I have the following data in a SQL2k data field:
> <History data="<?xml version="1.0" encoding="utf-16"?>
> <ColumnsList xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <column id="Availability" v="U" o="A" />
> <column id="LastModificationAgentID" v="59930" />
> </ColumnsList>"/>
> I'm trying to retrieve the data with Query Analyzer using the following
> query:
> SELECT *
> FROM OPENXML(Column, N'/ColumnsList/Column')
> WITH (ID varchar(40), v varchar(100), o varchar(100))
>
> Am I at all on the right track with this? This is my first attempt at
> working with XML and SQL together.
> Thanks in advance.
>

Retrieve BLOB from SQL Server

I have a table in a SQL Server database that contains a field(data type image) that contains a text file. I'm trying to retrieve this file and save it onto the hard drive. I'm using the code below, but get I get the error 'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.' at line 11. This code worked fine when I tested it on the pubs database and exporting the logo field out of pub_info. I'm not quite sure why this doesn't work for my database. Can anyone see where I'm going wrong?

1 Dim cn As ADODB.Connection
2 Dim rs As ADODB.Recordset
3 Dim mstream As ADODB.Stream

4 cn = New ADODB.Connection
5 cn.Open("Provider=SQLOLEDB;data Source=server;Initial Catalog=database;User Id='userid';Password='password'")

6 rs = New ADODB.Recordset
7 rs.Open("Select * from filesubmissions where bundleId = 'F0000014.bun'", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

8 mstream = New ADODB.Stream
9 mstream.Type = ADODB.StreamTypeEnum.adTypeBinary
10 mstream.Open()
11 mstream.Write(rs.Fields("BLOB").Value)
12 mstream.SaveToFile("c:\export.txt", ADODB.SaveOptionsEnum.adSaveCreateOverWrite)

13 rs.Close()
14 cn.Close()

Thanks

May I suggest you start by using ado.net?

|||

try this one...

Dim BlobQuery As String
Dim RG As New Random
Dim InvoiceCSVData As Byte()
Dim strFileName As String = "TestingCSV_" + TextBox1.Text + "_" + CStr(RG.Next(1, 100000)) + ".CSV"

BlobQuery = "select BLOB from YourTableName where Condition'"
myOraConnection.Open()
myOraCmd = New OracleCommand(BlobQuery, myOraConnection)
myDR = myOraCmd.ExecuteReader(CommandBehavior.SequentialAccess)
myDR.Read()
InvoiceCSVData = CType(myDR(0), Byte())
myDR.Close()
myOraConnection.Close()

Response.AddHeader("Content-disposition", _
"attachment; filename=" & strFileName)
Response.ContentType = "application/octet-stream"
Response.BinaryWrite(InvoiceCSVData)
Response.End()

Retrieve Bit type value

I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code

Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)

I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?You're not selecting what you think you're selecting. You will get a 1 or 0 back, not S00817. Do any of the fields in the record have that value? If you miss a comma in your field list, you can get goofy-looking, unexpected data. Also, when the bit comes back, you can feed it right into boolean properties. You don't have to change 1 into true, for example. Pretty handy. I'd run your SQL statement in Query Analyzer, some where outside of ASP. Actually, ADO might convert the 1 to -1. Might want to check that also. Check your query; something's up there I would suspect.|||Yes you were quite right. I was trying to read data from another field. I should have using Mydataset.tables(0).Rows(0)(13)

Thank you for your help