How to remove 'Select a Value' from the drop down list.

I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in all
the drop down list.
I don't want 'Select a value' in the drop down list
Please advise how to remove it from the drop down listYour report has parameters and unless a default is provided you will get the
'Select a value message. If you want it to be blank, provide a blank as the
default value.|||you have to provide a default value for your lists.
without a default value, the Select a value text is displayed
"amitmittal77" <u17066@.uwe> wrote in message news:598ef6aff1ffa@.uwe...
>I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in
>all
> the drop down list.
> I don't want 'Select a value' in the drop down list
> Please advise how to remove it from the drop down list|||I am using dataset for the dropdown which have default default values
the default value setting in the report parameter Screen is
i have selected From query option in default value section
under From query folllowing values have been selected
Dataset - my dataset
Value field - Parameter value
Still i am getting 'Select a value'
Jéjé wrote:
>you have to provide a default value for your lists.
>without a default value, the Select a value text is displayed
>>I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in
>>all
>> the drop down list.
>> I don't want 'Select a value' in the drop down list
>> Please advise how to remove it from the drop down list
--
Message posted via http://www.sqlmonster.comsql

How to reference to an image control

Hi,
I have a report. In one column i want to show a certain image depending the
value of another column. For example:
if text1 =1 than text10 = image1
elseif text1 =2 than text10 = image2
else text1 =3 than text10 = image3
end if
Is this possible? Also what i do recall is that in access if you set an
image for one row, the image goes for all rows (in a form). Does this applies
for SQL Reports also?
In general, how can i refence to a control (like a list, a rectangle, etc)
on a report?
Thnx,
StanleyIn the value of your image control, put the following:
=IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
in my solution, my bmp's are stored as part of the solution, so they are
deployed to the server as well.
"Stanley" wrote:
> Hi,
> I have a report. In one column i want to show a certain image depending the
> value of another column. For example:
> if text1 =1 than text10 = image1
> elseif text1 =2 than text10 = image2
> else text1 =3 than text10 = image3
> end if
> Is this possible? Also what i do recall is that in access if you set an
> image for one row, the image goes for all rows (in a form). Does this applies
> for SQL Reports also?
> In general, how can i refence to a control (like a list, a rectangle, etc)
> on a report?
> Thnx,
> Stanley
>|||Hi Pinolian,
Thnx for your response. I have added the pictures to the solution and
deployed it. But instead of seeing the images i get to see a 'red cross' like
it cannot find the image. Do you know why?
When the solution is deployed should i see the images in the report manager
also?
Thnx,
Stanley
"Pinolian" wrote:
> In the value of your image control, put the following:
> =IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
> in my solution, my bmp's are stored as part of the solution, so they are
> deployed to the server as well.
>
> "Stanley" wrote:
> > Hi,
> >
> > I have a report. In one column i want to show a certain image depending the
> > value of another column. For example:
> >
> > if text1 =1 than text10 = image1
> > elseif text1 =2 than text10 = image2
> > else text1 =3 than text10 = image3
> > end if
> >
> > Is this possible? Also what i do recall is that in access if you set an
> > image for one row, the image goes for all rows (in a form). Does this applies
> > for SQL Reports also?
> >
> > In general, how can i refence to a control (like a list, a rectangle, etc)
> > on a report?
> >
> > Thnx,
> >
> > Stanley
> >
> >|||Problem Solved with the image not showing. The picture name should be
mentioned without the extension, like:
=IIF(Fields!Sales.Value = True, "checkedbox-big", "blankbox")
"Pinolian" wrote:
> In the value of your image control, put the following:
> =IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
> in my solution, my bmp's are stored as part of the solution, so they are
> deployed to the server as well.
>
> "Stanley" wrote:
> > Hi,
> >
> > I have a report. In one column i want to show a certain image depending the
> > value of another column. For example:
> >
> > if text1 =1 than text10 = image1
> > elseif text1 =2 than text10 = image2
> > else text1 =3 than text10 = image3
> > end if
> >
> > Is this possible? Also what i do recall is that in access if you set an
> > image for one row, the image goes for all rows (in a form). Does this applies
> > for SQL Reports also?
> >
> > In general, how can i refence to a control (like a list, a rectangle, etc)
> > on a report?
> >
> > Thnx,
> >
> > Stanley
> >
> >

