Return all fields of database

Hello,

Is it possible to return all the field names of a database. I do not want the data rows. Just a list of fields in the databse.

Thanks

Something like this will do it

Use YourDBName

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

order by 1,3

|||

Thanks for that.

I am trying this

Use CallsAndIncidents

select PROBSUMMARYM1

from information_schema.columns

I have my database as HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1

where PROBSUMMARYM1 is the table name whose fields I want returned . i.e Name,Type ,Level,etc.

I did not understand these 2 lines

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

what should be column_name(many columns), ordinal_position(?), data_type(different data types),information_schema.columns(?)

Thanks for the help Will.

Kiran

|||

... just run this - don't change the SQL

USE HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

and see what you get

or even

USE PROBSUMMARYM1

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

|||

THANKS WILL,

WORKED GREAT

Only change I had to do was from

USE HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

to

USE CallsAndIncidents

select table_name, column_name, ordinal_position, data_type

from information_schema.columns

Thanks a bunch

Return a Field from a User Function ?

Hello,
I am using SQL Server 2000 and I am wondering if it possible to create a
user fonction that return a field so I can use the return of the function in
a WHERE .
My original query I someting like this:
'=======================================
===
SELECT * FROM Table1
WHERE
case @.Workgroup
WHEN 1 THEN Table1.RouteQuart1
WHEN 2 THEN Table1.RouteQuart2
WHEN 3 THEN Table1.RouteQuart3
END = @.NumRoute
'=======================================
====
I want to create a function to remplace the CASE. This function would return
a field. And My new query would be :
'=======================================
===
SELECT * FROM Table1
WHERE
MyNewUserFunction = @.NumRoute
'=======================================
====
Is there a way to do this ? I the query will be more optimized ' If not
possible how to make my original query the most efficient?
Regards,
Gilles LabelleGilles Labelle,
Write three sps and call them from the main one.
create procedure dbo.p1
@.RouteQuart1 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart1 = @.RouteQuart1
return @.@.error
go
create procedure dbo.p2
@.RouteQuart2 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart2 = @.RouteQuart2
return @.@.error
go
create procedure dbo.p3
@.RouteQuart3 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart3 = @.RouteQuart3
return @.@.error
go
create procedure dbo.p4
@.Workgroup int,
@.NumRoute int
as
set nocount on
declare @.rv int
declare @.error int
if @.Workgroup = 1
begin
exec @.rv = dbo.p1 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 2
begin
exec @.rv = dbo.p2 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 3
begin
exec @.rv = dbo.p3 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
-- handle when the value of @.Workgroup is not 1, 2,3
end
end
end
return @.error
go
AMB
"Gilles Labelle" wrote:

> Hello,
> I am using SQL Server 2000 and I am wondering if it possible to create a
> user fonction that return a field so I can use the return of the function
in
> a WHERE .
> My original query I someting like this:
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> case @.Workgroup
> WHEN 1 THEN Table1.RouteQuart1
> WHEN 2 THEN Table1.RouteQuart2
> WHEN 3 THEN Table1.RouteQuart3
> END = @.NumRoute
> '=======================================
====
> I want to create a function to remplace the CASE. This function would retu
rn
> a field. And My new query would be :
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> MyNewUserFunction = @.NumRoute
> '=======================================
====
>
> Is there a way to do this ? I the query will be more optimized ' If not
> possible how to make my original query the most efficient?
>
> Regards,
> Gilles Labelle
>
>
>

Retuning rows as a single string

Is there any way to do this?
Say I have SELECT MyCol FROM MyTable
MyCol is a nvarchar field, but instead of coming back like this in a table
ItemA
ItemB
ItemC
I want it to come back as a stingle string like this
ItemA, ItemB, ItemC
is this possible in T-SQL? thanks!http://www.aspfaq.com/show.asp?id=2529

