Help.
Have the following:
SELECT
(ISNULL(CAST(RANKTBL1.RANK AS decimal),0)) +
(ISNULL(CAST(RANKTBL2.RANK AS decimal),0)/10) AS RANK,
RANKTBL1.RANK AS RANK1,
RANKTBL2.RANK AS RANK2,
items.itempkid,
items.keywords,
menu.menupkid,
menu.title,
items.content
FROM
(menu INNER JOIN items ON menu.itempkid = items.itempkid)
INNER JOIN
(FREETEXTTABLE(items,keywords,'paul drew') AS RANKTBL1 FULL OUTER
JOIN FREETEXTTABLE(items,content,'paul drew') AS RANKTBL2 ON
RANKTBL1.[KEY] = RANKTBL2.[KEY])
ON (items.itempkid = RANKTBL1.[KEY] OR items.itempkid =
RANKTBL2.[KEY])
WHERE
(ISNULL(CAST(RANKTBL1.RANK AS decimal),0)/10) +
(ISNULL(CAST(RANKTBL2.RANK AS decimal),0)/10) > 3
ORDER BY
RANK DESC
But the highest ranked record this returns from my database has
neither the word 'paul' or 'drew' occuring in any of the fields. The
closest I can find is that the top record has 'dre' near the beginning
of the 'content' field.
If I just search for 'drew' this excludes 2 records that contain the
word 'drew' and still returns the aforementioned record.
I've tried doing a full population, and rebuilding the catalog+full
population with exactly the same results returned.
Any ideas?
Andy,
Could you provide the full output of -- SELECT @.@.version -- as this would be
helpful in troubleshooting this SQL FTS issue.
Also, could you post the count(*) value for your FT-enabled table items?
Additionally, while I understand why you're doing the "math" on the returned
RANK values, but it will not get you a higher RANK value for specific words
or phrases that you expect. See SQL Server 2000 BOL title "Full-Text Search
Recommendations" for a more information. As an alternative, you may want to
try the below approach - assuming that your table as a statistically
significantly number of rows - and using the WEIGHT parameter:
SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
--> examples of using RANK and multipling RANK
use pubs
go
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK, key_tbl1.Rank
FROM authors as FT_TBL,
CONTAINSTABLE (authors,au_lname, 'Ringer' ) AS KEY_TBL,
CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
WHERE
FT_TBL.au_id = KEY_TBL.[KEY] or
FT_TBL.au_id = KEY_TBL1.[KEY] and
key_tbl1.Rank < 2* KEY_TBL.RANK
ORDER BY KEY_TBL.Rank, KEY_TBL1.RANK
Regards,
John
"Andy Clark" <andy@.workonmypc.co.uk> wrote in message
news:718c4cf7.0405050614.f103787@.posting.google.co m...
> Help.
> Have the following:
> ----
--
> SELECT
> (ISNULL(CAST(RANKTBL1.RANK AS decimal),0)) +
> (ISNULL(CAST(RANKTBL2.RANK AS decimal),0)/10) AS RANK,
> RANKTBL1.RANK AS RANK1,
> RANKTBL2.RANK AS RANK2,
> items.itempkid,
> items.keywords,
> menu.menupkid,
> menu.title,
> items.content
> FROM
> (menu INNER JOIN items ON menu.itempkid = items.itempkid)
> INNER JOIN
> (FREETEXTTABLE(items,keywords,'paul drew') AS RANKTBL1 FULL OUTER
> JOIN FREETEXTTABLE(items,content,'paul drew') AS RANKTBL2 ON
> RANKTBL1.[KEY] = RANKTBL2.[KEY])
> ON (items.itempkid = RANKTBL1.[KEY] OR items.itempkid =
> RANKTBL2.[KEY])
> WHERE
> (ISNULL(CAST(RANKTBL1.RANK AS decimal),0)/10) +
> (ISNULL(CAST(RANKTBL2.RANK AS decimal),0)/10) > 3
> ORDER BY
> RANK DESC
> ----
--
> But the highest ranked record this returns from my database has
> neither the word 'paul' or 'drew' occuring in any of the fields. The
> closest I can find is that the top record has 'dre' near the beginning
> of the 'content' field.
> If I just search for 'drew' this excludes 2 records that contain the
> word 'drew' and still returns the aforementioned record.
> I've tried doing a full population, and rebuilding the catalog+full
> population with exactly the same results returned.
> Any ideas?
|||SELECT @.@.version:
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998
22:20:07 Copyright (c) 1988-1998 Microsoft Corporation Standard
Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
COUNT(*) of items:
353
The "math" on the RANK values is to weight the importance of different
fields, i.e. keywords are more important than content and therefore
have a greater influence in the overall RANK.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#cGlT9wMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Andy,
> Could you provide the full output of -- SELECT @.@.version -- as this would be
> helpful in troubleshooting this SQL FTS issue.
> Also, could you post the count(*) value for your FT-enabled table items?
> Additionally, while I understand why you're doing the "math" on the returned
> RANK values, but it will not get you a higher RANK value for specific words
> or phrases that you expect. See SQL Server 2000 BOL title "Full-Text Search
> Recommendations" for a more information. As an alternative, you may want to
> try the below approach - assuming that your table as a statistically
> significantly number of rows - and using the WEIGHT parameter:
> SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
> B_RANK, A.[RANK] as A_RANK
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and
> B.[KEY] = e.EmployeeID
> --> examples of using RANK and multipling RANK
> use pubs
> go
> SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK, key_tbl1.Rank
> FROM authors as FT_TBL,
> CONTAINSTABLE (authors,au_lname, 'Ringer' ) AS KEY_TBL,
> CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
> WHERE
> FT_TBL.au_id = KEY_TBL.[KEY] or
> FT_TBL.au_id = KEY_TBL1.[KEY] and
> key_tbl1.Rank < 2* KEY_TBL.RANK
> ORDER BY KEY_TBL.Rank, KEY_TBL1.RANK
> Regards,
> John
>
> "Andy Clark" <andy@.workonmypc.co.uk> wrote in message
> news:718c4cf7.0405050614.f103787@.posting.google.co m...
> --
> --
|||Thanks, Andy,
Yes, I suspected that your query was to have on one column "rank" higher
than the other. However, you will never get the expected results with only
353 rows. You should re-test your query on a table with at least 50,000 to
100,000 rows in order to get to a statistically significantly number of
rows.
Regards,
John
"Andy Clark" <andy@.workonmypc.co.uk> wrote in message
news:718c4cf7.0405060223.f3a5d3b@.posting.google.co m...
> SELECT @.@.version:
> Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998
> 22:20:07 Copyright (c) 1988-1998 Microsoft Corporation Standard
> Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> COUNT(*) of items:
> 353
>
>
> The "math" on the RANK values is to weight the importance of different
> fields, i.e. keywords are more important than content and therefore
> have a greater influence in the overall RANK.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<#cGlT9wMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
would be[vbcol=seagreen]
returned[vbcol=seagreen]
words[vbcol=seagreen]
Search[vbcol=seagreen]
to[vbcol=seagreen]
> ----
> ----
|||But surely this still doesn't explain the search coming up with a high
rank result that doesn't contain any of the search terms. Would
applying SQL Server 7 SP4 help?
"John Kane" <jt-kane@.comcast.net> wrote in message news:<uQFHNq3MEHA.3052@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Thanks, Andy,
> Yes, I suspected that your query was to have on one column "rank" higher
> than the other. However, you will never get the expected results with only
> 353 rows. You should re-test your query on a table with at least 50,000 to
> 100,000 rows in order to get to a statistically significantly number of
> rows.
> Regards,
> John
>
> "Andy Clark" <andy@.workonmypc.co.uk> wrote in message
> news:718c4cf7.0405060223.f3a5d3b@.posting.google.co m...
> news:<#cGlT9wMEHA.3944@.tk2msftngp13.phx.gbl>...
> would be
> returned
> words
> Search
> to
> ----
> ----
|||Andy,
While you may want to apply SQL Server 7.0 service pack SP4 for other
reasons, this is not one of them.
You should re-test your query using a much larger table. Additionally, if
you plan to use SQL 7.0 FTS in a production environment, I'd highly
recommend that you consider upgrading to SQL Server 2000 as there are a
number of fixes, feature enhancements and performance improvements in SQL
2000 that are not in SQL 7.0
Regards,
John
"Andy Clark" <andy@.workonmypc.co.uk> wrote in message
news:718c4cf7.0405070446.678531b9@.posting.google.c om...
> But surely this still doesn't explain the search coming up with a high
> rank result that doesn't contain any of the search terms. Would
> applying SQL Server 7 SP4 help?
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<uQFHNq3MEHA.3052@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
only[vbcol=seagreen]
to[vbcol=seagreen]
items?[vbcol=seagreen]
specific[vbcol=seagreen]
want[vbcol=seagreen]
as[vbcol=seagreen]
A,
>
----
>
----[vbcol=seagreen]
The[vbcol=seagreen]
beginning[vbcol=seagreen]
the[vbcol=seagreen]
catalog+full[vbcol=seagreen]
Showing posts with label cast. Show all posts
Showing posts with label cast. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Invalid Column Name in SELECT
This select works as I expect:
SELECT exp_AcctNum,
exp_Amount,
CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
However, I am concerned about it having to call the function 3 times for
each row. Will SQL Server 2000 be smart enough to know that it is the same
call each time?
I tried using column aliases, but got errors.
For example, in Access I am use to doing something like:
SELECT Amount AS [Amt],
AMT as Amt2
FROM [tbl Expenses];
When I try to do the same thing in SQL Server,
SELECT exp_Amount AS [Amt],
AMT as Amt2
FROM Expenses
Go
it gives me a Invalid column name 'AMT'.
I had wanted to call the function once and create a column alias and then
use that alias for the CAST and SUBSTRING, but cannot get by the column
issue.
Something like
SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS 'stvalue',
CAST(Left(stvalue,2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'stvalue'.
I would really appreciate some guidance on this one!
Thanks.On Thu, 1 Jun 2006 17:40:15 -0500, MikeV06 wrote:
>This select works as I expect:
>SELECT exp_AcctNum,
> exp_Amount,
> CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
> SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
>However, I am concerned about it having to call the function 3 times for
>each row. Will SQL Server 2000 be smart enough to know that it is the same
>call each time?
Hi Mike,
Unfortunately, no.
(snip)
>I had wanted to call the function once and create a column alias and then
>use that alias for the CAST and SUBSTRING, but cannot get by the column
>issue.
>Something like
>SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS 'stvalue',
> CAST(Left(stvalue,2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
You can't do it this way. A column alias can only be used in the ORDER
BY clause, nowhere else in the query.
You can use a derived table, though:
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
FROM (SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
Hugo Kornelis, SQL Server MVP|||On Fri, 02 Jun 2006 01:00:44 +0200, Hugo Kornelis wrote:
[snip]
> You can't do it this way. A column alias can only be used in the ORDER
> BY clause, nowhere else in the query.
> You can use a derived table, though:
> SELECT exp_AcctNum,
> exp_Amount,
> stvalue,
> CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
> FROM (SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS stvalue
> FROM Expenses) AS d
Thank you very much. I was miles away from this solution and had already
spent 1 day trying several things that did not work. It needs a little
touch up; however, what I finally came up from your template works. What I
really do like is that the call to the function is only made once.
I guess I could put the derived table in a view and have this view call
that view (which ends up being called by another view to get the final
stratification result table). I am not sure I see any benefit in taking
that approach, but may try it just to see what happens. Uhm, maybe another
UDF instead of a View ... too tired to really think at this point.
You have made my day -- I think I will give it up for the day. Thanks.
Mike.
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'prestrat')
DROP VIEW prestrat
GO
CREATE VIEW prestrat
AS
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc],
posrecs,
posdols,
negrecs,
negdols,
[posrecs]+[negrecs] AS totrecs,
[posdols]+[negdols] AS totdols,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType
FROM (SELECT exp_AcctNum,
exp_Amount,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType,
CASE WHEN exp_Amount>=0 THEN 1 ELSE 0 END AS posrecs,
CASE WHEN exp_Amount>=0 THEN exp_Amount ELSE 0 END AS
posdols,
CASE WHEN exp_Amount<0 THEN 0 ELSE 1 END AS negrecs,
CASE WHEN exp_Amount<0 THEN 0 ELSE exp_Amount END AS negdols,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
WHERE Exp_RecordType = '2'
Go
SELECT exp_AcctNum,
exp_Amount,
CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
However, I am concerned about it having to call the function 3 times for
each row. Will SQL Server 2000 be smart enough to know that it is the same
call each time?
I tried using column aliases, but got errors.
For example, in Access I am use to doing something like:
SELECT Amount AS [Amt],
AMT as Amt2
FROM [tbl Expenses];
When I try to do the same thing in SQL Server,
SELECT exp_Amount AS [Amt],
AMT as Amt2
FROM Expenses
Go
it gives me a Invalid column name 'AMT'.
I had wanted to call the function once and create a column alias and then
use that alias for the CAST and SUBSTRING, but cannot get by the column
issue.
Something like
SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS 'stvalue',
CAST(Left(stvalue,2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'stvalue'.
I would really appreciate some guidance on this one!
Thanks.On Thu, 1 Jun 2006 17:40:15 -0500, MikeV06 wrote:
>This select works as I expect:
>SELECT exp_AcctNum,
> exp_Amount,
> CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
> SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
>However, I am concerned about it having to call the function 3 times for
>each row. Will SQL Server 2000 be smart enough to know that it is the same
>call each time?
Hi Mike,
Unfortunately, no.
(snip)
>I had wanted to call the function once and create a column alias and then
>use that alias for the CAST and SUBSTRING, but cannot get by the column
>issue.
>Something like
>SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS 'stvalue',
> CAST(Left(stvalue,2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
You can't do it this way. A column alias can only be used in the ORDER
BY clause, nowhere else in the query.
You can use a derived table, though:
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
FROM (SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
Hugo Kornelis, SQL Server MVP|||On Fri, 02 Jun 2006 01:00:44 +0200, Hugo Kornelis wrote:
[snip]
> You can't do it this way. A column alias can only be used in the ORDER
> BY clause, nowhere else in the query.
> You can use a derived table, though:
> SELECT exp_AcctNum,
> exp_Amount,
> stvalue,
> CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
> FROM (SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS stvalue
> FROM Expenses) AS d
Thank you very much. I was miles away from this solution and had already
spent 1 day trying several things that did not work. It needs a little
touch up; however, what I finally came up from your template works. What I
really do like is that the call to the function is only made once.
I guess I could put the derived table in a view and have this view call
that view (which ends up being called by another view to get the final
stratification result table). I am not sure I see any benefit in taking
that approach, but may try it just to see what happens. Uhm, maybe another
UDF instead of a View ... too tired to really think at this point.
You have made my day -- I think I will give it up for the day. Thanks.
Mike.
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'prestrat')
DROP VIEW prestrat
GO
CREATE VIEW prestrat
AS
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc],
posrecs,
posdols,
negrecs,
negdols,
[posrecs]+[negrecs] AS totrecs,
[posdols]+[negdols] AS totdols,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType
FROM (SELECT exp_AcctNum,
exp_Amount,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType,
CASE WHEN exp_Amount>=0 THEN 1 ELSE 0 END AS posrecs,
CASE WHEN exp_Amount>=0 THEN exp_Amount ELSE 0 END AS
posdols,
CASE WHEN exp_Amount<0 THEN 0 ELSE 1 END AS negrecs,
CASE WHEN exp_Amount<0 THEN 0 ELSE exp_Amount END AS negdols,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
WHERE Exp_RecordType = '2'
Go
Friday, March 9, 2012
Invalid character value for cast specification/other errors
We are on SQL Server 2000 SP3
We are trying to get to SQL 2005, but cannot do that until we consolidate
our databases so the upgrade will complete in a reasonable amount of time.
I am trying to use bcp to copy data out from each database and move it
into a centralized database. Of the 108 tables/articles I have, only one is
causing me a problem. It appears to be on a text defined column(we have
other text columns that are fine). The column can contain HTML tags, code,
etc. From what I see, the system is encountering some character(s) in the
file that is treating it a an end-of-line. If I adjust the first record
being processed to just have 'normal' text in it, it will load in just fine.
The errors on the bcp insert are:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 01000, NativeError = 4836
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
BCP copy in failed
Any ideas/suggestions on how to get around this? I tried putting the data
into a temporary table, but received the same error. With bcp are the
merge triggers fired? I didn't think so as nothing goes into
MSmerge_contents. I had read about a stored procedure that may be getting
executed that tries to parse the data. They said the solution was to not
call this particular S.P. (don't recall it's name right now). But, if I'm
not executing any merge S.P., then any other S.P.s the system may be using
for the bcp are out of my control aren't they?
Thanks for any help,
Doug
can you verify that the publisher and subscriber have the same mdac level.
If not there is probably something in your data which is an invalid value.
Can you query your date data to ensure that all values are valid.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:57DB6ED2-A5EE-4843-B7E8-E10917575280@.microsoft.com...
> We are on SQL Server 2000 SP3
> We are trying to get to SQL 2005, but cannot do that until we consolidate
> our databases so the upgrade will complete in a reasonable amount of time.
> I am trying to use bcp to copy data out from each database and move it
> into a centralized database. Of the 108 tables/articles I have, only one
> is
> causing me a problem. It appears to be on a text defined column(we have
> other text columns that are fine). The column can contain HTML tags,
> code,
> etc. From what I see, the system is encountering some character(s) in the
> file that is treating it a an end-of-line. If I adjust the first record
> being processed to just have 'normal' text in it, it will load in just
> fine.
> The errors on the bcp insert are:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22003, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 01000, NativeError = 4836
> Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
> BCP copy in failed
> Any ideas/suggestions on how to get around this? I tried putting the data
> into a temporary table, but received the same error. With bcp are the
> merge triggers fired? I didn't think so as nothing goes into
> MSmerge_contents. I had read about a stored procedure that may be getting
> executed that tries to parse the data. They said the solution was to not
> call this particular S.P. (don't recall it's name right now). But, if I'm
> not executing any merge S.P., then any other S.P.s the system may be using
> for the bcp are out of my control aren't they?
> Thanks for any help,
> Doug
>
|||Hillary,
The bcp out & in are both occurring on the server/publisher. At this
point, there is no subscription involvement.
What is valid verses invalid for a text data type? The users can put
basically whatever they want into this data type. It is usually HTML and
text, but I suppose end-of-line, tabs, other HTML formatting can be in there.
We really don't want the system to interpret any of the data in this column,
just want it to move it. My guess is the problem is it's moving out to a
data file. I tried using a temporary table, hoping that would help, but it
didn't.
Looks like the MDAC level on the server is 2.82.1830.0
Doug
"Hilary Cotter" wrote:
> can you verify that the publisher and subscriber have the same mdac level.
> If not there is probably something in your data which is an invalid value.
> Can you query your date data to ensure that all values are valid.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:57DB6ED2-A5EE-4843-B7E8-E10917575280@.microsoft.com...
>
>
|||Anything but binary should be acceptable for text, however it is complaining
about bcp'ing into the datetime column, and a numeric column.
[vbcol=seagreen]
It is possible that there is something in the text data which it is
interpreting as an end of column or end of row character which causes it to
try to insert text into a datetime column. I notice you have exactly 10
failure messages before a final give up message. How many columns are there
in the table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:28B7B9DD-0782-4B07-8C84-978959E6E0B7@.microsoft.com...[vbcol=seagreen]
> Hillary,
> The bcp out & in are both occurring on the server/publisher. At this
> point, there is no subscription involvement.
> What is valid verses invalid for a text data type? The users can put
> basically whatever they want into this data type. It is usually HTML and
> text, but I suppose end-of-line, tabs, other HTML formatting can be in
> there.
> We really don't want the system to interpret any of the data in this
> column,
> just want it to move it. My guess is the problem is it's moving out to a
> data file. I tried using a temporary table, hoping that would help, but
> it
> didn't.
> Looks like the MDAC level on the server is 2.82.1830.0
> Doug
> "Hilary Cotter" wrote:
|||Hilary,
I too think there is something the bcp is interpreting as
end-of-line...something. But it appears to me it is on creating the out
file. I've created .txt, .dat, etc files (don't know that the format of the
output file matters to bcp). When I open the output file, I see part of the
text column has been put on the second line and even some has gone to the
third line.
I'm thinking the creation of the output file is where the problem really is.
It hasn't kept each record's data to its own line. When it tries to
populate a column, it is picking up data from another column which records is
the data type errors.
I have 11 records in the output. I had 'adjusted' the first record before
extracting it so it didnt have any special code in it,just some text. This
line loaded okay. The 10 errors you mention are probably because of the
other 10 records, not so much the number of columns. There are 12 columns in
the table.
It seems the bcp is trying to interpret the data as the output file is
being created. Is there either some way to stop that or some options that
would allow it to properly read this text column? Again, about anything
can be in it. I know if I do an INSERT INTO table1 .......SELECT col1,
col2, etc FROM this_table that works fine. I'm trying to avoid the
INSERT as I don't want the MSmerge_contents to be populated as the subscriber
already has the data. We're just trying to consolidate publisher dbs.
Thanks,
Doug
"Hilary Cotter" wrote:
> Anything but binary should be acceptable for text, however it is complaining
> about bcp'ing into the datetime column, and a numeric column.
>
> It is possible that there is something in the text data which it is
> interpreting as an end of column or end of row character which causes it to
> try to insert text into a datetime column. I notice you have exactly 10
> failure messages before a final give up message. How many columns are there
> in the table?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:28B7B9DD-0782-4B07-8C84-978959E6E0B7@.microsoft.com...
>
>
|||Can you try to bcp the data out, and then bcp it into a table with an
identical structure as the published table. Using the firstrow and lastrow
options you should be able to figure out where the problem data is.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:407ECC97-CDC6-4CBA-8CA3-E750BC0C1B13@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I too think there is something the bcp is interpreting as
> end-of-line...something. But it appears to me it is on creating the out
> file. I've created .txt, .dat, etc files (don't know that the format of
> the
> output file matters to bcp). When I open the output file, I see part of
> the
> text column has been put on the second line and even some has gone to
> the
> third line.
> I'm thinking the creation of the output file is where the problem really
> is.
> It hasn't kept each record's data to its own line. When it tries to
> populate a column, it is picking up data from another column which records
> is
> the data type errors.
> I have 11 records in the output. I had 'adjusted' the first record before
> extracting it so it didnt have any special code in it,just some text.
> This
> line loaded okay. The 10 errors you mention are probably because of the
> other 10 records, not so much the number of columns. There are 12 columns
> in
> the table.
> It seems the bcp is trying to interpret the data as the output file is
> being created. Is there either some way to stop that or some options that
> would allow it to properly read this text column? Again, about anything
> can be in it. I know if I do an INSERT INTO table1 .......SELECT col1,
> col2, etc FROM this_table that works fine. I'm trying to avoid the
> INSERT as I don't want the MSmerge_contents to be populated as the
> subscriber
> already has the data. We're just trying to consolidate publisher dbs.
> Thanks,
> Doug
>
> "Hilary Cotter" wrote:
|||Hilary,
fyi...... I had used the -c option for creating the output file and
reading it in.
I changed this to use the -n option and then the process worked okay.
The out file just won't be as easy to look at for later reference, but at
least the process of moving the data to the consolidated database works,
which is most important at this point.
Thanks for your help,
Doug
"Hilary Cotter" wrote:
> Can you try to bcp the data out, and then bcp it into a table with an
> identical structure as the published table. Using the firstrow and lastrow
> options you should be able to figure out where the problem data is.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:407ECC97-CDC6-4CBA-8CA3-E750BC0C1B13@.microsoft.com...
>
>
We are trying to get to SQL 2005, but cannot do that until we consolidate
our databases so the upgrade will complete in a reasonable amount of time.
I am trying to use bcp to copy data out from each database and move it
into a centralized database. Of the 108 tables/articles I have, only one is
causing me a problem. It appears to be on a text defined column(we have
other text columns that are fine). The column can contain HTML tags, code,
etc. From what I see, the system is encountering some character(s) in the
file that is treating it a an end-of-line. If I adjust the first record
being processed to just have 'normal' text in it, it will load in just fine.
The errors on the bcp insert are:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 01000, NativeError = 4836
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
BCP copy in failed
Any ideas/suggestions on how to get around this? I tried putting the data
into a temporary table, but received the same error. With bcp are the
merge triggers fired? I didn't think so as nothing goes into
MSmerge_contents. I had read about a stored procedure that may be getting
executed that tries to parse the data. They said the solution was to not
call this particular S.P. (don't recall it's name right now). But, if I'm
not executing any merge S.P., then any other S.P.s the system may be using
for the bcp are out of my control aren't they?
Thanks for any help,
Doug
can you verify that the publisher and subscriber have the same mdac level.
If not there is probably something in your data which is an invalid value.
Can you query your date data to ensure that all values are valid.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:57DB6ED2-A5EE-4843-B7E8-E10917575280@.microsoft.com...
> We are on SQL Server 2000 SP3
> We are trying to get to SQL 2005, but cannot do that until we consolidate
> our databases so the upgrade will complete in a reasonable amount of time.
> I am trying to use bcp to copy data out from each database and move it
> into a centralized database. Of the 108 tables/articles I have, only one
> is
> causing me a problem. It appears to be on a text defined column(we have
> other text columns that are fine). The column can contain HTML tags,
> code,
> etc. From what I see, the system is encountering some character(s) in the
> file that is treating it a an end-of-line. If I adjust the first record
> being processed to just have 'normal' text in it, it will load in just
> fine.
> The errors on the bcp insert are:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22008, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
> SQLState = 22003, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast
> specification
> SQLState = 01000, NativeError = 4836
> Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
> BCP copy in failed
> Any ideas/suggestions on how to get around this? I tried putting the data
> into a temporary table, but received the same error. With bcp are the
> merge triggers fired? I didn't think so as nothing goes into
> MSmerge_contents. I had read about a stored procedure that may be getting
> executed that tries to parse the data. They said the solution was to not
> call this particular S.P. (don't recall it's name right now). But, if I'm
> not executing any merge S.P., then any other S.P.s the system may be using
> for the bcp are out of my control aren't they?
> Thanks for any help,
> Doug
>
|||Hillary,
The bcp out & in are both occurring on the server/publisher. At this
point, there is no subscription involvement.
What is valid verses invalid for a text data type? The users can put
basically whatever they want into this data type. It is usually HTML and
text, but I suppose end-of-line, tabs, other HTML formatting can be in there.
We really don't want the system to interpret any of the data in this column,
just want it to move it. My guess is the problem is it's moving out to a
data file. I tried using a temporary table, hoping that would help, but it
didn't.
Looks like the MDAC level on the server is 2.82.1830.0
Doug
"Hilary Cotter" wrote:
> can you verify that the publisher and subscriber have the same mdac level.
> If not there is probably something in your data which is an invalid value.
> Can you query your date data to ensure that all values are valid.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:57DB6ED2-A5EE-4843-B7E8-E10917575280@.microsoft.com...
>
>
|||Anything but binary should be acceptable for text, however it is complaining
about bcp'ing into the datetime column, and a numeric column.
[vbcol=seagreen]
It is possible that there is something in the text data which it is
interpreting as an end of column or end of row character which causes it to
try to insert text into a datetime column. I notice you have exactly 10
failure messages before a final give up message. How many columns are there
in the table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:28B7B9DD-0782-4B07-8C84-978959E6E0B7@.microsoft.com...[vbcol=seagreen]
> Hillary,
> The bcp out & in are both occurring on the server/publisher. At this
> point, there is no subscription involvement.
> What is valid verses invalid for a text data type? The users can put
> basically whatever they want into this data type. It is usually HTML and
> text, but I suppose end-of-line, tabs, other HTML formatting can be in
> there.
> We really don't want the system to interpret any of the data in this
> column,
> just want it to move it. My guess is the problem is it's moving out to a
> data file. I tried using a temporary table, hoping that would help, but
> it
> didn't.
> Looks like the MDAC level on the server is 2.82.1830.0
> Doug
> "Hilary Cotter" wrote:
|||Hilary,
I too think there is something the bcp is interpreting as
end-of-line...something. But it appears to me it is on creating the out
file. I've created .txt, .dat, etc files (don't know that the format of the
output file matters to bcp). When I open the output file, I see part of the
text column has been put on the second line and even some has gone to the
third line.
I'm thinking the creation of the output file is where the problem really is.
It hasn't kept each record's data to its own line. When it tries to
populate a column, it is picking up data from another column which records is
the data type errors.
I have 11 records in the output. I had 'adjusted' the first record before
extracting it so it didnt have any special code in it,just some text. This
line loaded okay. The 10 errors you mention are probably because of the
other 10 records, not so much the number of columns. There are 12 columns in
the table.
It seems the bcp is trying to interpret the data as the output file is
being created. Is there either some way to stop that or some options that
would allow it to properly read this text column? Again, about anything
can be in it. I know if I do an INSERT INTO table1 .......SELECT col1,
col2, etc FROM this_table that works fine. I'm trying to avoid the
INSERT as I don't want the MSmerge_contents to be populated as the subscriber
already has the data. We're just trying to consolidate publisher dbs.
Thanks,
Doug
"Hilary Cotter" wrote:
> Anything but binary should be acceptable for text, however it is complaining
> about bcp'ing into the datetime column, and a numeric column.
>
> It is possible that there is something in the text data which it is
> interpreting as an end of column or end of row character which causes it to
> try to insert text into a datetime column. I notice you have exactly 10
> failure messages before a final give up message. How many columns are there
> in the table?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:28B7B9DD-0782-4B07-8C84-978959E6E0B7@.microsoft.com...
>
>
|||Can you try to bcp the data out, and then bcp it into a table with an
identical structure as the published table. Using the firstrow and lastrow
options you should be able to figure out where the problem data is.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:407ECC97-CDC6-4CBA-8CA3-E750BC0C1B13@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I too think there is something the bcp is interpreting as
> end-of-line...something. But it appears to me it is on creating the out
> file. I've created .txt, .dat, etc files (don't know that the format of
> the
> output file matters to bcp). When I open the output file, I see part of
> the
> text column has been put on the second line and even some has gone to
> the
> third line.
> I'm thinking the creation of the output file is where the problem really
> is.
> It hasn't kept each record's data to its own line. When it tries to
> populate a column, it is picking up data from another column which records
> is
> the data type errors.
> I have 11 records in the output. I had 'adjusted' the first record before
> extracting it so it didnt have any special code in it,just some text.
> This
> line loaded okay. The 10 errors you mention are probably because of the
> other 10 records, not so much the number of columns. There are 12 columns
> in
> the table.
> It seems the bcp is trying to interpret the data as the output file is
> being created. Is there either some way to stop that or some options that
> would allow it to properly read this text column? Again, about anything
> can be in it. I know if I do an INSERT INTO table1 .......SELECT col1,
> col2, etc FROM this_table that works fine. I'm trying to avoid the
> INSERT as I don't want the MSmerge_contents to be populated as the
> subscriber
> already has the data. We're just trying to consolidate publisher dbs.
> Thanks,
> Doug
>
> "Hilary Cotter" wrote:
|||Hilary,
fyi...... I had used the -c option for creating the output file and
reading it in.
I changed this to use the -n option and then the process worked okay.
The out file just won't be as easy to look at for later reference, but at
least the process of moving the data to the consolidated database works,
which is most important at this point.
Thanks for your help,
Doug
"Hilary Cotter" wrote:
> Can you try to bcp the data out, and then bcp it into a table with an
> identical structure as the published table. Using the firstrow and lastrow
> options you should be able to figure out where the problem data is.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:407ECC97-CDC6-4CBA-8CA3-E750BC0C1B13@.microsoft.com...
>
>
Invalid character value for cast specification error
I'm using Access 2K via ODBC to replicated SQL Server 2K. In some tables (not all) when I try to add a record either with a form or directly in the datasheet I get this error message and all form controls/table cells display '#Name?'. The record is added
and displays correctly if I close and reopen the form/table. I can add records with no problem in Enterprise Manager.
Any help gratefully received.
Jonathan Attree
Hi, I am getting exactly the same problem although this problem has only occurred since I implemented merge replication. Does anyone have an answer?
Amanda
and displays correctly if I close and reopen the form/table. I can add records with no problem in Enterprise Manager.
Any help gratefully received.
Jonathan Attree
Hi, I am getting exactly the same problem although this problem has only occurred since I implemented merge replication. Does anyone have an answer?
Amanda
Invalid character value for cast specification
Hi: gurus,
I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
thing works fine if I don't use the datetime type column in sql. If I use the
datetime column in sql I am getting this message "Invalid character value for
cast specification"
Here is my XSD, I am not sure how to map sql annotation for date time field
"effdate" (do I have to) for this XSD. I will really appreciate your response.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="BillerInfo" sql:relation="BillerInfo">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="blrid" type="xsd:string"/>
<xsd:element name="acdind" type="xsd:string"/>
<xsd:element name="effdate" type="xsd:dateTime"/>
<xsd:element name="trnaba" type="xsd:string"/>
<xsd:element name="billername" type="xsd:string"/>
<xsd:element name="billerclass" type="xsd:string"/>
<xsd:element name="dmpprenote" type="xsd:boolean"/>
<xsd:element name="dmppayonly" type="xsd:boolean"/>
<xsd:element name="blroldname" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I guess the problem most likely lies in your data file. I assume that you
have a datetime value which is not matching with the xsd:dataTime format.
If you don't have the control on the input data, you may specify xsd:string
instead of xsd:dataTime.
Thanks.
"Rashid" <Rashid@.discussions.microsoft.com> wrote in message
news:FE70E9A6-B2AF-4BD4-B0DC-88425795A2F1@.microsoft.com...
> Hi: gurus,
> I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
> thing works fine if I don't use the datetime type column in sql. If I use
the
> datetime column in sql I am getting this message "Invalid character value
for
> cast specification"
> Here is my XSD, I am not sure how to map sql annotation for date time
field
> "effdate" (do I have to) for this XSD. I will really appreciate your
response.
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="BillerInfo" sql:relation="BillerInfo">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="blrid" type="xsd:string"/>
> <xsd:element name="acdind" type="xsd:string"/>
> <xsd:element name="effdate" type="xsd:dateTime"/>
> <xsd:element name="trnaba" type="xsd:string"/>
> <xsd:element name="billername" type="xsd:string"/>
> <xsd:element name="billerclass" type="xsd:string"/>
> <xsd:element name="dmpprenote" type="xsd:boolean"/>
> <xsd:element name="dmppayonly" type="xsd:boolean"/>
> <xsd:element name="blroldname" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
thing works fine if I don't use the datetime type column in sql. If I use the
datetime column in sql I am getting this message "Invalid character value for
cast specification"
Here is my XSD, I am not sure how to map sql annotation for date time field
"effdate" (do I have to) for this XSD. I will really appreciate your response.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="BillerInfo" sql:relation="BillerInfo">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="blrid" type="xsd:string"/>
<xsd:element name="acdind" type="xsd:string"/>
<xsd:element name="effdate" type="xsd:dateTime"/>
<xsd:element name="trnaba" type="xsd:string"/>
<xsd:element name="billername" type="xsd:string"/>
<xsd:element name="billerclass" type="xsd:string"/>
<xsd:element name="dmpprenote" type="xsd:boolean"/>
<xsd:element name="dmppayonly" type="xsd:boolean"/>
<xsd:element name="blroldname" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I guess the problem most likely lies in your data file. I assume that you
have a datetime value which is not matching with the xsd:dataTime format.
If you don't have the control on the input data, you may specify xsd:string
instead of xsd:dataTime.
Thanks.
"Rashid" <Rashid@.discussions.microsoft.com> wrote in message
news:FE70E9A6-B2AF-4BD4-B0DC-88425795A2F1@.microsoft.com...
> Hi: gurus,
> I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
> thing works fine if I don't use the datetime type column in sql. If I use
the
> datetime column in sql I am getting this message "Invalid character value
for
> cast specification"
> Here is my XSD, I am not sure how to map sql annotation for date time
field
> "effdate" (do I have to) for this XSD. I will really appreciate your
response.
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="BillerInfo" sql:relation="BillerInfo">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="blrid" type="xsd:string"/>
> <xsd:element name="acdind" type="xsd:string"/>
> <xsd:element name="effdate" type="xsd:dateTime"/>
> <xsd:element name="trnaba" type="xsd:string"/>
> <xsd:element name="billername" type="xsd:string"/>
> <xsd:element name="billerclass" type="xsd:string"/>
> <xsd:element name="dmpprenote" type="xsd:boolean"/>
> <xsd:element name="dmppayonly" type="xsd:boolean"/>
> <xsd:element name="blroldname" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
Invalid cast when trying to use SQLXMLBulkload
I am trying to move data between two servers through my application by outputting the data as XML and then importing it later. I am using .WriteXML and .WriteXMLSchema in my .NET application to create the files, but I get "invalid cast Exception" when trying to BulkLoad them in using SQLXMLBulkLoad4. I even adjust the XSD file to add the sql
atatype in there for the DateTime fields. Is there another field I am breaking on. I looked at the docs and I thinbk my GUIDs are OK since they do not have the {} on them.
atatype in there for the DateTime fields. Is there another field I am breaking on. I looked at the docs and I thinbk my GUIDs are OK since they do not have the {} on them.Example file: XSD file
<?xml version="1.0" standalone="yes"?>
<xs
<xs:element name="STG_Additional_Labs" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs
<xs:element name="FacilityID" type="xs:int" minOccurs="0" />
<xs:element name="Comment" type="xs
<xs:element name="Deleted" type="xs:boolean" minOccurs="0" />
<xs:element name="Lab_Date" type="xs
<xs:element name="Lab_Name" type="xs
<xs:element name="Lab_Time" type="xs
<xs:element name="Lab_Value" type="xs
<xs:element name="PIID" type="xs:int" minOccurs="0" />
<xs:element name="rowguid" msdata
<xs:element name="RowIDGuid" msdata
<xs:element name="SaveDateTime" type="xs
<xs:element name="VersionGuid" msdata
<xs:element name="PatientID" msdata
</xs
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs
Example file: XML file
<?xml version="1.0" standalone="yes"?>
<STG_Additional_Labs>
<Table>
<FacilityID>648</FacilityID>
<Deleted>false</Deleted>
<Lab_Date>2007-05-30T00:00:00-04:00</Lab_Date>
<Lab_Name>Stool Red Subst</Lab_Name>
<Lab_Value>trace</Lab_Value>
<PIID>14</PIID>
<rowguid>03cc9264-8829-464f-b01d-2b18ee4ccdfb</rowguid>
<RowIDGuid>ff9e4e59-6716-46d4-bfcb-61777ed8cf5d</RowIDGuid>
<SaveDateTime>2007-05-30T10:18:24.52-04:00</SaveDateTime>
<VersionGuid>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</VersionGuid>
<PatientID>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</PatientID>
</Table>
<Table>
<FacilityID>648</FacilityID>
<Deleted>false</Deleted>
<Lab_Date>2007-05-19T00:00:00-04:00</Lab_Date>
<Lab_Name>Stool Red Sub</Lab_Name>
<Lab_Value><0.25/ neg</Lab_Value>
<PIID>14</PIID>
<rowguid>876ca5f9-5c0f-4a74-bf03-2e86260ca2a1</rowguid>
<RowIDGuid>45c45895-8008-40ed-a779-c478d476c15b</RowIDGuid>
<SaveDateTime>2007-05-19T10:13:53.857-04:00</SaveDateTime>
<VersionGuid>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</VersionGuid>
<PatientID>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</PatientID>
</Table>
</STG_Additional_Labs>Ack! Sorry about the emoticons!|||Actually, further work on this problem seemed to reveal that the Cast error was actually due to the connection string in my application being wrong (although it is used for all the other connections in the application). The error now coming back is this:
<?xml version="1.0"?><Result State="FAILED"><Error><HResult>0x80004005</HResult><Description>
<![CDATA[Reference to undeclared namespace prefix: 'sql'.
]]></Description><Source>Schema mapping</Source><Type>FATAL</Type></Error></Result>
What would be the proper way to specify the SQL namespace in the top of the file?|||I have essentially resolved my problem by not trying to output the XML data to then read it in later. I am now using the SQLBulkLoad classes to pump the data from a DataTable to the destination SQL server. It would be nice however to have a method in the SQLReader/Writer that can write out a SQL friendly XSD file, so that it can be used later by the SQLXMLBulkLoad class.
Subscribe to:
Posts (Atom)