How to reference a text box in an insert statement

I am trying to take a value from at textbox and insert into a database. I think I need to convert the text to an integer - but I keep getting an error message saying I'm not allowed to use column names. Basically, I need to figure out how to take user input from these tet boxes and insert them into a database. Any ideas would be welcomed. the exact error is at the bottom the code.

Here is the code:

Imports System.Data
Imports System.Data.SqlClient
Partial Class County_ConversionTest
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim v1 As Integer
Dim v2 As Integer
'Dim v3 As Integer
v1 = Integer.Parse(TextBox1.Text)
v2 = Integer.Parse(TextBox2.Text)
TextBox3.Text = (v1 + v2)

End Sub

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim strconn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\HCBS\App_Data\DivAging_Data.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim cnnDivAging As SqlConnection = New SqlConnection(strconn)
Dim v1 As Integer
Dim v2 As Integer
Dim v3 As Integer
v1 = CInt(TextBox1.Text)
v2 = CInt(TextBox2.Text)
v3 = CInt(TextBox3.Text)

Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George',v1,v2,v3)"

Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging)
cmdUpdates.Connection = cnnDivAging
cnnDivAging.Open()
cmdUpdates.CommandType = CommandType.Text
cmdUpdates.ExecuteNonQuery()
cnnDivAging.Close()
End Sub
End Class

Here is the error message -

The name "v1" is not permitted in this context. Valid expressions areconstants, constant expressions, and (in some contexts) variables.Column names are not permitted.hey jcahill,
try this:

Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George'," & v1 & "," & v2 & "," v3 & ")"

v1, v2, v3 are not there in sql, you just want the values in the sql.
Hope it helps.|||

You should get in the habit of doing it the right way:

Dim strsql AS String="INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George',@.v1,@.v2,@.v3)"
Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging)
' cmdUpdates.Connection = cnnDivAging -- Redundant
cmdUpdates.Parameters.Add("@.v1",sqldbtype.int32).value=v1
cmdUpdates.Parameters.Add("@.v2",sqldbtype.int32).value=v2
cmdUpdates.Parameters.Add("@.v3",sqldbtype.int32).value=v3
cnnDivAging.Open()
' cmdUpdates.CommandType = CommandType.Text -- Redundant and should be done before the open to minimize the amount of time the connection is "open"
cmdUpdates.ExecuteNonQuery()
cnnDivAging.Close()

sql

How to refer to report textbox values in another textbox

I want to add up the values in a couple of text boxes in another textbox. How do I refer to the textboxes?

fields!textbox1.value doesn't work..what does?

Hello,

Try this:

ReportItems!textbox1.Value

Hope this helps.

Jarret

How to reduce the time when using a variable

Hi,

I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @.i and now my select statement is like this: 'Select substring(column_name,@.i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.

Why it is like this? Is there any way I can reduce the time taken to execute each iteration?

Thanks.

how many records of this kind are you processing.

the number of records must ne put into consideration.

a cursor that iterate with every records first must be put in place

then place the while loop inside every fetch of the cursor

do the processing(while loop) of the column

i think there should be two loops to handle this case.

one that iterates with the records

and one that iterates for every codes in the the column.

if this is an SP. a with recompile option will make your query faster

regards.

|||

What about

INSERT INTO table1

SELECT SUBSTRING(column_name,1,3) from table WHERE SUBSTRING(column_name,1,3) <> ''

UNION

SELECT SUBSTRING(column_name,4,3) from table WHERE SUBSTRING(column_name,4,3) <> ''

UNION

...

? Copy/paste above line until substring index matches column_name size; or, if you don't want to hardcode it - construct this query referring to column_name size and run it through EXEC.

This could be faster - try it out...

|||

If you are using SQL Server 2005 then you can do the following:

1. Create a table-valued function that takes a string and splits it based on space character. You can search the web for several sample functions that does this operation

2. Use it in a SELECT statement using APPLY operator like:

insert into tbl(i, ...)

select s.val

from table as t

cross apply splitstr(t.column_name) as s

In SQL Server 2000, you can do similar operation except there is no APPLY operator so you cannot use a TVF. You can use a table of numbers to split the string and then do a cross join with this table instead. Or the approach using UNION as suggested in another post will also work. Make sure you use UNION ALL though for better performance and to eliminate the unnecessary sorting.