> Is there any way to do this?
> Say I have SELECT MyCol FROM MyTable
> MyCol is a nvarchar field, but instead of coming back like this in a table
> ItemA
> ItemB
> ItemC
> I want it to come back as a stingle string like this
> ItemA, ItemB, ItemC|||Try this:
Declare @.Results varchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + CAST(MyCol AS
varchar(3))
FROM
MyTable
Select @.Results
HTH
Barry|||Sorry that should read:
Declare @.Results nvarchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + MyCol
FROM
MyTable
Select @.Results|||didn't know about coalesce, thanks!
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1145990261.449558.38970@.y43g2000cwc.googlegroups.com...
> Sorry that should read:
> Declare @.Results nvarchar(2000)
>
> SELECT @.Results =
> COALESCE(@.Results + ', ', '') + MyCol
> FROM
> MyTable
>
> Select @.Results
>

Retriving Position In A Field

Hi All.
Is there a way to retrieve the position of a word, phrase or sign in a field?
For example, Field content is ABCDEFG1239/1002STJ
I would like to get the exact position of / which will be position 12.
Thank you.
Best regardsThe patindex function should work for you here. But, it will only give you the location of the first one. Syntax:
PATINDEX ( '%pattern%' , expression )|||I wonder what he difference is?

SELECT PATINDEX ( '%/%' , 'ABCDEFG1239/1002STJ' )
SELECT CHARINDEX ( '/','ABCDEFG1239/1002STJ' )|||PatIndex() allows SQL Server regular expressions. CharIndex() only allows literals.

-PatP|||Who was that masked man?|||Hello All.

Thank you for your reply. I couldn't thank of you earlier because I was away to Europe on a business trip.

I will try out your solutions today.

Once again. Thank you.

Best regards

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

retrive a file from a binary data field

hello dear friends

I am trying to save a binary file to database with the following code:

publicstaticvoid memorystreamToDb()

{

MemoryStream mst =newMemoryStream(); UnicodeEncoding u =newUnicodeEncoding(); string Textn ="Test"; byte[] b = u.GetBytes(Textn);

mst.Write(b ,0,Textn.Length );

BinaryReader reader =newBinaryReader(mst); byte[] file = reader.ReadBytes((int)mst.Length); using (SqlConnection connection =newSqlConnection("Some Connection String"))

{

SqlCommand command =newSqlCommand("INSERT INTO temp (examplefile) Values(@.File)", connection);

command.Parameters.Add(

"@.File",SqlDbType.Binary, file.Length).Value = file;

connection.Open();

command.ExecuteNonQuery();

}

reader.Close();

mst.Close();

}

or with the other method from a real file (not memory stream)

publicstaticvoid Addfile(string path)

{

CommonMethods_class k =newCommonMethods_class(); byte[] file = GetFile(path); using (SqlConnection connection =newSqlConnection(k.Get_connection_string()))

{

SqlCommand command =newSqlCommand("INSERT INTO temp (examplefile) Values(@.File)", connection);

command.Parameters.Add(

"@.File",SqlDbType.Binary, file.Length).Value = file;

connection.Open();

command.ExecuteNonQuery();

}

}

and after running each of them seams that the file have been saved; but I can not retrive the files. I have tried some solutions from msdn but inside the created file is empty. the point that i really look for it is to just working with memory not in the disk before saving. and then retriving each field that I want.

looking forward your points

thank you in advance

Hiashk1860 ,

If you haven't readRead and Write BLOB Data by Using ADO.NET Through ASP.NET you can take a look.

|||

hi

thank you for yor response, Yes, I have read the article and some other Articles and still have problem.

retrive a binary field from database

hi

I have used the following code (mostly created by MSDN) to retrive a binary field from SQL database. it works but I have extra space between characters. for example if I save a text file with "Hello world" text, after retriving I have it like "H e l l o w o r l d". what is the problem??

I am really looking forward your answers

private void retrive()

