Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
Bill
You will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Friday, March 30, 2012
Problem with Charindex function
Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Problem with Charindex function
Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Problem with Capital Field names in db and sql statements in small letters
Hi,
I integrated a third-party sw that is apparently used in hundreds of sites into my site. That sw has all field names in (Access) db in capital letters and uses small letters in sql statements in the code and that creates a problem on my site. The third-party sw developers say that this is caused by a false setting on the Web Server so they suggest I change my hosting. I know that if the field names and the sql statements in the code are both capital letters or both small letters everything works.
Does anybody have any idea about how to solve this problem? Is it true that it is a hosting problem? And if so could anybody please tell me how to correct web server setting?
Thanks a lot in advance,
CananWhat errors are you getting? It doesn't sound like a web setting to me. It sounds like typical third-party passing the buck crap.
It sounds like your web hosting company has a case-sensitive version of SQL Server. Either that or Access is case sensitive (not even sure if you can do that)??? :)
I think it's safe to say if it works by correcting the code it's not your webhost though.|||Thanks for your quick response. We found out that after v2.5 of MDAC this problem occurs if the server setting is Turkish. So this was our problem. And Microsoft Support pages suggest we use db field names and the sql statement in the same case to solve this problem if the server has Turkish settings ! We changed the Turkish settings on the server back to English but no luck the problem persisted. So what we did we changed the field names to small letters ! Not a nice way of solving a problem but works out... :)
Thanks again,
Canan
I integrated a third-party sw that is apparently used in hundreds of sites into my site. That sw has all field names in (Access) db in capital letters and uses small letters in sql statements in the code and that creates a problem on my site. The third-party sw developers say that this is caused by a false setting on the Web Server so they suggest I change my hosting. I know that if the field names and the sql statements in the code are both capital letters or both small letters everything works.
Does anybody have any idea about how to solve this problem? Is it true that it is a hosting problem? And if so could anybody please tell me how to correct web server setting?
Thanks a lot in advance,
CananWhat errors are you getting? It doesn't sound like a web setting to me. It sounds like typical third-party passing the buck crap.
It sounds like your web hosting company has a case-sensitive version of SQL Server. Either that or Access is case sensitive (not even sure if you can do that)??? :)
I think it's safe to say if it works by correcting the code it's not your webhost though.|||Thanks for your quick response. We found out that after v2.5 of MDAC this problem occurs if the server setting is Turkish. So this was our problem. And Microsoft Support pages suggest we use db field names and the sql statement in the same case to solve this problem if the server has Turkish settings ! We changed the Turkish settings on the server back to English but no luck the problem persisted. So what we did we changed the field names to small letters ! Not a nice way of solving a problem but works out... :)
Thanks again,
Canan
Wednesday, March 28, 2012
Problem with blob field in SQL Server
In SQLServer database i have stored a blob using updateblob function,
type of the table column is image.
When i use Selectblob statement to get the blob into a blob variable, i am getting only 32 KB of data. But with ASA i am getting the complete data..
How to get Complete data in SQL Server?
type of the table column is image.
When i use Selectblob statement to get the blob into a blob variable, i am getting only 32 KB of data. But with ASA i am getting the complete data..
How to get Complete data in SQL Server?
regds.,
rangaWhich data provider are you using?
Terrisql
Monday, March 26, 2012
Problem with an IIF Statement
In my group footer I build a string based on the number of rows in that
group plus the field the group was based on.
But if the group was based on a an empty string I get an #error in the
textbox.
So I wrapped the code with an IIF statement. If the field is empty do
nothing otherwise build the string.
= IIF(Fields!AdjustmentCode.Value = "","No Adjustment
Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
Description: " & Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower)FYI this is the error: The value expression for the textbox
'textbox35' contains an error: Object variable or With block
variable not set.|||VB.Net evaluates all parts of the IIF which is why you get the error. It
does not just evaluate the true portion. You could use code behind report to
do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128709777.373098.129000@.g49g2000cwa.googlegroups.com...
> In my group footer I build a string based on the number of rows in that
> group plus the field the group was based on.
> But if the group was based on a an empty string I get an #error in the
> textbox.
> So I wrapped the code with an IIF statement. If the field is empty do
> nothing otherwise build the string.
> = IIF(Fields!AdjustmentCode.Value = "","No Adjustment
> Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
> Description: " & Fields!AdjustmentCode.Value.trim & " - " &
> Fields!AdjustmentDescription.Value.tolower)
>|||Can you point me in some direction for a Code Behind example. I can't
find anything yet.
Thanks.|||Look in books on line for the phrase code block
They have an example with that. I tend to develop my code in vb.net, test it
and then copy it over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128712212.284978.143180@.z14g2000cwz.googlegroups.com...
> Can you point me in some direction for a Code Behind example. I can't
> find anything yet.
> Thanks.
>|||All i see is XML. This is going to be a lot of work to just handle the
fact the the Rpt can't handle a blank value.
So your saying I can put vb code in this.
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>25</ZIndex>
<rd:DefaultName>textbox35</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= countrows("grpAdjustmentDesc") &
" Records for Adjustment Code Description: " &
Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower</Value>
</Textbox>|||Can you post the hyperlink from the RSBook online address bar to where
the sample is.|||I put this in the code window
<Code>
Public Function test() As String
Dim strTest As String
strTest = "BLAH BLAH BLAH"
Return strTest
End Function
</Code>
I put this in a textbox
= code.test()
d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
custom code: [BC32035] Attribute specifier is not a complete statement.
Use a line continuation to apply the attribute to the following
statement.
And this is my error|||You should not be having to modify the rdl at all (which is what you are
doing).
Search Books Online for the phrase: writing custom code
Also these two links:
ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_expressions_v1_84f9.htm
ms-help://MS.RSBOL80.1033/RShowto/htm/hrs_designer_v1_1nfp.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128719715.084263.60890@.g43g2000cwa.googlegroups.com...
>I put this in the code window
> <Code>
> Public Function test() As String
> Dim strTest As String
> strTest = "BLAH BLAH BLAH"
> Return strTest
> End Function
> </Code>
>
> I put this in a textbox
> = code.test()
>
> d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
> custom code: [BC32035] Attribute specifier is not a complete statement.
> Use a line continuation to apply the attribute to the following
> statement.
> And this is my error
>|||Thanks for the links. I finally got it to work
I had cut and pasted the code from this article
http://www.15seconds.com/issue/041110.htm and I had the tags in there
which were getting placed in the XML definition
Thanks for the quick responses
group plus the field the group was based on.
But if the group was based on a an empty string I get an #error in the
textbox.
So I wrapped the code with an IIF statement. If the field is empty do
nothing otherwise build the string.
= IIF(Fields!AdjustmentCode.Value = "","No Adjustment
Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
Description: " & Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower)FYI this is the error: The value expression for the textbox
'textbox35' contains an error: Object variable or With block
variable not set.|||VB.Net evaluates all parts of the IIF which is why you get the error. It
does not just evaluate the true portion. You could use code behind report to
do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128709777.373098.129000@.g49g2000cwa.googlegroups.com...
> In my group footer I build a string based on the number of rows in that
> group plus the field the group was based on.
> But if the group was based on a an empty string I get an #error in the
> textbox.
> So I wrapped the code with an IIF statement. If the field is empty do
> nothing otherwise build the string.
> = IIF(Fields!AdjustmentCode.Value = "","No Adjustment
> Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
> Description: " & Fields!AdjustmentCode.Value.trim & " - " &
> Fields!AdjustmentDescription.Value.tolower)
>|||Can you point me in some direction for a Code Behind example. I can't
find anything yet.
Thanks.|||Look in books on line for the phrase code block
They have an example with that. I tend to develop my code in vb.net, test it
and then copy it over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128712212.284978.143180@.z14g2000cwz.googlegroups.com...
> Can you point me in some direction for a Code Behind example. I can't
> find anything yet.
> Thanks.
>|||All i see is XML. This is going to be a lot of work to just handle the
fact the the Rpt can't handle a blank value.
So your saying I can put vb code in this.
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>25</ZIndex>
<rd:DefaultName>textbox35</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= countrows("grpAdjustmentDesc") &
" Records for Adjustment Code Description: " &
Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower</Value>
</Textbox>|||Can you post the hyperlink from the RSBook online address bar to where
the sample is.|||I put this in the code window
<Code>
Public Function test() As String
Dim strTest As String
strTest = "BLAH BLAH BLAH"
Return strTest
End Function
</Code>
I put this in a textbox
= code.test()
d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
custom code: [BC32035] Attribute specifier is not a complete statement.
Use a line continuation to apply the attribute to the following
statement.
And this is my error|||You should not be having to modify the rdl at all (which is what you are
doing).
Search Books Online for the phrase: writing custom code
Also these two links:
ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_expressions_v1_84f9.htm
ms-help://MS.RSBOL80.1033/RShowto/htm/hrs_designer_v1_1nfp.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128719715.084263.60890@.g43g2000cwa.googlegroups.com...
>I put this in the code window
> <Code>
> Public Function test() As String
> Dim strTest As String
> strTest = "BLAH BLAH BLAH"
> Return strTest
> End Function
> </Code>
>
> I put this in a textbox
> = code.test()
>
> d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
> custom code: [BC32035] Attribute specifier is not a complete statement.
> Use a line continuation to apply the attribute to the following
> statement.
> And this is my error
>|||Thanks for the links. I finally got it to work
I had cut and pasted the code from this article
http://www.15seconds.com/issue/041110.htm and I had the tags in there
which were getting placed in the XML definition
Thanks for the quick responses
Problem with ampersand "&"
I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (using
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.
Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&"
|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?
Joe Fawcett - XML MVP
http://joe.fawcett.name
|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value><![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entitized.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi Rob
sorry for the late reply.
But you should never extract values from an XML document using
CONVERT(nvarchar(MAX), T.c.query('data(Value)'))
use
T.c.value('Value', 'nvarchar(MAX)') instead. That should take care of your
encoding issue.
Best regards
Michael
"Rob Epler" <Rob Epler@.discussions.microsoft.com> wrote in message
news:BFFD350A-2B24-4133-8CEB-FCF5F700D560@.microsoft.com...
> I'm using XQUERY to slog through an XML doc, and dump the value of a CDATA
> element into an NVARCHAR(MAX) field. The problem I'm running into is that
> when an ampersand goes into that field, what I get back out in a query
> (using
> FOR XML) is "&".
> using:
> CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
> The above stores a "&" in the field, even though the input was:
> <![CDATA[&]>
> Any insight is much appreciated.
>
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (using
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.
Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&"
|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?
Joe Fawcett - XML MVP
http://joe.fawcett.name
|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value><![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entitized.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi Rob
sorry for the late reply.
But you should never extract values from an XML document using
CONVERT(nvarchar(MAX), T.c.query('data(Value)'))
use
T.c.value('Value', 'nvarchar(MAX)') instead. That should take care of your
encoding issue.
Best regards
Michael
"Rob Epler" <Rob Epler@.discussions.microsoft.com> wrote in message
news:BFFD350A-2B24-4133-8CEB-FCF5F700D560@.microsoft.com...
> I'm using XQUERY to slog through an XML doc, and dump the value of a CDATA
> element into an NVARCHAR(MAX) field. The problem I'm running into is that
> when an ampersand goes into that field, what I get back out in a query
> (using
> FOR XML) is "&".
> using:
> CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
> The above stores a "&" in the field, even though the input was:
> <![CDATA[&]>
> Any insight is much appreciated.
>
Problem with ampersand "&"
I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (usin
g
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&"|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?
Joe Fawcett - XML MVP
http://joe.fawcett.name|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value>
<![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entiti
zed.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/sql
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (usin
g
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&"|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?
Joe Fawcett - XML MVP
http://joe.fawcett.name|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value>
<![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entiti
zed.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/sql
Friday, March 23, 2012
Problem With Adding a Date Field to a Database When It is Null
I am having problems adding a date field to a SQL Server Database from a form in ASP.Net. When I leave the date field blank, it automatically inserts Monday, January 01, 1900. I want it to be null when the expiration date is left blank. Can someone please help me with this?
Here's my code for adding information from the table to the database:
'--------------
' name: Button_Click()
'--------------
Sub Button_Click( s As Object, e As EventArgs )
Dim strConnect As String
Dim objConnect As SQLConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
'Get connection string from Web.Config
strConnect = ConfigurationSettings.AppSettings("ConnectionString")
objConnect = New SqlConnection(strConnect)
strInsert = "Insert DomainName (ClientID, DomainName, Registrar, ExpirationDate ) Values ( @.ClientID, @.DomainName, @.Registrar, @.ExpirationDate )"
cmdInsert = New SqlCommand( strInsert, objConnect)
cmdInsert.Parameters.Add( "@.ClientID", dropClient.SelectedItem.Value )
cmdInsert.Parameters.Add( "@.DomainName", txtDomainName.Text )
cmdInsert.Parameters.Add( "@.Registrar", txtRegistrar.Text )
cmdInsert.Parameters.Add( "@.ExpirationDate", txtExpirationDate.Text )
objConnect.Open()
cmdINsert.ExecuteNonQuery()
objConnect.Close()
'Display the results "page"
DisplayResults()
End Sub
Here's the code for the form:
<form id="frmDomainNames" method="post" runat="server" onSubmit="return InputIsValid()">
<div align="center">
<table border="0" cellpadding="2" cellspacing="2" width="50%" bgcolor="#330099">
<tr>
<td height="37" colspan="2" align="center" valign="middle" bgcolor="#330099"><font color="white" size="5">Domain Name Information</font></td>
<td> </td>
</tr>
<tr>
<td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>
Client's Name:</nobr></strong></font></td>
<td colspan="2" valign="top" bgcolor="#e8e8e8">
<p>
<asp:dropdownlist id="dropClient" runat="server" />
</p>
</td>
</tr>
<tr>
<td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>
Domain Name:</nobr></strong></font></td>
<td colspan="2" valign="top" bgcolor="#e8e8e8">
<p>
<ASP:TextBox id="txtDomainName" runat="server" TextMode="SingleLine" Columns="30" />
</p>
</td>
<tr>
<td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>
Registrar:</nobr></strong></font></td>
<td colspan="2" valign="top" bgcolor="#e8e8e8">
<p>
<ASP:TextBox id="txtRegistrar" runat="server" TextMode="SingleLine" Columns="30" />
</p>
</td>
</tr>
<tr>
<td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>
Expiration Date:</nobr></strong></font></td>
<td colspan="2" valign="top" bgcolor="#e8e8e8">
<p>
<ASP:TextBox id="txtExpirationDate" runat="server" TextMode="SingleLine" Columns="10" />
</p>
</td>
</tr>
<TR>
<TD>
</TD>
<TD align="center">
<asp:Button
Text="Submit"
OnClick="Button_Click"
Runat="Server" />
</TD>
</TR>
</table>
</form>
</div>
cmdInsert.Parameters.Add( "@.ExpirationDate", txtExpirationDate.Text )
to
cmdInsert.Parameters.Add( "@.ExpirationDate", iif(txtExpirationDate.Text="",dbNull.value,txtExpirationDate.text))
Nick
Problem with a where clause
I'd like to set the where clause of my query from a field in a table
ex:
Select TableX.field1
from tableX, TableY
where TableY.field2
Field2 is equal to "fieldW like '%blabla%' "
Thanks for your helpDynamic SQL:
DECLARE @.sqlStmt VARCHAR(2000)
DECLARE @.whereClause VARCHAR(500)
SELECT @.whereClause = field2
FROM tableY
WHERE {tableY.key_column} = {value}
SELECT @.sqlStmt = 'SELECT tableX.field1 FROM tableX, tableY WHERE '
SELECT @.sqlStmt = @.sqlStmt + @.whereClause
EXEC (@.sqlStmt)
Be careful about SQL Injection using this method.
"Epervier" <Epervier@.discussions.microsoft.com> wrote in message
news:D8F4A0A0-37CE-4FA5-B9CC-771BCE61B5FF@.microsoft.com...
> I'd like to set the where clause of my query from a field in a table
> ex:
> Select TableX.field1
> from tableX, TableY
> where TableY.field2
> Field2 is equal to "fieldW like '%blabla%' "
> Thanks for your help
ex:
Select TableX.field1
from tableX, TableY
where TableY.field2
Field2 is equal to "fieldW like '%blabla%' "
Thanks for your helpDynamic SQL:
DECLARE @.sqlStmt VARCHAR(2000)
DECLARE @.whereClause VARCHAR(500)
SELECT @.whereClause = field2
FROM tableY
WHERE {tableY.key_column} = {value}
SELECT @.sqlStmt = 'SELECT tableX.field1 FROM tableX, tableY WHERE '
SELECT @.sqlStmt = @.sqlStmt + @.whereClause
EXEC (@.sqlStmt)
Be careful about SQL Injection using this method.
"Epervier" <Epervier@.discussions.microsoft.com> wrote in message
news:D8F4A0A0-37CE-4FA5-B9CC-771BCE61B5FF@.microsoft.com...
> I'd like to set the where clause of my query from a field in a table
> ex:
> Select TableX.field1
> from tableX, TableY
> where TableY.field2
> Field2 is equal to "fieldW like '%blabla%' "
> Thanks for your help
Wednesday, March 21, 2012
Problem with a join due to multiple Nulls
I want to join 2 tables by a unique ID field, but the ID field also has
multiple NULLS which I do not want to ignore and I fear they will cause
duplication.
multiple NULLS which I do not want to ignore and I fear they will cause
duplication.
Using TableA and TableB below i will demonstrate the problem.
TableA
TableA.ID Field1 Field2
1 Paul 1
Null John 1
2 John 1
TableB
TableB.ID Field3 Field4
1 25 1
Null 32 1
Null 23 1
2 26 1
The Table I want is
TableA.ID TableB.ID Field1 Field2 Field3 Field4
1 1 Paul 1 25 1
2 2 John 1 26 1
Null Null John 1 Null Null
Null Null Null Null 32 1
Null Null Null Null 26 1
I think a select distcinct statement with a full outer join may do what
I want, but I'm not certain so want to check.
Regards,
Ciarnchudson...@.hotmail.com wrote:
> I want to join 2 tables by a unique ID field, but the ID field also has
> multiple NULLS which I do not want to ignore and I fear they will cause
> duplication.
> Using TableA and TableB below i will demonstrate the problem.
> TableA
> TableA.ID Field1 Field2
> 1 Paul 1
> Null John 1
> 2 John 1
>
> TableB
> TableB.ID Field3 Field4
> 1 25 1
> Null 32 1
> Null 23 1
> 2 26 1
> The Table I want is
> TableA.ID TableB.ID Field1 Field2 Field3 Field4
> 1 1 Paul 1 25 1
> 2 2 John 1 26 1
> Null Null John 1 Null Null
> Null Null Null Null 32 1
> Null Null Null Null 26 1
>
> I think a select distcinct statement with a full outer join may do what
> I want, but I'm not certain so want to check.
> Regards,
> Ciarn
It appears that TableA doesn't have a key. Not clear what the key is in
TableB either. Please post DDL rather than sketches of tables otherwise
we just have to guess.
Based on what you've posted I'd say you need to fix some data model
issues (missing keys) before you attempt your query. Maybe that's what
you are trying to do but it isn't obvious how your requested output
will help you.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||TableA.ID and TableB.ID are the respective keys, but the problem is
that they contain multiple Nulls
Regards,
Ciarn|||(chudson007@.hotmail.com) writes:
> I want to join 2 tables by a unique ID field, but the ID field also has
> multiple NULLS which I do not want to ignore and I fear they will cause
> duplication.
> Using TableA and TableB below i will demonstrate the problem.
> TableA
> TableA.ID Field1 Field2
> 1 Paul 1
> Null John 1
> 2 John 1
>
> TableB
> TableB.ID Field3 Field4
> 1 25 1
> Null 32 1
> Null 23 1
> 2 26 1
> The Table I want is
> TableA.ID TableB.ID Field1 Field2 Field3 Field4
> 1 1 Paul 1 25 1
> 2 2 John 1 26 1
> Null Null John 1 Null Null
> Null Null Null Null 32 1
> Null Null Null Null 26 1
The IDs cannot really be keys if there are NULL values, even less if there
are multiple NULL.
If I'm taking a guess of what you are looking for, this might be it:
SELECT a.ID, b.ID, a.Field1, a.Field2, b.Field3, b.Field4
FROM TableA a
JOIN TableB b ON a.ID = b.ID
UNION ALL
SELECT NULL, NULL, a.Field1, a.Field2, NULL, NULL
FROM TableA a
WHERE a.ID IS NULL
UNION ALL
SELECT NULL, NULL, NULL, NULL, b.Field3, b.Field4
FROM TableB b
WHERE b.ID IS NULL
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||chudson007@.hotmail.com wrote:
> TableA.ID and TableB.ID are the respective keys, but the problem is
> that they contain multiple Nulls
> Regards,
> Ciarn
If they contain nulls then they aren't keys. Every table should have a
key. Fix the design first.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||I like your logic Erland.
That'll work perfectly thanks.|||>> want to join 2 tables by a unique ID field [sic], but the ID field [sic] also has multiple NULLS which I do not want to ignore and I fear they will cause duplication. <<
Unh? "Unique identifier" is redundant and columns are not anything
like fields. Looking at your personal narrative, I see the magical,
vague "id" column violating the basic principle of data modeling that
data elements name distinct things and are not magical uiversal vague
creatures.
Please post some DDL with a key in each table. Without a key, these
things are not tables by definition.
You might also wantto take the time to learn RDBMS, so you so you do
not embarass yourself in future postings.|||David,
Unfortunately I'm working with data exttracts as opposed to a well
designed system.
The code below demonstrates what i am trying to do.
In TableA My_ID is is either Null or a unique number.
In TableB My_ID is either Null or a number which may not be unique.
I tried using a full outer join the other day, but after 15 hours it
still had not worked so I think I am doing somethiing wrong and need
help.
In my real data TableA contains just under 2 million records 1.2million
of which My_ID is null and TableB conatins 5million records of which
almost 3 million of which My_ID is null.
CREATE TABLE TableA ( My_ID nvarchar(4000),Field1
nvarchar(4000),CounterA nvarchar(4000))
GO
INSERT INTO TableA
SELECT
'1', 'Paul','1'
UNION all SELECT
'2', 'John','1'
UNION all SELECT
'3', 'Mark','1'
UNION all SELECT
Null, 'Simon','1'
UNION all SELECT
Null, 'Peter','1'
CREATE TABLE TableB ( My_ID nvarchar(4000),Field2
nvarchar(4000),CounterB nvarchar(4000))
GO
INSERT INTO TableB
SELECT
'1', '23','1'
UNION all SELECT
'1', '24','1'
UNION all SELECT
'4', '26','1'
UNION all SELECT
Null, '27','1'
UNION all SELECT
Null, '28','1'
SELECT *
FROM TableA FULL OUTER JOIN
TableB ON TableA.My_ID = TableB.My_ID
Regards,
Ciarn|||chudson007@.hotmail.com wrote:
> David,
> Unfortunately I'm working with data exttracts as opposed to a well
> designed system.
> The code below demonstrates what i am trying to do.
> In TableA My_ID is is either Null or a unique number.
> In TableB My_ID is either Null or a number which may not be unique.
> I tried using a full outer join the other day, but after 15 hours it
> still had not worked so I think I am doing somethiing wrong and need
> help.
> In my real data TableA contains just under 2 million records 1.2million
> of which My_ID is null and TableB conatins 5million records of which
> almost 3 million of which My_ID is null.
I can see that the system isn't well designed. What I'm suggesting is
that you fix it. I assume what you mean by "data extracts" is that you
are importing some data into a database from an outside source over
which you don't have any control. Can't you create your own tables and
import the data into them? That's what I recommend: convert your source
data into a normalized data model BEFORE you attempt any further
processing. You obviously haven't done that yet and I don't understand
what you are trying to achieve with this FULL JOIN. Why would you want
to return a join consisting of 7 million rows?
If you aren't permitted to implement a better design then maybe you'll
have to live with sub-optimal performance. I can't redesign your tables
for you because I don't know what your data means or what the result
you've asked for means.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Unfortunately I am not able to implement a better design.
Is there any more optimal way to ensure I have all transactions from
each table and a match wherever an inner join exists?
Regards,
Ciarn|||don't worry, celko says this to everyone. he can't actually help
anyone, he just finds excuses as to why he can't.
Problem with @@identity return in stored procedure insert.
I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.
CREATE PROCEDURE addMagicRecTest
(
@.theSequence int,
@.theSubject int,
@.theFirstName nvarchar(50)=null
@.theLastName nvarchar(75)=null
)AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@.theSequence,@.theSubject,@.theFirstName,@.theLastName)
SELECT @.@.identity AS finalNummagicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@.theSequence").Value = "41833"' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@.theSubject").Value = "1064"' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theFirstName", SqlDbType.NVarChar, 50))
magicCommand.SelectCommand.Parameters("@.theFirstName").Value = orderFirstName' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@.theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@.theLastName").Value = orderLastNameDSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If
I need finalMagicNum2I usually just have the stored proc return the @.@.identity like so:
CREATE PROCEDURE addMagicRecTest(
@.theSequence int,
@.theSubject int,
@.theFirstName nvarchar(50)=null
@.theLastName nvarchar(75)=null
)
AS
INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@.theSequence,@.theSubject,@.theFirstName,@.theLastName)
Return @.@.Identity
Your stored proc is not returning finalnum and your code has no output param set up in it.
Sam|||Try this code in .NET
DSMagic = new DataSet()magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables(0).Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables(0).Rows(0).item("finalnum").toString
'OR
'finalMagicNum2 = DSMagic.Tables(0).Rows(0).item(0).toStringEnd If
Hope this help
problem with >>insert into varchar field shows "?"
i would like to insert thai characters into varchar field in Database. and show that to a label.
now the problem is when i insert into database it becomes "?"
and when i display to Lable also "?".
is there anyway to fix this?
input comes from Textbox(vb.net)
output to Label.
thanks.
thompson.
You need to specifically 'tell' SQL Server that the incoming data is UNICODE. The common method to do that is to preface the data with the letter [ N ].
It would be done something like this:
INSERT INTO MyTable ( MyColumn ) VALUES ( N'MyUnicodeValue' )
|||Including Arnie Rowland's suggestion, check the datatype of your table column. It shoule be NVarchar. Varchar only supports ASCII chars, if you want to insert Unicode values then it should be NVarchar/NChar/NText datatype.
Note: Varchar is not equal to NVarchar
|||thanks to everyone answer this post. the problem has been solved.problem with >>insert into varchar field shows "?"
i would like to insert thai characters into varchar field in Database. and show that to a label.
now the problem is when i insert into database it becomes "?"
and when i display to Lable also "?".
is there anyway to fix this?
input comes from Textbox(vb.net)
output to Label.
thanks.
thompson.
You need to specifically 'tell' SQL Server that the incoming data is UNICODE. The common method to do that is to preface the data with the letter [ N ].
It would be done something like this:
INSERT INTO MyTable ( MyColumn ) VALUES ( N'MyUnicodeValue' )
|||Including Arnie Rowland's suggestion, check the datatype of your table column. It shoule be NVarchar. Varchar only supports ASCII chars, if you want to insert Unicode values then it should be NVarchar/NChar/NText datatype.
Note: Varchar is not equal to NVarchar
|||thanks to everyone answer this post. the problem has been solved.Tuesday, March 20, 2012
problem with convert 2000 server 2000
we want to insert date into table testDate in Datetime or another dataType in field testDate and extract it in hijry Date format And compare it with two Date.
acording to the articles we insert it in to the table in nchar dataType and then we want to extract it in datetime dataType using convert function in sql server :
(( SELECT CONVERT(datetime, ' testDate', 131)
FROM testDate ))
that gives this error:
(( Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string. ))
if we want to insert Date in datetime dataType it enter in gregorian Date format.
while we want Date in hijry format.try this, it may work? You will have to sort the hours and minutes etc...
convert( smalldatetime, ( datename( yy, testdate) + ' '
+ datename( mm, testdate) + ' '
+ datename( dd, testdate)
))
acording to the articles we insert it in to the table in nchar dataType and then we want to extract it in datetime dataType using convert function in sql server :
(( SELECT CONVERT(datetime, ' testDate', 131)
FROM testDate ))
that gives this error:
(( Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string. ))
if we want to insert Date in datetime dataType it enter in gregorian Date format.
while we want Date in hijry format.try this, it may work? You will have to sort the hours and minutes etc...
convert( smalldatetime, ( datename( yy, testdate) + ' '
+ datename( mm, testdate) + ' '
+ datename( dd, testdate)
))
Problem whit "." or "," in field MONEY
Hi guys...
UPDATE PRODUCT SET PRICE='1,11' WHERE COD='001'
but, in field "PRICE" = 111,00
I don′t need "." but i need to use ","
UPDATE PRODUCT SET PRICE='1.11' WHERE COD='001'
Works perfectly... but all sql commands in applications use ","
In Server
User′s set LANGUAGE=PORTUGUESES
DEFAULT LANGUAGE = PORTUGUESES
Tks All
If i'm correct then you are using money or decimal for price.
use varchar for price and use CHAR(44) for ',' and format the string.
Monday, March 12, 2012
problem when trying to query on datetime field
Hello All ,
my name is ron ,
i have the following problem:
when i running the query on my Sql server Database:
SELECT AVG(VOLUME) AS AvgVolume
FROM STOCKS_VOLUME
WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
i get an error:
The conversion of char data type to a dattime data type resulted in an
out-of-ranged datetime value .
what is wrong with my query does anyone have any idea how to solve this ?
thanksUse a safe and language neutral datetime format for your datetime literal. I
prefer the unseparated format:
'yyyymmdd'
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Roni Bar Yosef" <ronby@.malamall.co.il> wrote in message
news:402a4c5f@.news.bezeqint.net...
> Hello All ,
> my name is ron ,
> i have the following problem:
> when i running the query on my Sql server Database:
> SELECT AVG(VOLUME) AS AvgVolume
> FROM STOCKS_VOLUME
> WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
> i get an error:
> The conversion of char data type to a dattime data type resulted in an
> out-of-ranged datetime value .
> what is wrong with my query does anyone have any idea how to solve this ?
> thanks
>
my name is ron ,
i have the following problem:
when i running the query on my Sql server Database:
SELECT AVG(VOLUME) AS AvgVolume
FROM STOCKS_VOLUME
WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
i get an error:
The conversion of char data type to a dattime data type resulted in an
out-of-ranged datetime value .
what is wrong with my query does anyone have any idea how to solve this ?
thanksUse a safe and language neutral datetime format for your datetime literal. I
prefer the unseparated format:
'yyyymmdd'
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Roni Bar Yosef" <ronby@.malamall.co.il> wrote in message
news:402a4c5f@.news.bezeqint.net...
> Hello All ,
> my name is ron ,
> i have the following problem:
> when i running the query on my Sql server Database:
> SELECT AVG(VOLUME) AS AvgVolume
> FROM STOCKS_VOLUME
> WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
> i get an error:
> The conversion of char data type to a dattime data type resulted in an
> out-of-ranged datetime value .
> what is wrong with my query does anyone have any idea how to solve this ?
> thanks
>
problem when trying to query on datetime field
Hello All ,
my name is ron ,
i have the following problem:
when i running the query on my Sql server Database:
SELECT AVG(VOLUME) AS AvgVolume
FROM STOCKS_VOLUME
WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
i get an error:
The conversion of char data type to a dattime data type resulted in an
out-of-ranged datetime value .
what is wrong with my query does anyone have any idea how to solve this ?
thanksUse a safe and language neutral datetime format for your datetime literal. I
prefer the unseparated format:
'yyyymmdd'
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Roni Bar Yosef" <ronby@.malamall.co.il> wrote in message
news:402a4c5f@.news.bezeqint.net...
> Hello All ,
> my name is ron ,
> i have the following problem:
> when i running the query on my Sql server Database:
> SELECT AVG(VOLUME) AS AvgVolume
> FROM STOCKS_VOLUME
> WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
> i get an error:
> The conversion of char data type to a dattime data type resulted in an
> out-of-ranged datetime value .
> what is wrong with my query does anyone have any idea how to solve this ?
> thanks
>
my name is ron ,
i have the following problem:
when i running the query on my Sql server Database:
SELECT AVG(VOLUME) AS AvgVolume
FROM STOCKS_VOLUME
WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
i get an error:
The conversion of char data type to a dattime data type resulted in an
out-of-ranged datetime value .
what is wrong with my query does anyone have any idea how to solve this ?
thanksUse a safe and language neutral datetime format for your datetime literal. I
prefer the unseparated format:
'yyyymmdd'
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Roni Bar Yosef" <ronby@.malamall.co.il> wrote in message
news:402a4c5f@.news.bezeqint.net...
> Hello All ,
> my name is ron ,
> i have the following problem:
> when i running the query on my Sql server Database:
> SELECT AVG(VOLUME) AS AvgVolume
> FROM STOCKS_VOLUME
> WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
> i get an error:
> The conversion of char data type to a dattime data type resulted in an
> out-of-ranged datetime value .
> what is wrong with my query does anyone have any idea how to solve this ?
> thanks
>
Friday, March 9, 2012
problem when i try to modifie a field of a table in transactional
Hy,
i have the following problem:
i try to modifie an article (table) in a context of transactional
replication with queue updating in two way:
1- first way with sp_repladdcolumn and sp_repldropcolumn:
add a temporary field to the article:
exec sp_repladdcolumn @.source_object='Alc_AllarmiCritici'
,@.column='TempTipoEvento'
,@.typetext='nvarchar(100) NULL'
,@.publication_to_add='Chironpubb',
@.force_reinit_subscription =1
copy the data of the original column in the temporary fields
update Alc_AllarmiCritici set TempTipoEvento=TipoEvento
drop th original column
exec sp_repldropcolumn
@.source_object='alc_allarmicritici',@.column='TipoE vento'
add a new empty column with the name and data type of the temporary
exec sp_repladdcolumn @.source_object='Alc_AllarmiCritici'
,@.column='TipoEvento'
,@.typetext='nvarchar(100) NULL'
,@.publication_to_add='Chironpubb'
copy the date from temporary colum in the new empty column
update Alc_AllarmiCritici set TipoEvento=TempTipoEvento
drop the temporary table
exec sp_repldropcolumn @.source_object='alc_allarmicritici'
,@.column='TempTipoEvento'
then, i start the snapshot agent
the update of the structure is delivered th the subscriver susccessfully
the problem was that the field now is in the last positon in the table and
not in the same position before the updating. Therefore applications doesnt
function becouse the position of the fields have changed.
Does anybody know how i can resove the trouble?
2 - I exclude from replication the article with sp_dropsubscription and
sp_droparticle , i modified the article and then i put the article in the
replication with sp_addsubscription and sp_addarticle.
In this case sincronization from publisher to subscriver is ok.
Sincronization from subscriver to publisher doesn't function. Should i
create triggers for queue updating?
help me please, thankyou
Luca Schiavon
MCSD
(developer)
Luca,
mostly the advice is to make your application not dependant on column order
and to use column names instead. If this is not possible, then you can
change the column order on the publisher and reinitialize the table (drop
the article and then readd).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
i have the following problem:
i try to modifie an article (table) in a context of transactional
replication with queue updating in two way:
1- first way with sp_repladdcolumn and sp_repldropcolumn:
add a temporary field to the article:
exec sp_repladdcolumn @.source_object='Alc_AllarmiCritici'
,@.column='TempTipoEvento'
,@.typetext='nvarchar(100) NULL'
,@.publication_to_add='Chironpubb',
@.force_reinit_subscription =1
copy the data of the original column in the temporary fields
update Alc_AllarmiCritici set TempTipoEvento=TipoEvento
drop th original column
exec sp_repldropcolumn
@.source_object='alc_allarmicritici',@.column='TipoE vento'
add a new empty column with the name and data type of the temporary
exec sp_repladdcolumn @.source_object='Alc_AllarmiCritici'
,@.column='TipoEvento'
,@.typetext='nvarchar(100) NULL'
,@.publication_to_add='Chironpubb'
copy the date from temporary colum in the new empty column
update Alc_AllarmiCritici set TipoEvento=TempTipoEvento
drop the temporary table
exec sp_repldropcolumn @.source_object='alc_allarmicritici'
,@.column='TempTipoEvento'
then, i start the snapshot agent
the update of the structure is delivered th the subscriver susccessfully
the problem was that the field now is in the last positon in the table and
not in the same position before the updating. Therefore applications doesnt
function becouse the position of the fields have changed.
Does anybody know how i can resove the trouble?
2 - I exclude from replication the article with sp_dropsubscription and
sp_droparticle , i modified the article and then i put the article in the
replication with sp_addsubscription and sp_addarticle.
In this case sincronization from publisher to subscriver is ok.
Sincronization from subscriver to publisher doesn't function. Should i
create triggers for queue updating?
help me please, thankyou
Luca Schiavon
MCSD
(developer)
Luca,
mostly the advice is to make your application not dependant on column order
and to use column names instead. If this is not possible, then you can
change the column order on the publisher and reinitialize the table (drop
the article and then readd).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Problem when field is NULL
I am doing a LEFT JOIN, that should display all files in table a even if
there is no match with table B as far as I know.
The problem is that if the field that joins A with B is NULL then I get no
results, I am trying the following:
SELECT a.Id , a.CaseId , a.EmpId, a.AlienId, a.FirmAddressId, b.FirstNm ,
b.MiddleNm, b.LastNm , b.FirmId, c.Processcatalog , a.MainCase ,
d.MaidenNm
FROM Cases a LEFT JOIN Users as d ON a.EmpId = d.UserId
LEFT JOIN Users as b ON ISNULL (a.AlienId,'0') = b.UserId
INNER JOIN Processcatalog as c ON Process = ProcesscatalogID WHERE
ISNULL (a.CaseID,'') LIKE '%MMColParam%' AND ISNULL (b.LastNm,'') LIKE
'MMColParam1%' AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%' AND
b.Firmid = 'MMColParam3' AND a.archived LIKE 'MMColParam4' and
a.firmaddressid LIKE 'MMColParam5' and ISNULL(a.firmaddressid,'')LIKE
'MMColParam5' AND ISNULL(d.UserID,'') LIKE 'MMColParam6' AND MainCase IS
NOT NULL
ORDER BY a.caseId
---
a = Cases (This is the main table)
b = Contacts (This is the table I am joining to using LEFT join)
Even though I do it like this and I give value = 0 when it is NULL I still
get no matches, and there is at least one results where there is one record
for 'cases' where cases.alienid is NULL, still won't show up.
What am I missing ?
Let me know if you need more info to help, thanks !
AleksOn Fri, 11 Feb 2005 16:00:50 -0500, Aleks wrote:
>I am doing a LEFT JOIN, that should display all files in table a even if
>there is no match with table B as far as I know.
>The problem is that if the field that joins A with B is NULL then I get no
>results, I am trying the following:
>--
>SELECT a.Id , a.CaseId , a.EmpId, a.AlienId, a.FirmAddressId, b.FirstNm ,
>b.MiddleNm, b.LastNm , b.FirmId, c.Processcatalog , a.MainCase ,
>d.MaidenNm
>FROM Cases a LEFT JOIN Users as d ON a.EmpId = d.UserId
>LEFT JOIN Users as b ON ISNULL (a.AlienId,'0') = b.UserId
>INNER JOIN Processcatalog as c ON Process = ProcesscatalogID WHERE
>ISNULL (a.CaseID,'') LIKE '%MMColParam%' AND ISNULL (b.LastNm,'') LIKE
>'MMColParam1%' AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%' AND
>b.Firmid = 'MMColParam3' AND a.archived LIKE 'MMColParam4' and
>a.firmaddressid LIKE 'MMColParam5' and ISNULL(a.firmaddressid,'')LIKE
>'MMColParam5' AND ISNULL(d.UserID,'') LIKE 'MMColParam6' AND MainCase IS
>NOT NULL
>ORDER BY a.caseId
>---
>a = Cases (This is the main table)
>b = Contacts (This is the table I am joining to using LEFT join)
>Even though I do it like this and I give value = 0 when it is NULL I still
>get no matches, and there is at least one results where there is one record
>for 'cases' where cases.alienid is NULL, still won't show up.
>What am I missing ?
Hi Aleks,
All filtering on the outer-join-ed tables should be move to the ON clause.
To explain: if a row from a (Cases) has no match in b (Users), then the
value of b.FirstNm will be NULL (becuase of the outer join). In the WHERE
clause, you have "... AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%'", so
these outer-joined rows will fail this test and be removed from the result
set.
You also don't need to use ISNULL here - NULL LIKE 'MMColParam2%' will not
evaluate to true anyway.
Finally, it's better to use = instead of LIKE when searching for one
specific value. Use LIKE only when searching for patterns.
SELECT a.Id, a.CaseId, a.EmpId, a.AlienId, a.FirmAddressId,
b.FirstNm, b.MiddleNm, b.LastNm, b.FirmId,
c.Processcatalog, a.MainCase, d.MaidenNm
FROM Cases AS a
LEFT JOIN Users AS d
ON d.UserId = a.EmpId
AND d.UserID = 'MMColParam6'
LEFT JOIN Users AS b
ON a.AlienId = b.UserId
AND b.LastNm LIKE 'MMColParam1%'
AND b.FirstNm LIKE 'MMColParam2%'
AND b.Firmid = 'MMColParam3'
INNER JOIN Processcatalog AS c
ON ?.Process = ?.ProcesscatalogID -- better qualify ALL columns
WHERE a.CaseID LIKE '%MMColParam%'
AND a.archived = 'MMColParam4'
AND a.firmaddressid = 'MMColParam5'
AND ?.MainCase IS NOT NULL -- better qualify ALL columns
ORDER BY a.caseId
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I am using LIKE because people search for names using the first letters, not
all the name, that gives them flexibility, I use ISNULL because sometimes
they leave the last name of the first name blank, which is NULL in the
database, but I still need to return that record.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg9q01dk2cr489b62eqtkbq7093gujnv9q@.
4ax.com...
> On Fri, 11 Feb 2005 16:00:50 -0500, Aleks wrote:
>
> Hi Aleks,
> All filtering on the outer-join-ed tables should be move to the ON clause.
> To explain: if a row from a (Cases) has no match in b (Users), then the
> value of b.FirstNm will be NULL (becuase of the outer join). In the WHERE
> clause, you have "... AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%'", so
> these outer-joined rows will fail this test and be removed from the result
> set.
> You also don't need to use ISNULL here - NULL LIKE 'MMColParam2%' will not
> evaluate to true anyway.
> Finally, it's better to use = instead of LIKE when searching for one
> specific value. Use LIKE only when searching for patterns.
> SELECT a.Id, a.CaseId, a.EmpId, a.AlienId, a.FirmAddressId,
> b.FirstNm, b.MiddleNm, b.LastNm, b.FirmId,
> c.Processcatalog, a.MainCase, d.MaidenNm
> FROM Cases AS a
> LEFT JOIN Users AS d
> ON d.UserId = a.EmpId
> AND d.UserID = 'MMColParam6'
> LEFT JOIN Users AS b
> ON a.AlienId = b.UserId
> AND b.LastNm LIKE 'MMColParam1%'
> AND b.FirstNm LIKE 'MMColParam2%'
> AND b.Firmid = 'MMColParam3'
> INNER JOIN Processcatalog AS c
> ON ?.Process = ?.ProcesscatalogID -- better qualify ALL columns
> WHERE a.CaseID LIKE '%MMColParam%'
> AND a.archived = 'MMColParam4'
> AND a.firmaddressid = 'MMColParam5'
> AND ?.MainCase IS NOT NULL -- better qualify ALL columns
> ORDER BY a.caseId
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Feb 2005 16:51:19 -0500, Aleks wrote:
>I am using LIKE because people search for names using the first letters, no
t
>all the name, that gives them flexibility,
Hi Aleks,
Your code didn't allow for user input. It was just
somecol LIKE 'MMColParam2%' (where LIKE is fine) or
somecol LIKE 'MMColParam4' (where no wildcard is used, so LIKE has no
added value; use = to get better performance)
> I use ISNULL because sometimes
>they leave the last name of the first name blank, which is NULL in the
>database, but I still need to return that record.
But if you use ISNULL (a.AlienId,'0') = b.UserId, you'll only return the
row if b.UserID happens to be equal to '0', which is not too likely to
happen. And if you use ISNULL (b.FirstNm,'') LIKE 'MMColParam2%', you'll
only return the row when '' is LIKE 'MMColParam2%', which will definitely
never be the case, so the row won't be returned.
Q1: Did you try the query I suggested? Did it work?
Q2: Since I now have a feeling that your real problem is quite different
from the simplification you posted, could you now post something a little
bit closer to the real issue? If possible with SQL to construct your
tables and fill them with some sample data, and with a description of the
requested output from that sample data. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
there is no match with table B as far as I know.
The problem is that if the field that joins A with B is NULL then I get no
results, I am trying the following:
SELECT a.Id , a.CaseId , a.EmpId, a.AlienId, a.FirmAddressId, b.FirstNm ,
b.MiddleNm, b.LastNm , b.FirmId, c.Processcatalog , a.MainCase ,
d.MaidenNm
FROM Cases a LEFT JOIN Users as d ON a.EmpId = d.UserId
LEFT JOIN Users as b ON ISNULL (a.AlienId,'0') = b.UserId
INNER JOIN Processcatalog as c ON Process = ProcesscatalogID WHERE
ISNULL (a.CaseID,'') LIKE '%MMColParam%' AND ISNULL (b.LastNm,'') LIKE
'MMColParam1%' AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%' AND
b.Firmid = 'MMColParam3' AND a.archived LIKE 'MMColParam4' and
a.firmaddressid LIKE 'MMColParam5' and ISNULL(a.firmaddressid,'')LIKE
'MMColParam5' AND ISNULL(d.UserID,'') LIKE 'MMColParam6' AND MainCase IS
NOT NULL
ORDER BY a.caseId
---
a = Cases (This is the main table)
b = Contacts (This is the table I am joining to using LEFT join)
Even though I do it like this and I give value = 0 when it is NULL I still
get no matches, and there is at least one results where there is one record
for 'cases' where cases.alienid is NULL, still won't show up.
What am I missing ?
Let me know if you need more info to help, thanks !
AleksOn Fri, 11 Feb 2005 16:00:50 -0500, Aleks wrote:
>I am doing a LEFT JOIN, that should display all files in table a even if
>there is no match with table B as far as I know.
>The problem is that if the field that joins A with B is NULL then I get no
>results, I am trying the following:
>--
>SELECT a.Id , a.CaseId , a.EmpId, a.AlienId, a.FirmAddressId, b.FirstNm ,
>b.MiddleNm, b.LastNm , b.FirmId, c.Processcatalog , a.MainCase ,
>d.MaidenNm
>FROM Cases a LEFT JOIN Users as d ON a.EmpId = d.UserId
>LEFT JOIN Users as b ON ISNULL (a.AlienId,'0') = b.UserId
>INNER JOIN Processcatalog as c ON Process = ProcesscatalogID WHERE
>ISNULL (a.CaseID,'') LIKE '%MMColParam%' AND ISNULL (b.LastNm,'') LIKE
>'MMColParam1%' AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%' AND
>b.Firmid = 'MMColParam3' AND a.archived LIKE 'MMColParam4' and
>a.firmaddressid LIKE 'MMColParam5' and ISNULL(a.firmaddressid,'')LIKE
>'MMColParam5' AND ISNULL(d.UserID,'') LIKE 'MMColParam6' AND MainCase IS
>NOT NULL
>ORDER BY a.caseId
>---
>a = Cases (This is the main table)
>b = Contacts (This is the table I am joining to using LEFT join)
>Even though I do it like this and I give value = 0 when it is NULL I still
>get no matches, and there is at least one results where there is one record
>for 'cases' where cases.alienid is NULL, still won't show up.
>What am I missing ?
Hi Aleks,
All filtering on the outer-join-ed tables should be move to the ON clause.
To explain: if a row from a (Cases) has no match in b (Users), then the
value of b.FirstNm will be NULL (becuase of the outer join). In the WHERE
clause, you have "... AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%'", so
these outer-joined rows will fail this test and be removed from the result
set.
You also don't need to use ISNULL here - NULL LIKE 'MMColParam2%' will not
evaluate to true anyway.
Finally, it's better to use = instead of LIKE when searching for one
specific value. Use LIKE only when searching for patterns.
SELECT a.Id, a.CaseId, a.EmpId, a.AlienId, a.FirmAddressId,
b.FirstNm, b.MiddleNm, b.LastNm, b.FirmId,
c.Processcatalog, a.MainCase, d.MaidenNm
FROM Cases AS a
LEFT JOIN Users AS d
ON d.UserId = a.EmpId
AND d.UserID = 'MMColParam6'
LEFT JOIN Users AS b
ON a.AlienId = b.UserId
AND b.LastNm LIKE 'MMColParam1%'
AND b.FirstNm LIKE 'MMColParam2%'
AND b.Firmid = 'MMColParam3'
INNER JOIN Processcatalog AS c
ON ?.Process = ?.ProcesscatalogID -- better qualify ALL columns
WHERE a.CaseID LIKE '%MMColParam%'
AND a.archived = 'MMColParam4'
AND a.firmaddressid = 'MMColParam5'
AND ?.MainCase IS NOT NULL -- better qualify ALL columns
ORDER BY a.caseId
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I am using LIKE because people search for names using the first letters, not
all the name, that gives them flexibility, I use ISNULL because sometimes
they leave the last name of the first name blank, which is NULL in the
database, but I still need to return that record.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg9q01dk2cr489b62eqtkbq7093gujnv9q@.
4ax.com...
> On Fri, 11 Feb 2005 16:00:50 -0500, Aleks wrote:
>
> Hi Aleks,
> All filtering on the outer-join-ed tables should be move to the ON clause.
> To explain: if a row from a (Cases) has no match in b (Users), then the
> value of b.FirstNm will be NULL (becuase of the outer join). In the WHERE
> clause, you have "... AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%'", so
> these outer-joined rows will fail this test and be removed from the result
> set.
> You also don't need to use ISNULL here - NULL LIKE 'MMColParam2%' will not
> evaluate to true anyway.
> Finally, it's better to use = instead of LIKE when searching for one
> specific value. Use LIKE only when searching for patterns.
> SELECT a.Id, a.CaseId, a.EmpId, a.AlienId, a.FirmAddressId,
> b.FirstNm, b.MiddleNm, b.LastNm, b.FirmId,
> c.Processcatalog, a.MainCase, d.MaidenNm
> FROM Cases AS a
> LEFT JOIN Users AS d
> ON d.UserId = a.EmpId
> AND d.UserID = 'MMColParam6'
> LEFT JOIN Users AS b
> ON a.AlienId = b.UserId
> AND b.LastNm LIKE 'MMColParam1%'
> AND b.FirstNm LIKE 'MMColParam2%'
> AND b.Firmid = 'MMColParam3'
> INNER JOIN Processcatalog AS c
> ON ?.Process = ?.ProcesscatalogID -- better qualify ALL columns
> WHERE a.CaseID LIKE '%MMColParam%'
> AND a.archived = 'MMColParam4'
> AND a.firmaddressid = 'MMColParam5'
> AND ?.MainCase IS NOT NULL -- better qualify ALL columns
> ORDER BY a.caseId
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Feb 2005 16:51:19 -0500, Aleks wrote:
>I am using LIKE because people search for names using the first letters, no
t
>all the name, that gives them flexibility,
Hi Aleks,
Your code didn't allow for user input. It was just
somecol LIKE 'MMColParam2%' (where LIKE is fine) or
somecol LIKE 'MMColParam4' (where no wildcard is used, so LIKE has no
added value; use = to get better performance)
> I use ISNULL because sometimes
>they leave the last name of the first name blank, which is NULL in the
>database, but I still need to return that record.
But if you use ISNULL (a.AlienId,'0') = b.UserId, you'll only return the
row if b.UserID happens to be equal to '0', which is not too likely to
happen. And if you use ISNULL (b.FirstNm,'') LIKE 'MMColParam2%', you'll
only return the row when '' is LIKE 'MMColParam2%', which will definitely
never be the case, so the row won't be returned.
Q1: Did you try the query I suggested? Did it work?
Q2: Since I now have a feeling that your real problem is quite different
from the simplification you posted, could you now post something a little
bit closer to the real issue? If possible with SQL to construct your
tables and fill them with some sample data, and with a description of the
requested output from that sample data. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)