|||Here is an article on handling arrays and lists - quite a few methods and instruments, may be useful.|||

Hi Umachandar,

Your tip was very helpful to me. Now the whole process completes in 2 mins. Thx.

sql

How to receive mails, when a record is entered into my error log table?

I'm using SQL Server 2000.

I want to receive a mail for every new entry, into my error log table. I want to receive the inserted value in the form of subject of the mail and some other text column's value as the body of the mail.

Plz. guide me.

Best Regards

Trinadh P.

SQLNS can certainly do this, but depending on your circumstances another technology - including Database Mail or an external SMTP XP - may be better suited for the task.

If you opt for the SQLNS route, take at look at one of the samples provided with the install and mimic one that uses the SQL Server Event Provider.

HTH....

Joe

How to recall column name defined in CASE function

Hello all,

I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @.Term, SQL Server returned an error: Invalid column name 'SLA'

If anyone knows a solution, please kindly let me know.

Thank you!

Here is the sample code:

ALTER PROCEDURE [dbo].[sp_premium_register]
@.PolicyType AS VARCHAR(10),
@.ReportFrom AS DATETIME,
@.ReportTo AS DATETIME,
@.Business AS VARCHAR(1),
@.Term AS VARCHAR(1)

SELECT
ColumnA,
ColumnB,

SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
END

FROM DNIssue D
LEFT OUTER JOIN KILRIShare S
ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
LEFT OUTER JOIN KILPolicy P
ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
LEFT OUTER JOIN v_report_KILDNFund F
ON (D.DebitNote = F.DebitNote)
LEFT OUTER JOIN PolicyProfile R
ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType

WHERE
SLA = @.Term

Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDateuse an outer query
SELECT ColumnA
, ColumnB
, SLA
FROM (
SELECT ColumnA
, ColumnB
, SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1
THEN 'L'
else 'S'
END
FROM DNIssue D
LEFT OUTER
JOIN KILRIShare S
ON S.PolicyNo = D.PolicyNo
AND S.PolicyType = D.PolicyType
AND S.Origin = D.Origin
AND S.EndrNo = D.EndorsementNo
AND S.RINo = D.PartyNo
LEFT OUTER
JOIN KILPolicy P
ON P.PolicyNo = D.PolicyNo
AND P.PolicyType = D.PolicyType
AND P.Origin = D.Origin
AND P.EndrNo = D.EndorsementNo
LEFT OUTER
JOIN v_report_KILDNFund F
ON F.DebitNote = D.DebitNote
LEFT OUTER
JOIN PolicyProfile R
ON R.Origin = D.Origin
AND R.PolicyType = D.PolicyType
ORDER
BY D.PolicyType
, D.DebitNote
, D.Origin
, D.PolicyNo
, D.EndorsementNo
, D.EntryDate
) as t
WHERE SLA = @.Term or bite the bullet and code the datediff into the WHERE condition|||you'll have to repeat the case statement in your where clause|||It works! Thank you so much!!!

How to read the value of DEADLOCK_PRIORITY

Hi,
a simple question, i want to know the value of DEADLOCK_PRIORITY, where
and/or how to read it ?
Best regards.In 2005, you can get it from the sys.dm_exec_sessions view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Franck Fouache" <franck.fouache@.ccmx.fr> wrote in message
news:OZh48ksiGHA.3496@.TK2MSFTNGP02.phx.gbl...
> Hi,
> a simple question, i want to know the value of DEADLOCK_PRIORITY, where an
d/or how to read it ?
> Best regards.
>|||i forgot to tell i used SQL2000 / 8.0 ... if you know how to in this version
...
but i'll keep in mind your answer for SQL2005, thanks for your help
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news: %23frqPOuiGHA.4884@.TK2MSFTNGP03.phx.gbl...
> In 2005, you can get it from the sys.dm_exec_sessions view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Franck Fouache" <franck.fouache@.ccmx.fr> wrote in message
> news:OZh48ksiGHA.3496@.TK2MSFTNGP02.phx.gbl...
>|||I don't think there is a way in 2000. I tried both:
DBCC USEROPTIONS
SELECT @.@.OPTIONS
And none of above seem to reflect this setting. I might be missing something
, of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Franck Fouache" <franck.fouache@.ccmx.fr> wrote in message
news:OAwF9C6iGHA.3408@.TK2MSFTNGP05.phx.gbl...
> i forgot to tell i used SQL2000 / 8.0 ... if you know how to in this versi
on ...
> but i'll keep in mind your answer for SQL2005, thanks for your help
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de
> news: %23frqPOuiGHA.4884@.TK2MSFTNGP03.phx.gbl...
>