{

publicvoid a()

{

SqlConnection connection =newSqlConnection("Some Connection string");SqlCommand command =newSqlCommand("Select * from temp", connection);// Writes the BLOB to a fileFileStream stream;// Streams the BLOB to the FileStream object.BinaryWriter writer;// Size of the BLOB buffer.int bufferSize = 50;// The BLOB byte[] buffer to be filled by GetBytes.byte[] outByte =newbyte[bufferSize];// The bytes returned from GetBytes.long retval;// The starting position in the BLOB output.long startIndex = 0;// Open the connection and read data into the DataReader.

connection.Open();

SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);while (reader.Read())

{

// Create a file to hold the output.

stream =

newFileStream("C:\\file.txt",FileMode.OpenOrCreate,FileAccess.Write);

writer =

newBinaryWriter(stream);// Reset the starting byte for the new BLOB.

startIndex = 0;

// Read bytes into outByte[] and retain the number of bytes returned.

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);

// Continue while there are bytes beyond the size of the buffer.while (retval == bufferSize)

{

writer.Write(outByte);

writer.Flush();

// Reposition start index to end of last buffer and fill buffer.

startIndex += bufferSize;

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);

}

// Write the remaining buffer.if (retval != 0)

writer.Write(outByte, 0, (

int)retval - 1);

writer.Flush();

// Close the output file.

writer.Close();

stream.Close();

}

// Close the reader and the connection.

reader.Close();

connection.Close();

}

}

Hi, I guess you're storing the text as NTEXT data type in your SQL Server. Since NTEXT/NVARCHAR/NCHAR data uses 2 bytes to stores a unicode character, when you retrieve the text from database using binary stream, each character is transferred as 2 bytes (with the 2nd byte empty), so that's why you found spaces between words. You?can?use?UltraEdit?to?open?the?generated?text?file?and?swith?to?hex?mode,?you'll?00s?between?normal?characters.

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 XML data from table

Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and insert.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.
I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>
sql

retrieving XML data from table

Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and inser
t.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>

Retrieving varchar(max) and varbinary(max) fields with VFP9

I have a test database that contains a varbinary(max) field and a varchar(max) field.

when I do a

Select * from test where id = xx

I get the expected results if my connection string uses

'driver=SQL Server;Server'

but these two fields return no data if I use

'driver=SQL Native Client'

the other fields in the record come back with no problems.

Is there anything special I need to do to retrieve these types of fields?

sorry, typo:

The first driver of course it just

'driver= SQL Server'

Retrieving values from a subreport to Body of Parent Report

Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.

Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.

Thanks,
Garick

I want to do something similar.

I want the value of the amount of records retrieved in the sub report.

The table row that the sub report is in needs to be hidden if the value is not greater than 1.

Can it be done?

|||

Jabuka

I think that there's a simple way to do what you want with creating the same dataset that you have in your subreport in the parent one. Then you can evalute the field in your visibility expression.

Hope it helps you

|||

Another way is to create a simple assembly (any language in .Net) and have a static (or shared) variable in it. Set the value of this variable in your subreport and refer to that in your main report.

The only problem with this approach is concurrency as you are using a static variable.

Shyam

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 multiple values from one field in SQL Server for use in multiple columsn in Reports

I am trying to create a report using Reporting Services.

My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.

I can currently get one value but how to get the information I need to be able to use in my reports.

So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.

Any help would be appreciated.

Thanks.

I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.

What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report

As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.

As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem

Thanks for the suggestions that were made, I apoligize for not making the problem clearer.

Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.

--Pulls the Service Opportunity

SELECT cs.value AS "Service Opportunity"

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE ca.name = 'Service Opportunity'

--Pulls the Number of Hours

SELECT cs.value AS 'Number of Hours'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Num of Hours'

--Pulls the Person Grade Level

SELECT cs.value AS 'Grade'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Grade'

--Pulls the Person Number, First and Last Name and Grade Level

SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"

FROM student s

