I really wanted to be enthusiastic about SQL Server 2005. I'm not off to a
great start with that. I'm stymied by yet another wizard that is anything
but.
I tried to copy a database from a 2000 server to a 2005 server using the new
"Management Studio" - I got a cryptic message about "Integration SSIS proxy"
accounts not existing and stuff about subsystems, so I looked around
newsgroups, and realized that I had not installed "Integration Services",
needed to make this happen. So I did.
After installing this component, I went into the proxies, to SSIS Package
Execution, got told I had to make a credential, which I went and did... then
it bombed out on me with a "specified @.subsystem_id (11) does not exist"
message. When I tried again, it asserted that the proxy exists... but I
could see no evidence of this new proxy name, nor does it show up in this
newfangled copy wizard afterwords. I tried all this again with a new proxy
name, this time clicking "script" instead of the "OK" button. When I run the
script I still get the message about @.subsystem_id.
This is a real step down from the old DTS wizard... mainly in that I could
get that to work without all these confusing runarounds. Is there anyone who
can help me to get where I need to go with this? I just want to copy some
databases... is there a simple way? Can I, if nothing else, at least copy
and attach? At least that I can understand...
I've never been a fan of the CDW in 2000 or 2005. Much simpler to
backup/restore or detach/attach. Note that this will leave your database in
80 compatability mode so you'll need to run
EXEC sp_dbcmptlevel 'database name', '90'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Richard K Bethell" <softdev@.spammingisevil.bad> wrote in message
news:OP%23TK1G7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>I really wanted to be enthusiastic about SQL Server 2005. I'm not off to a
>great start with that. I'm stymied by yet another wizard that is anything
>but.
> I tried to copy a database from a 2000 server to a 2005 server using the
> new "Management Studio" - I got a cryptic message about "Integration SSIS
> proxy" accounts not existing and stuff about subsystems, so I looked
> around newsgroups, and realized that I had not installed "Integration
> Services", needed to make this happen. So I did.
> After installing this component, I went into the proxies, to SSIS Package
> Execution, got told I had to make a credential, which I went and did...
> then it bombed out on me with a "specified @.subsystem_id (11) does not
> exist" message. When I tried again, it asserted that the proxy exists...
> but I could see no evidence of this new proxy name, nor does it show up in
> this newfangled copy wizard afterwords. I tried all this again with a new
> proxy name, this time clicking "script" instead of the "OK" button. When I
> run the script I still get the message about @.subsystem_id.
> This is a real step down from the old DTS wizard... mainly in that I could
> get that to work without all these confusing runarounds. Is there anyone
> who can help me to get where I need to go with this? I just want to copy
> some databases... is there a simple way? Can I, if nothing else, at least
> copy and attach? At least that I can understand...
>
|||It appears to have gotten worse in 2005, even. :-) Thanks for the
compatibility tip! I probably would have forgotten about that...
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OeCleBH7FHA.1140@.tk2msftngp13.phx.gbl...
> I've never been a fan of the CDW in 2000 or 2005. Much simpler to
> backup/restore or detach/attach. Note that this will leave your database
> in 80 compatability mode so you'll need to run
> EXEC sp_dbcmptlevel 'database name', '90'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Richard K Bethell" <softdev@.spammingisevil.bad> wrote in message
> news:OP%23TK1G7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>
|||I should also have said a couple more things
1) Update all your statistics
2) Run DBCC UPDATEUSAGE (0)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Richard K Bethell" <softdev@.spammingisevil.bad> wrote in message
news:OJMDWSH7FHA.2484@.TK2MSFTNGP10.phx.gbl...
> It appears to have gotten worse in 2005, even. :-) Thanks for the
> compatibility tip! I probably would have forgotten about that...
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OeCleBH7FHA.1140@.tk2msftngp13.phx.gbl...
>
Showing posts with label confusing. Show all posts
Showing posts with label confusing. Show all posts
Wednesday, 7 March 2012
Confusing Results
I am working with some data in the form of dates formatted as mm/dd/yy. Ove
r
the years, people have made mistekes in entering dates using formats like
mmdd. I am using a CASE statement to sort through these and apply the
correct formatting to them before I parse them to smalldatetime. I have the
following code:
SELECT
CASE
WHEN LEN([DateOut]) = 8 THEN
CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
WHEN [DateOut] <> '' THEN
'-- ' + [DateOut] + ' --'
ELSE
'** Empty **'
END AS [DateOut2]
FROM [TOFC].[dbo].[temp_hold] hold
By checking the length of the string, I can make sure that the date is in
the right format, and is faster than using LIKE '__/__/__'. There have been
a
few cases when the date was eight characters long but in the incorrect
format, but those have been fixed. Here is some sample data:
[DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
When I run the query, I get 'Syntax error converting character string to
smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
WHERE clause, it queries with no errors. I have simmilar code blocks in
other parts of the SELECT that do the same thig, but to different fields, an
d
have no problems. I just can't figure out why the CASE block isn't catching
the wrong length strings. Does anyone have any idea why this particular
block is not working? Where is one of the CASE blocks that is working, just
for reference:
CASE
WHEN LEN([SortDate]) = 4 THEN
CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
smalldatetime)
WHEN [SortDate] <> '' THEN
'-- ' + [SortDate] + ' --'
END AS [SortDate]
Thanks in advance.
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsChris Lieb wrote:
> I am working with some data in the form of dates formatted as
> mm/dd/yy. Over the years, people have made mistekes in entering
> dates using formats like mmdd. I am using a CASE statement to sort
> through these and apply the correct formatting to them before I parse
> them to smalldatetime. I have the following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS
> smalldatetime) WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date
> is in the right format, and is faster than using LIKE '__/__/__'.
> There have been a few cases when the date was eight characters long
> but in the incorrect format, but those have been fixed. Here is some
> sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string
> to smalldatetime data type.' However, when I put LEN([DateOut]) = 8
> in the WHERE clause, it queries with no errors. I have simmilar code
> blocks in other parts of the SELECT that do the same thig, but to
> different fields, and have no problems. I just can't figure out why
> the CASE block isn't catching the wrong length strings. Does anyone
> have any idea why this particular block is not working? Where is one
> of the CASE blocks that is working, just for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
For a date-only conversion you should use YYYYMMDD as the format as it's
the only format that is portable.
It sounds like you might have a transposed month and day in one of the
dates. Why not output to the screen each date as its about to be
converted. That will show you the date with the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||To begin, there is a function called IsDate() that returns 1 if the
expression can be converted into a type of datetime. If the users have been
able to enter whatever they wish free form, then you need to LTrim() and
RTrim() spaces when checking the length. For example, len("0522 ") = 8,
but LTrim(RTrim("0522 ")) = 4. Also, you may need to take NULL values
into consideration.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:7A367A9D-9003-4ABF-B8F7-0C60F8150762@.microsoft.com...
> I am working with some data in the form of dates formatted as mm/dd/yy.
Over
> the years, people have made mistekes in entering dates using formats like
> mmdd. I am using a CASE statement to sort through these and apply the
> correct formatting to them before I parse them to smalldatetime. I have
the
> following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date is in
> the right format, and is faster than using LIKE '__/__/__'. There have
been a
> few cases when the date was eight characters long but in the incorrect
> format, but those have been fixed. Here is some sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string to
> smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
> WHERE clause, it queries with no errors. I have simmilar code blocks in
> other parts of the SELECT that do the same thig, but to different fields,
and
> have no problems. I just can't figure out why the CASE block isn't
catching
> the wrong length strings. Does anyone have any idea why this particular
> block is not working? Where is one of the CASE blocks that is working,
just
> for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Chris,
The result type of a case expression will be the highest precedence. Because
you are casting one expression to smalldatetime, then sql server will try to
cast:
> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
"'-- ' + [DateOut] + ' --'" and "'** Empty **'" to smalldatime, and this
will give an error. If you use varchar for all result_expressions, then you
will not get any error.
AMB
"Chris Lieb" wrote:
> I am working with some data in the form of dates formatted as mm/dd/yy. O
ver
> the years, people have made mistekes in entering dates using formats like
> mmdd. I am using a CASE statement to sort through these and apply the
> correct formatting to them before I parse them to smalldatetime. I have t
he
> following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date is in
> the right format, and is faster than using LIKE '__/__/__'. There have bee
n a
> few cases when the date was eight characters long but in the incorrect
> format, but those have been fixed. Here is some sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string to
> smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
> WHERE clause, it queries with no errors. I have simmilar code blocks in
> other parts of the SELECT that do the same thig, but to different fields,
and
> have no problems. I just can't figure out why the CASE block isn't catchi
ng
> the wrong length strings. Does anyone have any idea why this particular
> block is not working? Where is one of the CASE blocks that is working, ju
st
> for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Commenting out the second WHEN clause and the ELSE clause got rid of the
error. If the CASE black uses type precidence to decide which data type to
be, why does a virtually identical CASE block that operates on another field
operate with no problems?
Chris
"Alejandro Mesa" wrote:
> Chris,
> The result type of a case expression will be the highest precedence. Becau
se
> you are casting one expression to smalldatetime, then sql server will try
to
> cast:
>
> "'-- ' + [DateOut] + ' --'" and "'** Empty **'" to smalldatime, and this
> will give an error. If you use varchar for all result_expressions, then yo
u
> will not get any error.
>
> AMB
>
> "Chris Lieb" wrote:
>|||I decided to place the functionality in a function since I call it for seven
fields. Here is the code:
CREATE FUNCTION [dbo].[STRtoDATE] (@.str nvarchar(20))
RETURNS sql_variant AS
BEGIN
DECLARE @.tmp sql_variant
IF @.str LIKE '__/__/__'
SET @.tmp = LEFT(@.str, 6) + '20' + RIGHT(@.str, 2)
ELSE IF @.str LIKE '____'
SET @.tmp = LEFT(@.str, 2) + '/' + RIGHT(@.str, 2) + '/' + '2005'
ELSE IF @.str <> ''
SET @.tmp = '-- ' + @.str + ' --'
ELSE
SET @.tmp = '** EMPTY **'
IF ISDATE(CAST (@.tmp AS nvarchar)) = 1
SET @.tmp = CAST(@.tmp AS smalldatetime)
RETURN @.tmp
END
It porbably could be done better since I resorted to using sql_variant as
the return type. I want to add a time parameter, but make it optional since
only one field needs this functionality. If it is unused, it can default to
00:00:00. How do I go about declaring an optional parameter to a function i
n
T-SQL?
Chris|||The same way as you do with a sp. You have to pass a value or use keyword
'default', you can not omit the parameter.
Example:
use northwind
go
create function dbo.ufn_f1 (
@.d datetime = '17530101'
)
returns datetime
as
begin
return (@.d)
end
go
select dbo.ufn_f1(default)
go
drop function dbo.ufn_f1
go
AMB
"Chris Lieb" wrote:
> I decided to place the functionality in a function since I call it for sev
en
> fields. Here is the code:
> CREATE FUNCTION [dbo].[STRtoDATE] (@.str nvarchar(20))
> RETURNS sql_variant AS
> BEGIN
> DECLARE @.tmp sql_variant
> IF @.str LIKE '__/__/__'
> SET @.tmp = LEFT(@.str, 6) + '20' + RIGHT(@.str, 2)
> ELSE IF @.str LIKE '____'
> SET @.tmp = LEFT(@.str, 2) + '/' + RIGHT(@.str, 2) + '/' + '2005'
> ELSE IF @.str <> ''
> SET @.tmp = '-- ' + @.str + ' --'
> ELSE
> SET @.tmp = '** EMPTY **'
> IF ISDATE(CAST (@.tmp AS nvarchar)) = 1
> SET @.tmp = CAST(@.tmp AS smalldatetime)
> RETURN @.tmp
> END
> It porbably could be done better since I resorted to using sql_variant as
> the return type. I want to add a time parameter, but make it optional sin
ce
> only one field needs this functionality. If it is unused, it can default
to
> 00:00:00. How do I go about declaring an optional parameter to a function
in
> T-SQL?
> Chris|||Thanks. Here is my final code:
CREATE FUNCTION [dbo].[STRtoDATE] (@.date nvarchar(20), @.time nvarchar(20) =
'x', @.year integer = 0)
RETURNS sql_variant AS
BEGIN
DECLARE @.tmpdate nvarchar(20)
DECLARE @.tmptime nvarchar(20)
DECLARE @.tmp sql_variant
IF @.date LIKE '__/__/__' BEGIN
IF RIGHT(@.date, 2) BETWEEN 80 AND 99
SET @.tmpdate = LEFT(@.date, 6) + '19' + RIGHT(@.date, 2)
ELSE
SET @.tmpdate = LEFT(@.date, 6) + '20' + RIGHT(@.date, 2)
END
ELSE IF @.date LIKE '____' BEGIN
IF @.year <> 0
SET @.tmpdate = LEFT(@.date, 2) + '/' + RIGHT(@.date, 2) + '/' + CAST(@.year
AS nvarchar)
ELSE
SET @.tmpdate = LEFT(@.date, 2) + '/' + RIGHT(@.date, 2) + '/' + '2005'
END
ELSE IF @.date LIKE '______' BEGIN
IF RIGHT(@.date, 2) BETWEEN 80 AND 99
SET @.tmpdate = LEFT(@.date, 2) + '/' + SUBSTRING(@.date, 3, 2) + '/19' +
RIGHT (@.date, 2)
ELSE
SET @.tmpdate = LEFT(@.date, 2) + '/' + SUBSTRING(@.date, 3, 2) + '/20' +
RIGHT (@.date, 2)
END
ELSE IF @.date <> ''
SET @.tmpdate = '-- ' + @.date + ' --'
ELSE
SET @.tmpdate = '** EMPTY **'
IF @.time LIKE '__:__'
SET @.tmptime = @.time
ELSE IF @.time <> ''
SET @.tmptime = '-- ' + @.time + ' --'
ELSE
SET @.tmptime = '** EMPTY **'
IF @.time = 'x'
SET @.tmp = @.tmpdate
ELSE
SET @.tmp = @.tmpdate + ' ' + @.tmptime
IF CAST(@.tmp AS nvarchar) LIKE '__/__/____ __:__' OR CAST(@.tmp AS nvarchar)
LIKE '__/__/____'
SET @.tmp = CAST(@.tmp AS smalldatetime)
ELSE IF @.tmp = '** EMPTY **' OR @.tmp = '** EMPTY ** ** EMPTY **'
SET @.tmp = ''
RETURN @.tmp
END
Again, not the prettiest, but it gets the job done, and in an acceptable tim
e.
Thanks everyone.
Chris
r
the years, people have made mistekes in entering dates using formats like
mmdd. I am using a CASE statement to sort through these and apply the
correct formatting to them before I parse them to smalldatetime. I have the
following code:
SELECT
CASE
WHEN LEN([DateOut]) = 8 THEN
CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
WHEN [DateOut] <> '' THEN
'-- ' + [DateOut] + ' --'
ELSE
'** Empty **'
END AS [DateOut2]
FROM [TOFC].[dbo].[temp_hold] hold
By checking the length of the string, I can make sure that the date is in
the right format, and is faster than using LIKE '__/__/__'. There have been
a
few cases when the date was eight characters long but in the incorrect
format, but those have been fixed. Here is some sample data:
[DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
When I run the query, I get 'Syntax error converting character string to
smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
WHERE clause, it queries with no errors. I have simmilar code blocks in
other parts of the SELECT that do the same thig, but to different fields, an
d
have no problems. I just can't figure out why the CASE block isn't catching
the wrong length strings. Does anyone have any idea why this particular
block is not working? Where is one of the CASE blocks that is working, just
for reference:
CASE
WHEN LEN([SortDate]) = 4 THEN
CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
smalldatetime)
WHEN [SortDate] <> '' THEN
'-- ' + [SortDate] + ' --'
END AS [SortDate]
Thanks in advance.
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsChris Lieb wrote:
> I am working with some data in the form of dates formatted as
> mm/dd/yy. Over the years, people have made mistekes in entering
> dates using formats like mmdd. I am using a CASE statement to sort
> through these and apply the correct formatting to them before I parse
> them to smalldatetime. I have the following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS
> smalldatetime) WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date
> is in the right format, and is faster than using LIKE '__/__/__'.
> There have been a few cases when the date was eight characters long
> but in the incorrect format, but those have been fixed. Here is some
> sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string
> to smalldatetime data type.' However, when I put LEN([DateOut]) = 8
> in the WHERE clause, it queries with no errors. I have simmilar code
> blocks in other parts of the SELECT that do the same thig, but to
> different fields, and have no problems. I just can't figure out why
> the CASE block isn't catching the wrong length strings. Does anyone
> have any idea why this particular block is not working? Where is one
> of the CASE blocks that is working, just for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
For a date-only conversion you should use YYYYMMDD as the format as it's
the only format that is portable.
It sounds like you might have a transposed month and day in one of the
dates. Why not output to the screen each date as its about to be
converted. That will show you the date with the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||To begin, there is a function called IsDate() that returns 1 if the
expression can be converted into a type of datetime. If the users have been
able to enter whatever they wish free form, then you need to LTrim() and
RTrim() spaces when checking the length. For example, len("0522 ") = 8,
but LTrim(RTrim("0522 ")) = 4. Also, you may need to take NULL values
into consideration.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:7A367A9D-9003-4ABF-B8F7-0C60F8150762@.microsoft.com...
> I am working with some data in the form of dates formatted as mm/dd/yy.
Over
> the years, people have made mistekes in entering dates using formats like
> mmdd. I am using a CASE statement to sort through these and apply the
> correct formatting to them before I parse them to smalldatetime. I have
the
> following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date is in
> the right format, and is faster than using LIKE '__/__/__'. There have
been a
> few cases when the date was eight characters long but in the incorrect
> format, but those have been fixed. Here is some sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string to
> smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
> WHERE clause, it queries with no errors. I have simmilar code blocks in
> other parts of the SELECT that do the same thig, but to different fields,
and
> have no problems. I just can't figure out why the CASE block isn't
catching
> the wrong length strings. Does anyone have any idea why this particular
> block is not working? Where is one of the CASE blocks that is working,
just
> for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Chris,
The result type of a case expression will be the highest precedence. Because
you are casting one expression to smalldatetime, then sql server will try to
cast:
> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
"'-- ' + [DateOut] + ' --'" and "'** Empty **'" to smalldatime, and this
will give an error. If you use varchar for all result_expressions, then you
will not get any error.
AMB
"Chris Lieb" wrote:
> I am working with some data in the form of dates formatted as mm/dd/yy. O
ver
> the years, people have made mistekes in entering dates using formats like
> mmdd. I am using a CASE statement to sort through these and apply the
> correct formatting to them before I parse them to smalldatetime. I have t
he
> following code:
> SELECT
> CASE
> WHEN LEN([DateOut]) = 8 THEN
> CAST((LEFT([DateOut], 6) + '20' + RIGHT([DateOut], 2)) AS smalldatetime)
> WHEN [DateOut] <> '' THEN
> '-- ' + [DateOut] + ' --'
> ELSE
> '** Empty **'
> END AS [DateOut2]
> FROM [TOFC].[dbo].[temp_hold] hold
> By checking the length of the string, I can make sure that the date is in
> the right format, and is faster than using LIKE '__/__/__'. There have bee
n a
> few cases when the date was eight characters long but in the incorrect
> format, but those have been fixed. Here is some sample data:
> [DateOut]: '02/28/05', '5/21/05', '8/23/05', '0521', '050 8AM'
> When I run the query, I get 'Syntax error converting character string to
> smalldatetime data type.' However, when I put LEN([DateOut]) = 8 in the
> WHERE clause, it queries with no errors. I have simmilar code blocks in
> other parts of the SELECT that do the same thig, but to different fields,
and
> have no problems. I just can't figure out why the CASE block isn't catchi
ng
> the wrong length strings. Does anyone have any idea why this particular
> block is not working? Where is one of the CASE blocks that is working, ju
st
> for reference:
> CASE
> WHEN LEN([SortDate]) = 4 THEN
> CAST((LEFT([SortDate], 2) + '/' + RIGHT([SortDate], 2) + '/2005') AS
> smalldatetime)
> WHEN [SortDate] <> '' THEN
> '-- ' + [SortDate] + ' --'
> END AS [SortDate]
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Commenting out the second WHEN clause and the ELSE clause got rid of the
error. If the CASE black uses type precidence to decide which data type to
be, why does a virtually identical CASE block that operates on another field
operate with no problems?
Chris
"Alejandro Mesa" wrote:
> Chris,
> The result type of a case expression will be the highest precedence. Becau
se
> you are casting one expression to smalldatetime, then sql server will try
to
> cast:
>
> "'-- ' + [DateOut] + ' --'" and "'** Empty **'" to smalldatime, and this
> will give an error. If you use varchar for all result_expressions, then yo
u
> will not get any error.
>
> AMB
>
> "Chris Lieb" wrote:
>|||I decided to place the functionality in a function since I call it for seven
fields. Here is the code:
CREATE FUNCTION [dbo].[STRtoDATE] (@.str nvarchar(20))
RETURNS sql_variant AS
BEGIN
DECLARE @.tmp sql_variant
IF @.str LIKE '__/__/__'
SET @.tmp = LEFT(@.str, 6) + '20' + RIGHT(@.str, 2)
ELSE IF @.str LIKE '____'
SET @.tmp = LEFT(@.str, 2) + '/' + RIGHT(@.str, 2) + '/' + '2005'
ELSE IF @.str <> ''
SET @.tmp = '-- ' + @.str + ' --'
ELSE
SET @.tmp = '** EMPTY **'
IF ISDATE(CAST (@.tmp AS nvarchar)) = 1
SET @.tmp = CAST(@.tmp AS smalldatetime)
RETURN @.tmp
END
It porbably could be done better since I resorted to using sql_variant as
the return type. I want to add a time parameter, but make it optional since
only one field needs this functionality. If it is unused, it can default to
00:00:00. How do I go about declaring an optional parameter to a function i
n
T-SQL?
Chris|||The same way as you do with a sp. You have to pass a value or use keyword
'default', you can not omit the parameter.
Example:
use northwind
go
create function dbo.ufn_f1 (
@.d datetime = '17530101'
)
returns datetime
as
begin
return (@.d)
end
go
select dbo.ufn_f1(default)
go
drop function dbo.ufn_f1
go
AMB
"Chris Lieb" wrote:
> I decided to place the functionality in a function since I call it for sev
en
> fields. Here is the code:
> CREATE FUNCTION [dbo].[STRtoDATE] (@.str nvarchar(20))
> RETURNS sql_variant AS
> BEGIN
> DECLARE @.tmp sql_variant
> IF @.str LIKE '__/__/__'
> SET @.tmp = LEFT(@.str, 6) + '20' + RIGHT(@.str, 2)
> ELSE IF @.str LIKE '____'
> SET @.tmp = LEFT(@.str, 2) + '/' + RIGHT(@.str, 2) + '/' + '2005'
> ELSE IF @.str <> ''
> SET @.tmp = '-- ' + @.str + ' --'
> ELSE
> SET @.tmp = '** EMPTY **'
> IF ISDATE(CAST (@.tmp AS nvarchar)) = 1
> SET @.tmp = CAST(@.tmp AS smalldatetime)
> RETURN @.tmp
> END
> It porbably could be done better since I resorted to using sql_variant as
> the return type. I want to add a time parameter, but make it optional sin
ce
> only one field needs this functionality. If it is unused, it can default
to
> 00:00:00. How do I go about declaring an optional parameter to a function
in
> T-SQL?
> Chris|||Thanks. Here is my final code:
CREATE FUNCTION [dbo].[STRtoDATE] (@.date nvarchar(20), @.time nvarchar(20) =
'x', @.year integer = 0)
RETURNS sql_variant AS
BEGIN
DECLARE @.tmpdate nvarchar(20)
DECLARE @.tmptime nvarchar(20)
DECLARE @.tmp sql_variant
IF @.date LIKE '__/__/__' BEGIN
IF RIGHT(@.date, 2) BETWEEN 80 AND 99
SET @.tmpdate = LEFT(@.date, 6) + '19' + RIGHT(@.date, 2)
ELSE
SET @.tmpdate = LEFT(@.date, 6) + '20' + RIGHT(@.date, 2)
END
ELSE IF @.date LIKE '____' BEGIN
IF @.year <> 0
SET @.tmpdate = LEFT(@.date, 2) + '/' + RIGHT(@.date, 2) + '/' + CAST(@.year
AS nvarchar)
ELSE
SET @.tmpdate = LEFT(@.date, 2) + '/' + RIGHT(@.date, 2) + '/' + '2005'
END
ELSE IF @.date LIKE '______' BEGIN
IF RIGHT(@.date, 2) BETWEEN 80 AND 99
SET @.tmpdate = LEFT(@.date, 2) + '/' + SUBSTRING(@.date, 3, 2) + '/19' +
RIGHT (@.date, 2)
ELSE
SET @.tmpdate = LEFT(@.date, 2) + '/' + SUBSTRING(@.date, 3, 2) + '/20' +
RIGHT (@.date, 2)
END
ELSE IF @.date <> ''
SET @.tmpdate = '-- ' + @.date + ' --'
ELSE
SET @.tmpdate = '** EMPTY **'
IF @.time LIKE '__:__'
SET @.tmptime = @.time
ELSE IF @.time <> ''
SET @.tmptime = '-- ' + @.time + ' --'
ELSE
SET @.tmptime = '** EMPTY **'
IF @.time = 'x'
SET @.tmp = @.tmpdate
ELSE
SET @.tmp = @.tmpdate + ' ' + @.tmptime
IF CAST(@.tmp AS nvarchar) LIKE '__/__/____ __:__' OR CAST(@.tmp AS nvarchar)
LIKE '__/__/____'
SET @.tmp = CAST(@.tmp AS smalldatetime)
ELSE IF @.tmp = '** EMPTY **' OR @.tmp = '** EMPTY ** ** EMPTY **'
SET @.tmp = ''
RETURN @.tmp
END
Again, not the prettiest, but it gets the job done, and in an acceptable tim
e.
Thanks everyone.
Chris
Confusing question or should I address it some where else?
I posted a question here on 12/6/05 about importing data from .csv file and
no one addressed it.
Here is the link
http://www.microsoft.com/technet/co...ogramming&fltr=
And the title is
Subject: import data properly from csv file.
Should I post it some where else or the question was not clear?
Please let me know if there is a better design for this.
I would really appreciate it.
ThanksActually, this is the direct link to your original quiestion:
http://msdn.microsoft.com/newsgroup...86f3&sloc=en-us
I don't really see any need for a global temporary table here. Why can't you
simply query the file directly? You're building stairs in the football field
.
If for some reason you do need a temporary table, create a local one (#name
instead of ##name). Of course local temporary tables are scope-specific, so
you'd have to create it in a top level procedure that starts the whole
process (creates the table, calls a sub procedure to import data, then does
the rest of transformations).
ML
http://milambda.blogspot.com/|||Assuming you know the structure of the resulting table, which would
be likely if you have other code to process it, how about
create table #temptbl (
thisCol thisType,
thatCol thatType
)
insert into #temptbl exec (
'SELECT * into ##temptbl FROM '+
@.linked_server + '...['+@.file + '#' +
@.extension + ']')
-- process as before
drop table #temptbl
If this is all in a procedure, you don't have to explicitly drop
#temptbl, but it's not a bad idea to do so.
Steve Kass
Drew University
sqlster wrote:
>I posted a question here on 12/6/05 about importing data from .csv file and
>no one addressed it.
>Here is the link
>http://www.microsoft.com/technet/co...ogramming&fltr=
>And the title is
>Subject: import data properly from csv file.
>Should I post it some where else or the question was not clear?
>Please let me know if there is a better design for this.
>I would really appreciate it.
>Thanks
>
>|||ML and Steve,
Thank you very much...
"Steve Kass" wrote:
> Assuming you know the structure of the resulting table, which would
> be likely if you have other code to process it, how about
> create table #temptbl (
> thisCol thisType,
> thatCol thatType
> )
> insert into #temptbl exec (
> 'SELECT * into ##temptbl FROM '+
> @.linked_server + '...['+@.file + '#' +
> @.extension + ']')
> -- process as before
> drop table #temptbl
> If this is all in a procedure, you don't have to explicitly drop
> #temptbl, but it's not a bad idea to do so.
> Steve Kass
> Drew University
> sqlster wrote:
>
>
no one addressed it.
Here is the link
http://www.microsoft.com/technet/co...ogramming&fltr=
And the title is
Subject: import data properly from csv file.
Should I post it some where else or the question was not clear?
Please let me know if there is a better design for this.
I would really appreciate it.
ThanksActually, this is the direct link to your original quiestion:
http://msdn.microsoft.com/newsgroup...86f3&sloc=en-us
I don't really see any need for a global temporary table here. Why can't you
simply query the file directly? You're building stairs in the football field
.
If for some reason you do need a temporary table, create a local one (#name
instead of ##name). Of course local temporary tables are scope-specific, so
you'd have to create it in a top level procedure that starts the whole
process (creates the table, calls a sub procedure to import data, then does
the rest of transformations).
ML
http://milambda.blogspot.com/|||Assuming you know the structure of the resulting table, which would
be likely if you have other code to process it, how about
create table #temptbl (
thisCol thisType,
thatCol thatType
)
insert into #temptbl exec (
'SELECT * into ##temptbl FROM '+
@.linked_server + '...['+@.file + '#' +
@.extension + ']')
-- process as before
drop table #temptbl
If this is all in a procedure, you don't have to explicitly drop
#temptbl, but it's not a bad idea to do so.
Steve Kass
Drew University
sqlster wrote:
>I posted a question here on 12/6/05 about importing data from .csv file and
>no one addressed it.
>Here is the link
>http://www.microsoft.com/technet/co...ogramming&fltr=
>And the title is
>Subject: import data properly from csv file.
>Should I post it some where else or the question was not clear?
>Please let me know if there is a better design for this.
>I would really appreciate it.
>Thanks
>
>|||ML and Steve,
Thank you very much...
"Steve Kass" wrote:
> Assuming you know the structure of the resulting table, which would
> be likely if you have other code to process it, how about
> create table #temptbl (
> thisCol thisType,
> thatCol thatType
> )
> insert into #temptbl exec (
> 'SELECT * into ##temptbl FROM '+
> @.linked_server + '...['+@.file + '#' +
> @.extension + ']')
> -- process as before
> drop table #temptbl
> If this is all in a procedure, you don't have to explicitly drop
> #temptbl, but it's not a bad idea to do so.
> Steve Kass
> Drew University
> sqlster wrote:
>
>
Confusing layout in SSIS with regard to "Execute SQL Task".
I hope someone can help.
I'm trying to read rows from a SQL Server Table and for each row use a few columns as parameters into a query to be run against oracle which will delete oracle rows.
I add OLDEB connections for Oracle and SQL and then I try to add a "Execute SQL Task". I've also tried a "OLE Command" but I can't get the mapping of the columns to the parameters to work.
There is lots of articles on the web that talk in general around parameterized queries but no clear examples.
I also find the difference between the Control Flow and Data Flow tabs confusing as its not intuitive where to place things. It also appears to force me to re-define things that it should already know (this is no doubt because I'm interpreting what I've done / acheived wrongly).
I have my source and destination on the "Data Flow" tab along with a "Execute SQL Task" object in the middle.
I'm setting its "connection manager" the Oracle (i.e. the destination where I want the deletes to be executed). I don't follow why this also has a "connection property, surely this it set when I drag the output of the SQL Server OLEDB Source to the input of the "Execute SQL Task".
Perhaps I'm expected too much from the wizards / dialogs and I have to create "variables" and "parameters" myself?
Any help or suggestions would be very much appreciated.
Thanks in advance
Craig
Scotland
Craig,
In general, you can think in the control flow as the one responsible for the workflow of the package; it could be also use to perform batch operations against a table. In you case, for example you could issue a delete statement using an execute sql task against the Oracle table to delete all rows at once. In order to do so, you would need to have a staging table that holds the rows that need to be delete.
The DataFlow; is deemed to move data and perform operations in a row-by-row basis. You could solve your problem by using an OLE DB command transformation to perform the delete. The drawback with this approach, it is that the delete statement would be performed for every row in the dataflow pipeline; so the performance is affected considerably.
If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction.
In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement.
I hope this helps you
|||Thanks,
Some comments.
"If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction."
One table exists in SQL Server, then other in Oracle, does you comment still apply?
"In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement."
MUST I do this. This does not appear to me to be in the nature of a parameterized query? Would this method still use "Prepared Statements" ?
Lastly, I'm still stuck in that my real issue is on how to map input data (from SQL) to parameters (to Oracle), I think if I followed your example, I'd still have that same problem only now I would be trying to map to a variable that was my entire query instead of just a parameter?
Thanks for the help so far and best regards
Craig
|||Can you create and populate a staging table in the Oracle side? If so, you could load all the data in the SQL server table into a staging table in the Oracle side and then use an execute sql task to perform a 1 time update. This is the way I do this kind of things because it's more efficient performance wise.
If that is not possible; I guess you have to stick with the data flow/OLE DB command approach. But I cannot help with that as I don't have an Oracle instance to test how the parameters get mapped.
Confusing data error
I have a gridview that is very basic and list name, address, ......, and a URL.
If I have this url in the database I have no problemhttp://www.lubbockisd.org/earlychildhood/
yet when I place this URLhttp://www.lubbockisd.org/erlychildhood/stubbs.htm
It triggers this error on my localhost server and my production server.
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
I've made changes to datatypes with no success, made sure my length of data type was set long enough with no success.
Which datatype is best for a URL in sql express?
Any ideas why this happens with such a minor change?
Thanks
Tony
You may have already done all this but I just want to make sure that that URL is not already present in the database. Other than that, what are the constraints on your fields. Does the URL field accepts only unique values.
bullpit
|||Thanks for replying so quickly,
That is what is confusing me so much, I don't have any constraints on this field, nor is it a unique or identifier field.
Tony
|||Hi Tony,
Is there any other table that reference this field in the DataSet?
|||Nope, this is the only page that uses this particular table...http://www.tonyhager.com/schools/prek.aspx
TH
|||Hi,
Please check each column that you have constraints on this table. If it has a unique constraint, make sure it is not violated with a duplicated value.
A VarChar data type will be suitable to store URLs.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
|||As stated previously, I have no constraints in this table. You can see that I have the URL duplicated on the page in question in the table with all three of the PreK schools listed. I have 5 pages that use this data table and none of the other pages trigger the error even when I have the URL that does trigger the error on the PreK page the other pages work fine. It only happens when I add the page name to the one URL. The default page ishttp://www.tonyhager.com/schools/
Thanks again,
TH
|||Hi,
To troubleshoot on this issue, if you're using a DataSet to store the values, I suggest you use WriteXml("filename.xml", XmlWriteMode.WriteSchema) to write all the data and schema out to an xml file.
Analysis on this file will reveal where the constraint error is. If you're having problem, you can paste it here together with the new data you're inserting to this DataSet, and I will try to check this for you.
|||try checking your XML schema designer page and see if any of your fields have theNullValue property set to "(Throw exception)". If so, then try changing it to null.|||Would I even have a XML schema page if I am using a sql express database? Where do I look for this page?
|||the XSD schema is generated if you "drag and drop" your tables. It creates strongly typed objects through which you can manage your scheme via the designer's interface.
if a schema has been generated it is normally generated under your "App_Code" folder and the name ends in "XSD". If you don't have a schema then you may want to consider this approach for creating strongly typed datasets in the future. It is much quicker and less error prone.|||Every field had it set to "throw exception" is that a default setting? So I should change each string field to read null?|||
only change the "offending" fields to null (unless you don't mind your other fields accepting null values as well).
the dataset schema acts as a second 'safety mechanism' to catch data-type mismatches, differences in column names, null values etc. It is a good thing but it can cause confusion sometimes because it adds an extra layer of complexity
Confusing Cross-Database Permissions Issue
We're trying to follow the principle of least privilege here in setting up a user account for our website to use to access SQL Server 2005, but we're having a nightmarish time getting it to work.
The issue seems to be trying to get a limited access user account the ability to cross databases.
Here's the situation:
We have a User [WebUser] that we want to grant access to the database. This account has a login [WebUser] that has username=WebUser and password=ALongPassword.
This user only calls stored procedures in the database [WebData].
However, some of the stored procedures in [WebData] call stored procedures in the database [dbutil].
One of the stored procedures in [dbutil] inserts records into a table in a third database [dbutil_temp].[DebugLog].
This all works out great from my development account using Windows Authentication.
But as you might guess, if I do something like "EXECUTE AS [WebUser]" and run the same procedure on [WebData] things fall apart quickly. I've looked online regarding cross-database ownership chaining, but quite frankly, the whole users/logins/roles/schemas security model is confusing, and I'm getting nowhere fast on my own.
We really only want [WebUser] to have CONNECT and EXECUTE permissions on the primary [WebData] database, but it seems like we've got to do a lot more than that to get this to work.
I'd appreciate any help...
Yes, you have to do a bit more work, but it isn't a whole lot more work. The most appropriate solution for this is to use signatures for the cross database access. There are detailed demos for creating signatures as well as specifically signatures for cross database queries in the following blogs, which I VERY highly recommend reading.
http://blogs.msdn.com/lcris/
http://blogs.msdn.com/raulga/
I would also suggest listenting to the security presentations at http://cmcgc.com/media/WMP/261115
|||Thanks Mike, I'll take a look at those resources.Confusing CONTAINS behavior
I'm experimenting with the CONTAINS operation and I've come across some
confusing or inconsistent results. The following results are exactly the
same if the search terms are enclosed in double quotes so that they are
treated as a phrase.
I'm searching a Books table for books whose Author column contains "cs".
The following CONTAINS statements in the WHERE clause result in books by
"Lewis, C.S." being returned:
CONTAINS( Author, 'cs' )
CONTAINS( Author, 'CS' )
CONTAINS( Author, 'C.S.' )
The fact that "cs" matches an author containing "C.S." indicates that both
case and punctuation are ignored. However, the following CONTAINS statement
does not return any results for "Lewis, C.S.":
CONTAINS( Author, 'c.s.' )
This puzzles me. It seems as though either punctuation is ignored or case
is ignored, but not both. What kind of behavior is that? Is this by design
?Greg Smalter wrote:
> I'm experimenting with the CONTAINS operation and I've come across
> some confusing or inconsistent results. The following results are
> exactly the same if the search terms are enclosed in double quotes so
> that they are treated as a phrase.
> I'm searching a Books table for books whose Author column contains
> "cs".
> The following CONTAINS statements in the WHERE clause result in books
> by "Lewis, C.S." being returned:
> CONTAINS( Author, 'cs' )
> CONTAINS( Author, 'CS' )
> CONTAINS( Author, 'C.S.' )
> The fact that "cs" matches an author containing "C.S." indicates that
> both case and punctuation are ignored. However, the following
> CONTAINS statement does not return any results for "Lewis, C.S.":
> CONTAINS( Author, 'c.s.' )
> This puzzles me. It seems as though either punctuation is ignored or
> case is ignored, but not both. What kind of behavior is that? Is
> this by design?
According to BOL, a word is one or more characters without spaces or
punctuation. Punctuation marks such as the period, colon, semicolon,
comma, and hyphen are ignored during a search.
I do not believe the full-text engine is indexing the punctuation marks
which is why the second query fails.
David Gugick
Quest Software
www.quest.com|||I agree about the specification of the word and how it shouldn't contain
punctuation. That is why I also tested it with phrases surrounded by double
quotes, where the specification doesn't mention that puncutation is not
allowed.
I imagine you are right about the indexing, but that doesn't explain why
CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.' ) does not
work, because they both have the exact same punctuation. The only differenc
e
is case.
"David Gugick" wrote:
> Greg Smalter wrote:
> According to BOL, a word is one or more characters without spaces or
> punctuation. Punctuation marks such as the period, colon, semicolon,
> comma, and hyphen are ignored during a search.
> I do not believe the full-text engine is indexing the punctuation marks
> which is why the second query fails.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>|||Greg Smalter wrote:
> I agree about the specification of the word and how it shouldn't
> contain punctuation. That is why I also tested it with phrases
> surrounded by double quotes, where the specification doesn't mention
> that puncutation is not allowed.
> I imagine you are right about the indexing, but that doesn't explain
> why CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.'
> ) does not work, because they both have the exact same punctuation.
> The only difference is case.
That part is confusing...
David Gugick
Quest Software
www.quest.com
confusing or inconsistent results. The following results are exactly the
same if the search terms are enclosed in double quotes so that they are
treated as a phrase.
I'm searching a Books table for books whose Author column contains "cs".
The following CONTAINS statements in the WHERE clause result in books by
"Lewis, C.S." being returned:
CONTAINS( Author, 'cs' )
CONTAINS( Author, 'CS' )
CONTAINS( Author, 'C.S.' )
The fact that "cs" matches an author containing "C.S." indicates that both
case and punctuation are ignored. However, the following CONTAINS statement
does not return any results for "Lewis, C.S.":
CONTAINS( Author, 'c.s.' )
This puzzles me. It seems as though either punctuation is ignored or case
is ignored, but not both. What kind of behavior is that? Is this by design
?Greg Smalter wrote:
> I'm experimenting with the CONTAINS operation and I've come across
> some confusing or inconsistent results. The following results are
> exactly the same if the search terms are enclosed in double quotes so
> that they are treated as a phrase.
> I'm searching a Books table for books whose Author column contains
> "cs".
> The following CONTAINS statements in the WHERE clause result in books
> by "Lewis, C.S." being returned:
> CONTAINS( Author, 'cs' )
> CONTAINS( Author, 'CS' )
> CONTAINS( Author, 'C.S.' )
> The fact that "cs" matches an author containing "C.S." indicates that
> both case and punctuation are ignored. However, the following
> CONTAINS statement does not return any results for "Lewis, C.S.":
> CONTAINS( Author, 'c.s.' )
> This puzzles me. It seems as though either punctuation is ignored or
> case is ignored, but not both. What kind of behavior is that? Is
> this by design?
According to BOL, a word is one or more characters without spaces or
punctuation. Punctuation marks such as the period, colon, semicolon,
comma, and hyphen are ignored during a search.
I do not believe the full-text engine is indexing the punctuation marks
which is why the second query fails.
David Gugick
Quest Software
www.quest.com|||I agree about the specification of the word and how it shouldn't contain
punctuation. That is why I also tested it with phrases surrounded by double
quotes, where the specification doesn't mention that puncutation is not
allowed.
I imagine you are right about the indexing, but that doesn't explain why
CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.' ) does not
work, because they both have the exact same punctuation. The only differenc
e
is case.
"David Gugick" wrote:
> Greg Smalter wrote:
> According to BOL, a word is one or more characters without spaces or
> punctuation. Punctuation marks such as the period, colon, semicolon,
> comma, and hyphen are ignored during a search.
> I do not believe the full-text engine is indexing the punctuation marks
> which is why the second query fails.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>|||Greg Smalter wrote:
> I agree about the specification of the word and how it shouldn't
> contain punctuation. That is why I also tested it with phrases
> surrounded by double quotes, where the specification doesn't mention
> that puncutation is not allowed.
> I imagine you are right about the indexing, but that doesn't explain
> why CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.'
> ) does not work, because they both have the exact same punctuation.
> The only difference is case.
That part is confusing...
David Gugick
Quest Software
www.quest.com
Confusing arithmetic overflow error
I have a numeric column whoose precision is 7 and scale is 3.
My first question is how come the length is reported as 5 in Enterprise
Managaer?
Secondly, whilst trying to update values in these columns I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely, any
of these values should fit nicely into my defined data type? Why is this
occuring?
Cheers,
elzikoThe length is reported as 5 because that's the number of bytes that the
datatype occupies.
Precision 7, scale 3 = XXXX.YYY
4 digits to the left of the decimal point, 3 to the right.
-16942.18 has 5 to the left, 2 to the right. That's why it's failing.
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>|||Oh it appears I counted four to the left... stupid me! Sorry & thanks!|||If you read in BOL (decimal and numeric) there is a table that specifies how
many bytes to store numeric data based on the precision.
As for your overflow this is occurring, because you ask for 3 digits to the
right of the decimal point to be stored (scale), but the total length of the
the number to only be 7 digits long (precision). Therefore the numbers you
are trying to use really have 8 digit precision (i.e -16942.18, is
really -16942.180).
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>
My first question is how come the length is reported as 5 in Enterprise
Managaer?
Secondly, whilst trying to update values in these columns I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely, any
of these values should fit nicely into my defined data type? Why is this
occuring?
Cheers,
elzikoThe length is reported as 5 because that's the number of bytes that the
datatype occupies.
Precision 7, scale 3 = XXXX.YYY
4 digits to the left of the decimal point, 3 to the right.
-16942.18 has 5 to the left, 2 to the right. That's why it's failing.
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>|||Oh it appears I counted four to the left... stupid me! Sorry & thanks!|||If you read in BOL (decimal and numeric) there is a table that specifies how
many bytes to store numeric data based on the precision.
As for your overflow this is occurring, because you ask for 3 digits to the
right of the decimal point to be stored (scale), but the total length of the
the number to only be 7 digits long (precision). Therefore the numbers you
are trying to use really have 8 digit precision (i.e -16942.18, is
really -16942.180).
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>
Confusing arithmetic overflow error
I have a numeric column whoose precision is 7 and scale is 3.
My first question is how come the length is reported as 5 in Enterprise
Managaer?
Secondly, whilst trying to update values in these columns I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely, any
of these values should fit nicely into my defined data type? Why is this
occuring?
Cheers,
elziko
The length is reported as 5 because that's the number of bytes that the
datatype occupies.
Precision 7, scale 3 = XXXX.YYY
4 digits to the left of the decimal point, 3 to the right.
-16942.18 has 5 to the left, 2 to the right. That's why it's failing.
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>
|||Oh it appears I counted four to the left... stupid me! Sorry & thanks!
|||If you read in BOL (decimal and numeric) there is a table that specifies how
many bytes to store numeric data based on the precision.
As for your overflow this is occurring, because you ask for 3 digits to the
right of the decimal point to be stored (scale), but the total length of the
the number to only be 7 digits long (precision). Therefore the numbers you
are trying to use really have 8 digit precision (i.e -16942.18, is
really -16942.180).
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>
My first question is how come the length is reported as 5 in Enterprise
Managaer?
Secondly, whilst trying to update values in these columns I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely, any
of these values should fit nicely into my defined data type? Why is this
occuring?
Cheers,
elziko
The length is reported as 5 because that's the number of bytes that the
datatype occupies.
Precision 7, scale 3 = XXXX.YYY
4 digits to the left of the decimal point, 3 to the right.
-16942.18 has 5 to the left, 2 to the right. That's why it's failing.
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>
|||Oh it appears I counted four to the left... stupid me! Sorry & thanks!
|||If you read in BOL (decimal and numeric) there is a table that specifies how
many bytes to store numeric data based on the precision.
As for your overflow this is occurring, because you ask for 3 digits to the
right of the decimal point to be stored (scale), but the total length of the
the number to only be 7 digits long (precision). Therefore the numbers you
are trying to use really have 8 digit precision (i.e -16942.18, is
really -16942.180).
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>
Confusing arithmetic overflow error
I have a numeric column whoose precision is 7 and scale is 3.
My first question is how come the length is reported as 5 in Enterprise
Managaer?
Secondly, whilst trying to update values in these columns I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely, any
of these values should fit nicely into my defined data type? Why is this
occuring?
--
Cheers,
elzikoThe length is reported as 5 because that's the number of bytes that the
datatype occupies.
Precision 7, scale 3 = XXXX.YYY
4 digits to the left of the decimal point, 3 to the right.
-16942.18 has 5 to the left, 2 to the right. That's why it's failing.
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>|||Oh it appears I counted four to the left... stupid me! Sorry & thanks!|||If you read in BOL (decimal and numeric) there is a table that specifies how
many bytes to store numeric data based on the precision.
As for your overflow this is occurring, because you ask for 3 digits to the
right of the decimal point to be stored (scale), but the total length of the
the number to only be 7 digits long (precision). Therefore the numbers you
are trying to use really have 8 digit precision (i.e -16942.18, is
really -16942.180).
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>
My first question is how come the length is reported as 5 in Enterprise
Managaer?
Secondly, whilst trying to update values in these columns I get
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely, any
of these values should fit nicely into my defined data type? Why is this
occuring?
--
Cheers,
elzikoThe length is reported as 5 because that's the number of bytes that the
datatype occupies.
Precision 7, scale 3 = XXXX.YYY
4 digits to the left of the decimal point, 3 to the right.
-16942.18 has 5 to the left, 2 to the right. That's why it's failing.
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>|||Oh it appears I counted four to the left... stupid me! Sorry & thanks!|||If you read in BOL (decimal and numeric) there is a table that specifies how
many bytes to store numeric data based on the precision.
As for your overflow this is occurring, because you ask for 3 digits to the
right of the decimal point to be stored (scale), but the total length of the
the number to only be 7 digits long (precision). Therefore the numbers you
are trying to use really have 8 digit precision (i.e -16942.18, is
really -16942.180).
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:OC9W741jEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have a numeric column whoose precision is 7 and scale is 3.
> My first question is how come the length is reported as 5 in Enterprise
> Managaer?
> Secondly, whilst trying to update values in these columns I get
> Server: Msg 8115, Level 16, State 8, Line 1
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> ...for values such as -16942.18, -16939.59, 16935.46, 16939.59. Surely,
any
> of these values should fit nicely into my defined data type? Why is this
> occuring?
> --
> Cheers,
> elziko
>
Subscribe to:
Posts (Atom)