How to read the value of DEADLOCK_PRIORITY

Hi,
a simple question, i want to know the value of DEADLOCK_PRIORITY, where
and/or how to read it ?
Best regards.In 2005, you can get it from the sys.dm_exec_sessions view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Franck Fouache" <franck.fouache@.ccmx.fr> wrote in message
news:OZh48ksiGHA.3496@.TK2MSFTNGP02.phx.gbl...
> Hi,
> a simple question, i want to know the value of DEADLOCK_PRIORITY, where and/or how to read it ?
> Best regards.
>|||i forgot to tell i used SQL2000 / 8.0 ... if you know how to in this version
...
but i'll keep in mind your answer for SQL2005, thanks for your help
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a écrit
dans le message de news: %23frqPOuiGHA.4884@.TK2MSFTNGP03.phx.gbl...
> In 2005, you can get it from the sys.dm_exec_sessions view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Franck Fouache" <franck.fouache@.ccmx.fr> wrote in message
> news:OZh48ksiGHA.3496@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> a simple question, i want to know the value of DEADLOCK_PRIORITY, where
>> and/or how to read it ?
>> Best regards.
>|||I don't think there is a way in 2000. I tried both:
DBCC USEROPTIONS
SELECT @.@.OPTIONS
And none of above seem to reflect this setting. I might be missing something, of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Franck Fouache" <franck.fouache@.ccmx.fr> wrote in message
news:OAwF9C6iGHA.3408@.TK2MSFTNGP05.phx.gbl...
> i forgot to tell i used SQL2000 / 8.0 ... if you know how to in this version ...
> but i'll keep in mind your answer for SQL2005, thanks for your help
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a écrit dans le message de
> news: %23frqPOuiGHA.4884@.TK2MSFTNGP03.phx.gbl...
>> In 2005, you can get it from the sys.dm_exec_sessions view.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Franck Fouache" <franck.fouache@.ccmx.fr> wrote in message
>> news:OZh48ksiGHA.3496@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> a simple question, i want to know the value of DEADLOCK_PRIORITY, where and/or how to read it ?
>> Best regards.
>>
>

How to read the return value of a stored procedure?

Stored procedure in SQL Server 2K is as follows..

CREATE PROCEDURE TestDataRead

@.TestString varchar(20) OUTPUT

AS

SET @.TestString = 'Mr.String'

GO

--

SSIS package details.

OLEDB connection manager; Connection works fine.

Execute SQL Task Editor properties are as follows.

ResultSetàNone

SQLStatementà exec TestDataRead

SQLSourceTypeirectInput

Parameter Mapping:

VariableName: selected the user variable from the list.

Direction: Output

DataType:varchar

Parameter Name: 0

Parameter Type: 20

When I run I am getting the following error.

SSIS package "TEST SSIS1.dtsx" starting.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec TestDataRead" failed with the following error: "Procedure 'TestDataRead' expects parameter '@.TestString', which was not supplied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ...........................................
...................................................................

Please help.

Smith

John Smith001 wrote:

SQLStatementà exec TestDataRead ? OUTPUT

For output parameters also it needs to be passed when calling the stored procedure.

Thanks

|||

Thank you, Karunakarana!

How to read return value from sqlpipe send(string) method

Hi guys,
How can i read in my C# client application values returned from sqlpipe send(string)/send(reader method from CLR stored procedure.....following is example of a stored procedure.I want to read datetime string in my C# client
Thanks
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PrintToday()
{
// Put your code here
SqlPipe p;
p = SqlContext.Pipe;
p.Send(System.DateTime.Today.ToString());
}

Hi!

Execute your PrintToday with SqlCommand.ExecuteReader, then read the string from SqlDataReader returned.

|||Exactly how are you supposed to do this? No matter what i do, the reader is always empty.

How to read return value from sqlpipe send(string) method