INNER JOIN cperson cs ON cs.personid = s.personid

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE cs.value =(SELECT cs.value AS 'Grade'

WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')

There are a number of ways to solve this. Here are a few options:

If each value is in its own row, like this:

Name Value

Hours 100

Type AAA

Grade C

the SQL Pivot statement can be used in your query to pivot the rows to columns, so the result looks like:

Hours Type Grade

100 AAA C

If the values are concatenated in a single column, like:

Column

100;AAA;C

then you can use the Split VB function in the report to split the string into its component parts, and put them into fields. To get the values, you can use the following:

=Split(Fields!Column.Value, ";")(0)

=Split(Fields!Column.Value, ";")(1)

=Split(Fields!Column.Value, ";")(2)

|||

It is quite clear from your post that you have all the data in one column in a database table. If you have access to the query or the stored procedure, try to accomplish this in the SQL query level itself because calculating it in reporting service is costlier than doing it in the query level (better if there is a stored procedure becasue it is compiled and faster). Try using this sql query (assuming that your field separator is "," and the order is hours, type and then grade):

CASE WHEN CHARINDEX(',', Column1) > 0

SUBSTRING(Column1, 1, CHARINDEX(',', Column1)-1) AS 'Hours'

END,

CASE WHEN CHARINDEX(',', Column1) > 0

CASE WHEN CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1))) > 0

SUBSTRING(Column1, CHARINDEX(',', Column1)+1, CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1)))-1) AS 'Type'

END

END,

CASE WHEN CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1))) > 0

RIGHT(Column1, CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1)))+1) AS 'Grade'

END

If your still want to use reporting services, create 3 calculated dataset fields as follows:

Hours: IIf(Split(Fields!Column1.Value, ",").UpperBound>=0, Split(Fields!Column1.Value, ",")(0), "")

Type: IIf(Split(Fields!Column1.Value, ",").UpperBound>=1, Split(Fields!Column1.Value, ",")(1), "")

Grade: IIf(Split(Fields!Column1.Value, ",").UpperBound>=2, Split(Fields!Column1.Value, ",")(2), "")

Shyam

|||

John,

Thanks for the suggestion. I guess I didn't make myself very clear on to what I was attempting. I modified my thread and enclosed a sample of what I am attempting to do.

Thanks again for the help

|||

Shyam,

Thanks for the advice. I guess I didn't make myself clear enough on what I was attempting to do. I have modifed my thread and enclosed a sample of the code I am trying to make work.

Thanks again

|||

Use the following query to get all values at one shot:

SELECT cs1.value AS 'Service Opportunity', cs2.value AS 'Number of Hours', cs3.value AS 'Grade'

FROM Cstudent cs1

INNER JOIN cattribute ca ON ca.attributeid = cs1.attributeid

AND ca.name = 'Service Opportunity'

INNER JOIN Cstudent cs2 ON ca.attributeid = cs2.attributeid

AND ca.name = 'Num of Hours'

INNER JOIN Cstudent cs3 ON ca.attributeid = cs3.attributeid

AND ca.name = 'Grade'

Thanks,

Shyam

|||

Shyam,

I've been working with what you suggested but so far with no luck. It pulls the column headings but there is no information in the columns. I'm looking to see if I'm missing something somewhere.

Thanks for the advice though, I'll keep working at it and see what I can come up with.

Thanks

Wayne

|||

Maybe not all records are available in Cstudent, so use this query:

SELECT cs1.value AS 'Service Opportunity', cs2.value AS 'Number of Hours', cs3.value AS 'Grade'

FROM cattribute ca

LEFT OUTER JOIN Cstudent cs1

ca ON ca.attributeid = cs1.attributeid

AND ca.name = 'Service Opportunity'

LEFT OUTER JOIN Cstudent cs2 ON ca.attributeid = cs2.attributeid

AND ca.name = 'Num of Hours'

LEFT OUTER JOIN Cstudent cs3 ON ca.attributeid = cs3.attributeid

AND ca.name = 'Grade'

Shyam

|||

Shyam

Thanks for the help, Just have to do some more tweaking on my end but looks like it might give me what I'm looking for.

Thanks Again for the Help

Wayne

|||

So, can you mark the post as answer?

Shyam

|||

Shyam

Thanks for all the help. This was the first post I had done one here so I apoligize for that. It's taken care of and I posted it answered.

Retrieving Images from SQL Database

Hello everyone!