Hi guys,
How can i read in my C# client application values returned from sqlpipe send(string)/send(reader method from CLR stored procedure.....following is example of a stored procedure.I want to read datetime string in my C# client
Thanks
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PrintToday()
{
// Put your code here
SqlPipe p;
p = SqlContext.Pipe;
p.Send(System.DateTime.Today.ToString());
}

Hi!

Execute your PrintToday with SqlCommand.ExecuteReader, then read the string from SqlDataReader returned.

|||Exactly how are you supposed to do this? No matter what i do, the reader is always empty.

how to read a xsd value

Hello (this is a frenchy message so : a very bad english...(
will be able to forgive me? )) :)
how then I to read the contents of a file xsd and to recover the value of
nodes and their children?
this program work but only for the principal node but not for children
if my shcema content
<xsd:element name="COMMENT">
<xsd:element name="CONSOMMATION">
<xsd:element name="OPTIONS">
<xsd:element name="PERSONNAL">
<xsd:element name="items">
my program write
COMMENT
CONSOMMATION
OPTIONS
PERSONNAL
items
but not
<xsd:element name="city" type="xsd:string" />
<xsd:element name="higtway" type="xsd:string" />
<xsd:element name="mix" type="xsd:string" />
in <xsd:element name="CONSOMMATION">
'first i chose a schema in a drop
'and i read the shcema in sql server 2005 like this
Dim cmdSql As String
cmdSql = "SELECT XML_SCHEMA_NAMESPACE(sch.name, xsd.name) FROM
sys.xml_schema_collections AS xsd "
cmdSql = cmdSql + "JOIN sys.schemas AS sch ON xsd.schema_id = sch.schema_id
WHERE xsd.name = '"
cmdSql = cmdSql + Session("dropSelect") + "'" ' drop select contents the
schema name in my sql server 2005
Dim xml As SqlXml
Dim xmlRdr As System.Xml.XmlReader
Dim conn As SqlConnection = New
SqlConnection(ConfigurationManager.ConnectionStrings("cs_vehicule").Connecti
onString)
conn.Open()
Dim cmd As SqlCommand = New SqlCommand(cmdSql, conn)
Dim rdr As SqlDataReader = cmd.ExecuteReader
While rdr.Read
xml = rdr.GetSqlXml(0) ' GetSqlXml(0) = number of row where i can read xml
data
xmlRdr = xml.CreateReader
While xmlRdr.Read
Dim schema As XmlSchema
schema = XmlSchema.Read(xmlRdr, Nothing)
LblSchema.Text = schema.Id
Dim schemaObject As XmlSchemaObject
For Each schemaObject In schema.Items
If schemaObject.GetType() Is GetType(XmlSchemaElement) Then
'Dim ElementType As XmlSchemaElement = CType(schemaObject,
XmlSchemaElement)
'LblSchema.Text = LblSchema.Text + ElementType.Name.ToString + "<br/>"
Dim schemaSequence As XmlSchemaSequence
For Each schemaSequence In schema.Elements
Dim SequenceType As XmlSchemaSequence = CType(schemaObject,
XmlSchemaSequence)
LblSchema.Text = LblSchema.Text + SequenceType.Items.ToString(+"<br/>")
Next
End If
Next schemaObject
End While
xmlRdr.Close()
End While
rdr.Close()
conn.Close()
Thank youI am less concerned about your french influence than the VB code ;-)
Why don't you just use XQuery in the database to generate your output?
SELECT XML_SCHEMA_NAMESPACE(sch.name, xsd.name).query('for $n in
/xs:schema/xs:element/@.name return data($n)') FROM
sys.xml_schema_collections AS xsd
JOIN sys.schemas AS sch ON xsd.schema_id = sch.schema_id
WHERE xsd.name = @.dropSelect -- @.dropSelect is a parameter you pass from
your client program.
Best regards
Michael
"Helter Skelter" <nowhere-man@.apple.uk> wrote in message
news:45b88440$0$27410$ba4acef3@.news.orange.fr...
> Hello (this is a frenchy message so : a very bad english...(
> will be able to forgive me? )) :)
> how then I to read the contents of a file xsd and to recover the value of
> nodes and their children?
> this program work but only for the principal node but not for children
> if my shcema content
> <xsd:element name="COMMENT">
> <xsd:element name="CONSOMMATION">
> <xsd:element name="OPTIONS">
> <xsd:element name="PERSONNAL">
> <xsd:element name="items">
> my program write
> COMMENT
> CONSOMMATION
> OPTIONS
> PERSONNAL
> items
> but not
> <xsd:element name="city" type="xsd:string" />
> <xsd:element name="higtway" type="xsd:string" />
> <xsd:element name="mix" type="xsd:string" />
> in <xsd:element name="CONSOMMATION">
>
> 'first i chose a schema in a drop
> 'and i read the shcema in sql server 2005 like this
> Dim cmdSql As String
> cmdSql = "SELECT XML_SCHEMA_NAMESPACE(sch.name, xsd.name) FROM
> sys.xml_schema_collections AS xsd "
> cmdSql = cmdSql + "JOIN sys.schemas AS sch ON xsd.schema_id =
> sch.schema_id
> WHERE xsd.name = '"
> cmdSql = cmdSql + Session("dropSelect") + "'" ' drop select contents the
> schema name in my sql server 2005
> Dim xml As SqlXml
> Dim xmlRdr As System.Xml.XmlReader
> Dim conn As SqlConnection = New
> SqlConnection(ConfigurationManager.ConnectionStrings("cs_vehicule").Connec
tionString)
> conn.Open()
> Dim cmd As SqlCommand = New SqlCommand(cmdSql, conn)
> Dim rdr As SqlDataReader = cmd.ExecuteReader
> While rdr.Read
> xml = rdr.GetSqlXml(0) ' GetSqlXml(0) = number of row where i can read
> xml data
> xmlRdr = xml.CreateReader
> While xmlRdr.Read
> Dim schema As XmlSchema
> schema = XmlSchema.Read(xmlRdr, Nothing)
> LblSchema.Text = schema.Id
> Dim schemaObject As XmlSchemaObject
> For Each schemaObject In schema.Items
> If schemaObject.GetType() Is GetType(XmlSchemaElement) Then
> 'Dim ElementType As XmlSchemaElement = CType(schemaObject,
> XmlSchemaElement)
> 'LblSchema.Text = LblSchema.Text + ElementType.Name.ToString + "<br/>"
> Dim schemaSequence As XmlSchemaSequence
> For Each schemaSequence In schema.Elements
> Dim SequenceType As XmlSchemaSequence = CType(schemaObject,
> XmlSchemaSequence)
> LblSchema.Text = LblSchema.Text +
> SequenceType.Items.ToString(+"<br/>")
> Next
> End If
> Next schemaObject
> End While
> xmlRdr.Close()
> End While
> rdr.Close()
> conn.Close()
>
> Thank you
>|||Dear Michael
thank you for your humour and the interest which you carry to my work.
I build a super market which will be able to sell televisions, cars,
computers, vegetables and shirts (laughter).
My base "sql server" contains a single table for all these articles.
Only one fields will be different: that of the characteristics (in xml).
So I have different diagrams xml according to item (to be able to compare
them). I currently have 27 possible diagrams !
The characteristics of a television are different from a car.
When I read a recording, fields indicates to me which is the diagram that I
must associate for good read the fields of the characteristics.
The diagram xsd will be useful to me for the validation of a new seizure or
a modification.
It must also serve to me to create on my Web page the "labels" and "textbox"
during creation as the recording to fill my fields xml of the
characteristics.
When I create a new article, I must say:
1) which is its diagram (dropdownlist)
2) to read which are the fields contained in the diagram
3) to create its fields on my page
example:
video chart
--processor speed
--RAM
mother board
--chipset
--a number of slot
When I create a new article, it is by reading the diagram that I will find
which are the fields to be created.
For this reason I read the diagram in "sql server 2005"
Let us be us always friendly (laughter ) ?
A splendid time is guaranteed for all !
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> a crit dans le message de
news: %23ApgopPQHHA.4992@.TK2MSFTNGP04.phx.gbl...
>I am less concerned about your french influence than the VB code ;-)
> Why don't you just use XQuery in the database to generate your output?
> SELECT XML_SCHEMA_NAMESPACE(sch.name, xsd.name).query('for $n in
> /xs:schema/xs:element/@.name return data($n)') FROM
> sys.xml_schema_collections AS xsd
> JOIN sys.schemas AS sch ON xsd.schema_id = sch.schema_id
> WHERE xsd.name = @.dropSelect -- @.dropSelect is a parameter you pass from
> your client program.
> Best regards
> Michael
> "Helter Skelter" <nowhere-man@.apple.uk> wrote in message
> news:45b88440$0$27410$ba4acef3@.news.orange.fr...
>
>

how to quick query duplicate records?

any idea?

quick query duplicate records (speicifed fields.value are same) using T-SQL?

depends on how many columns you've got in the table but as an example:

say you have a table called Customer with one column called CustomerID then this simple query will tell you.

SELECT CustomerID, COUNT(*)

FROM Customer

GROUP BY CustomerID

HAVING COUNT(*) > 1

ORDER BY 2 DESC

|||

Thanks your reply what i want is

display duplicate records depends on multi-column

regards

|||

just add the extra columns in to the SELECT and GROUP BY clauses and it will work for multiple columns.

even better, can u post an example of the data that you are wanting to look for duplicates in?

|||

At the same time if you table own identity field you could use another query. Keeping on mind identity value you drop the one own less value.

I'm sorry but I don't have that query

|||

Because i want to remove duplicate records,so have to display first i think

I found an article

http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp

I think it's cool, sharing

How to query the date whose value is in text format

Hello,

I get a problem in developing the company report. The column of "BATCH" in Oracle view is shared by all departments that need the different entries, so we have to use "Text" data type for this column in order meet all departments needs.

One department uses it as a sample date column. The text entry value is in the format of mm/dd/yyyy.

If I do not do the data converting, when I set the query: Where SampleDate between '01/01/2005' and '01/31/2005', the outcome not only include the January data of 2005 but also include the January data from all privious years, because system evaluates the data in text format rather than date format.

What I have done is to use Oracle To_Date(SampleDate,'mm/dd/yyyy') function to convert the text format to Oracle date format in report dataset.

I have two problems:

1) How to take out the wrong format enties? for example, user enters 1102/2005 instead of 11/02/2005. I find that the wrong formating entries cause the failure of data retrival. Micro T-SQL has a function of IsDate() to flag out the entries that are not in date formating, but it does not work here, I guess the reason may be our data source is Oracle.

2) Even the data is conveted, it still does not work propertly.

For example, I write:

Where To_Date(Batch,'mm/dd/yyyy') between '01/01/2005' and '01/31/2005'

it does not work

I write: Where To_Date(Batch,'mm/dd/yyyy') between To_date('01/01/2005','mm/dd/yyyy') and To_date('01/31/2005','mm/dd,yyyy')

it still does not work.

How to resolve these issues.

Thanks,

Zixing

For problem #1, if you are using Oracle 10g you can use a regular expression in your query so you only get valid matching dates. If you aren't using 10g, then you could use the like operator with some wildcards.

For problem #2, try running your query in an Oracle client first just to make sure that you have the query syntax right. I am not familiar with Oracle tools for performing queries but I am thinking something like MSSQL Query Analyzer.

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in messag
e
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
>

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>
|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
>

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
--
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> > I have edited a single company name in the Northwoods Customers
> > table so the CompanyName value begins with a lower case character.
> >
> > When using Query Analyzer to run against Northwoods the following
> > query returns 4 records: the one I edited with the lower case a and 3
> > others where the company's name begins with upper case A
> >
> > SELECT * FROM Customers
> > WHERE CompanyName LIKE 'a%'
> >
> > Thus, case insensitivity appears to be the T-SQL default. How would
> > I get only those records where CompanyName begins with lower case
> > characters?
> >
> > BTW: I'm building one of those alphanumerice linked character menus
> > that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> > have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> > *all* company names whether they begin with an upper case letter
> > or a lower case letter.
> >
> > Using [Aa]% functioned as described above but then I realized I misled
> > myself as I was not able to get *only* records with a company name that
> > began with the lower case character when using [a]% or a%.
> >
> >
> > --
> > <%= Clinton Gallagher
> > A/E/C Consulting, Web Design, e-Commerce Software Development
> > Wauwatosa, Milwaukee County, Wisconsin USA
> > NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> > URL http://www.metromilwaukee.com/clintongallagher/
> >
> >
>