I have an SQL Database that originally we had an image filename in the "Image" field, and using ASP, concantenated a loaction with it to retrieve an image on our website. We just converted that field to where we can actually place the actual image in the field. My question is, how can I use ASP to retireve and show the image?

Thanks for your help in advance!

Matt

Use the following link for a good example

http://authors.aspalliance.com/stevesmith/articles/imagequery.asp

Retrieving Images from SQL Database

Hello everyone!

I have an SQL Database that originally we had an image filename in the "Image" field, and using ASP, concantenated a loaction with it to retrieve an image on our website. We just converted that field to where we can actually place the actual image in the field. My question is, how can I use ASP to retireve and show the image?

Thanks for your help in advance!

Matt

Use the following link for a good example

http://authors.aspalliance.com/stevesmith/articles/imagequery.asp

Retrieving database record with the lowest value in field aaa?

How do I code a SQL SELECT statement so that always only this record is retr
ieved
which matches a certain criteria AND has the lowest ID (= value in key field
aaa)?
It must me something like
SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aaa)
)
As a result either zero or at most 1 record should be passed back.
George"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
aaa=lowestkey(column(aaa))
> As a result either zero or at most 1 record should be passed back.
> George
>
I am not exactly sure what you are looking for, but typically this done with
a subselect:
SELECT a.col1, b.col2 FROM table-name a
WHERE a.col1 = 'somevalue'
AND a.col2 = (select b.min(col2) from FROM table-name b where ...)|||You can use TOP clause.
SELECT top 1 * FROM ... WHERE somefield='somevalue'
order by aaa asc
AMB
"George Dainis" wrote:

> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
>|||This would appear to be a good candidate for an inline view using
rownum.
Something like;
Select ...<information you want to see>
from (Select ...<information you want to see>
_ from ...
_ where somefield='somevalue'
_ order by aaa)
where rownum < 2;
(Ignore the underscore, they're just there for holding the indentation)|||George Dainis wrote:
> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
>
Lookup min() (in your textbook?)
Regards,
Frank van Bortel|||try
ROW_NUMBER() OVER(order by ...)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
> retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
> field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
> aaa=lowestkey(column(aaa))
> As a result either zero or at most 1 record should be passed back.
> George
>
AND aaa = ( <use a subquery with the MIN() function> )
++ mcs|||George Dainis wrote:

> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
I am going to assume, given that you have posted this to every usenet
group you can spell, among them comp.databases.oracle.misc,
microsoft.public.sqlserver.programming, comp.databases.oracle, and
comp.databases.ibm-db2, that you are trying to find someone to do your
homework for you.
The optimal solution will vary by product and even version so posting
as you have says something about what you are trying to do.
As we don't do other people's homework for them and you seemingly have
made no attempt to solve this on your own ... go talk to your faculty
advisor about what you have done and ask for help there.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
> retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
> field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
> aaa=lowestkey(column(aaa))
lookup MIN in the sql reference.
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com|||On Thu, 27 Jan 2005 19:50:22 +0100, george.dainis@.bluecorner.com (George
Dainis) wrote:

>How do I code a SQL SELECT statement so that always only this record is ret
rieved
>which matches a certain criteria AND has the lowest ID (= value in key fiel
d aaa)?
>It must me something like
>SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aaa
))
>As a result either zero or at most 1 record should be passed back.
SELECT *
FROM ...
WHERE somefield = 'somevalue'
AND aaa = (SELECT MIN(aaa)
FROM ...
WHERE somefield = 'somevalue')
Andy Hassall / <andy@.andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

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

Retrieve XML From sysdtspackages90

Is there a way to translate the packagedata field data to XML on the sysdtspackages90 table on msdb?

I want to be able to programmatically analyze and potentially modify the XML of Integration Services packages that reside on the server.

Thanks for your help.

Modifying the xml directly is not supported. However, you can get the package from the packages table by using the LoadPackageFromSQLServer method on the application object. You can save it to a file and then read the package file however you like to access the xml (xmlreader, dom, etc).

Matt