How to query for a number only?

I have a varchar column, I want to find the records where the value is a
number only.
TIA
CDSee if this helps:
What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"CD" wrote:

> I have a varchar column, I want to find the records where the value is a
> number only.
> TIA
> CD
>
>|||Hi
just see if this helps:
SELECT *
FROM <table>
WHERE ISNUMERIC(<Column> ) = 1
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"CD" wrote:

> I have a varchar column, I want to find the records where the value is a
> number only.
> TIA
> CD
>
>|||CD
CREATE TABLE #Test
(
col VARCHAR(10) NOT NULL
)
INSERT INTO #Test VALUES ('GDGS12')
INSERT INTO #Test VALUES ('566')
INSERT INTO #Test VALUES ('GGG')
SELECT * FROM #Test WHERE PATINDEX('%[A-Z]%',col) =0
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:ONdeU2CbFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a varchar column, I want to find the records where the value is a
> number only.
> TIA
> CD
>|||Here's a UDF that checks if a value is really numeric.
Sorry, I don't remember who to give credit to for the function.
CREATE FUNCTION dbo.IsReallyNumeric
(@.num VARCHAR(19))
RETURNS BIT
BEGIN
RETURN CASE
WHEN LEFT(@.num,1) LIKE '[-0-9+.]'
AND PATINDEX('%[^0-9.]%', SUBSTRING(@.num, 2, 18)) = 0
AND LEN(@.num) - LEN(REPLACE(@.num, '.', '')) <=1
THEN 1
ELSE 0
END
END
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:ONdeU2CbFHA.3120@.TK2MSFTNGP12.phx.gbl...
>I have a varchar column, I want to find the records where the value is a
>number only.
> TIA
> CD
>

how to query for a column value that contains dashes

Hi,

I have a large table with a 'datetime' column that has date and time
values in it. The data is in this format:

2007-10-02 09:54:00.000

The table is called 'profile' and the column 'msgdate'

I want to return only rows that match a specific date. So far I have
the following query working:

select * from profile where msgdate like '%2007%'

This returns all rows that start with '2007'. However I cannot seem to
ge the syntax that will allow me to return a specific date, e.g.
2007-10-02

I have researched this, trying all sorts of queries with escape
characters/sequences because of the dash character, but I cannot get
it to return anything. Most of my queries have ran without error, its
just that no data is returned.

JamesWhat exactly is the data type of the column msgdate? You mention it
is a 'datetime' column. If it is the datatype of datetime then forget
about strings, it is stored internally as a couple of numbers. With a
non-zero time the standard way to test for a specific date is:

WHERE msgdate >= '20071002' AND msgdate < '20071003'

Note that the second date is the next day. This approach allows use
of an index is one is available and otherwise makes sense.

Roy Harvey
Beacon Falls, CT

On Fri, 12 Oct 2007 10:01:32 -0700, jdrake@.living-dead.net wrote:

Quote:

Originally Posted by

>Hi,
>
>I have a large table with a 'datetime' column that has date and time
>values in it. The data is in this format:
>
>2007-10-02 09:54:00.000
>
>The table is called 'profile' and the column 'msgdate'
>
>I want to return only rows that match a specific date. So far I have
>the following query working:
>
>select * from profile where msgdate like '%2007%'
>
>This returns all rows that start with '2007'. However I cannot seem to
>ge the syntax that will allow me to return a specific date, e.g.
>2007-10-02
>
>I have researched this, trying all sorts of queries with escape
>characters/sequences because of the dash character, but I cannot get
>it to return anything. Most of my queries have ran without error, its
>just that no data is returned.
>
>
>James

|||Something like this (untested, but you see the point):

select <col list>
from profile
where msgdate >= @.date and msgdate < dateadd(dd,1,@.date)

date would be a parameter...

MC

<jdrake@.living-dead.netwrote in message
news:1192208492.248548.49030@.e34g2000pro.googlegro ups.com...

Quote:

Originally Posted by

Hi,
>
I have a large table with a 'datetime' column that has date and time
values in it. The data is in this format:
>
2007-10-02 09:54:00.000
>
The table is called 'profile' and the column 'msgdate'
>
I want to return only rows that match a specific date. So far I have
the following query working:
>
select * from profile where msgdate like '%2007%'
>
This returns all rows that start with '2007'. However I cannot seem to
ge the syntax that will allow me to return a specific date, e.g.
2007-10-02
>
I have researched this, trying all sorts of queries with escape
characters/sequences because of the dash character, but I cannot get
it to return anything. Most of my queries have ran without error, its
just that no data is returned.
>
>
James
>