Error from AS2005 - Binding for column 'xxxxx' is not a ColumnBinding type

I've seen this question come up a couple times on the public .olap newsgroup, but I've never seen an answer, so I'm going to give it another try:

I'm getting an error when I try to incrementally process my SSAS 2005 cube:

Errors in the high-level relational engine. The binding for the 'column-name' column is not a ColumnBinding type.

In this case, the column name that's given is a name that occurs in only 1 table in the underlying schema, so I know unambiguously which column it's talking about. The relevant part of the schema is roughly this:

create table Date(
DateID int not null primary key,
/* ... */
);

create table File(
FileID int not null primary key,
FileDateID int not null foreign key references Date(DateID),
/* ... */
);

create table Fact(
/* ... */
DateID int not null foreign key references Date(DateID),
FileID int not null foreign key references File(FileID)
);

The column that's named in the error message is File.FileDateID. Note that this is not a pure snowflake schema - there's a "loop" that associates files with dates in addition to other date relationships within the main fact table. I'm just guessing that the root of the problem has something to do with non-snowflake-ness of the schema. Full processing of the database/cube works fine - it's only when I try to process incrementally or create aggregations that I get this error.

Can anyone explain what this error really means and what to do about it?Anyone?|||

Carl,

When you do the incremental processing, are you using a table as the source or a query?

Dave Fackler

|||

It was a query of the form select * from MyFactTable where SomeIncrementalCondition.

Of course, the error has gone away since I first started asking about this error a month ago. Something I changed since then "fixed" the problem (this is a product in development, so I'm constantly fiddling with the cube and dimension definitions - I have no idea which change fixed this problem, unfortunately).

Error from AS2005 - Binding for column 'xxxxx' is not a ColumnBinding type

I've seen this question come up a couple times on the public .olap newsgroup, but I've never seen an answer, so I'm going to give it another try:

I'm getting an error when I try to incrementally process my SSAS 2005 cube:

Errors in the high-level relational engine. The binding for the 'column-name' column is not a ColumnBinding type.

In this case, the column name that's given is a name that occurs in only 1 table in the underlying schema, so I know unambiguously which column it's talking about. The relevant part of the schema is roughly this:

create table Date(
DateID int not null primary key,
/* ... */
);

create table File(
FileID int not null primary key,
FileDateID int not null foreign key references Date(DateID),
/* ... */
);

create table Fact(
/* ... */
DateID int not null foreign key references Date(DateID),
FileID int not null foreign key references File(FileID)
);

The column that's named in the error message is File.FileDateID. Note that this is not a pure snowflake schema - there's a "loop" that associates files with dates in addition to other date relationships within the main fact table. I'm just guessing that the root of the problem has something to do with non-snowflake-ness of the schema. Full processing of the database/cube works fine - it's only when I try to process incrementally or create aggregations that I get this error.

Can anyone explain what this error really means and what to do about it?Anyone?|||

Carl,

When you do the incremental processing, are you using a table as the source or a query?

Dave Fackler

|||

It was a query of the form select * from MyFactTable where SomeIncrementalCondition.

Of course, the error has gone away since I first started asking about this error a month ago. Something I changed since then "fixed" the problem (this is a product in development, so I'm constantly fiddling with the cube and dimension definitions - I have no idea which change fixed this problem, unfortunately).

Error during preparedstmt.setNull(1) in timestamp datatype column

------------------------

I would like to get information related to timestamp data type in SQL Server (WANT TO SET NULL IN TIMESTAMP COLUMN )I have Following case

try {
try {
stmt.execute("drop table timestampTable");
}
catch (SQLException ex1) {
}
stmt.execute(
"Create table timestampTable(c1 int Primary Key, c2 Timestamp)");

PreparedStatement pst = connection.prepareStatement(
"insert into timestampTable values(?,?)");
pst.setInt(1, 2);
pst.setNull(2, Types.TIMESTAMP);
pst.execute();
}
catch (SQLException ex) {
ex.printStackTrace();
}

TRACE IS GIVEN BELOW
====================

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Disallowed implicit conversion from data type datetime to data type timestamp, table 'ClientDB.dbo.timestampTable', column 'c2'. Use the CONVERT function to run this query.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInter nal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec ute(Unknown Source)
at JDBC.TestSQLServer.testTIMETAMPDataTypes(TestSQLSe rver.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.daffodilwoods.tools.testworker.TestRunner.runM ethod(TestRunner.java:159)
at com.daffodilwoods.tools.testworker.TestRunner.runI nSequence(TestRunner.java:83)
at com.daffodilwoods.tools.testworker.TestRunner.runT estCase(TestRunner.java:4

PLEASE REPLY ME AS SOON AS POSSIBLE

THANKS

SUBE SINGHit appears that types.timestamp is actually a datetime data type. timestamp data type in sql has nothing to do with date and/or time.

error deploying analysis server cube

Has anyone ever received this message before?
"The Description element at line 3123, column 29 (namespace
http://schemas.microsoft.com/analys...ces/2003/engine) cannot appear
under
Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Cubes/Cu
be/Dimensions/Dimension."
All I was doing was adding description to the properties of the dimension
tables. I was doing this so that the report user can see pop-up descriptions
in Report Builder.
I haven't been able to resolve this problem. Not only that - Now that I've
received this error, I haven't been able to reset the solution file so that
I
don't get this error. I have even removed all the description changes I
made, but to no avail.
Can anyone help me?
--JPThere are a couple of things you could try.
1) you could run Profiler while you are trying to deploy, this way you
would be able to capture the XMLA that is being sent to the server. You
would then be able to copy and paste the XMLA into a text editor and
search it. Maybe there is one change that you have missed undoing.
2) If all else fails, you could re-create your project from the database
on the server by starting a new project and choosing the "import
Analysis Services 9.0 database option"
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell
In article <16360AA6-10E3-4E75-9124-D939176CC1C2@.microsoft.com>,
Garza_JP@.discussions.microsoft.com says...
> Has anyone ever received this message before?
> "The Description element at line 3123, column 29 (namespace
> http://schemas.microsoft.com/analys...ces/2003/engine) cannot appear
> under
> Envelope/Body/Execute/Command/Batch/Alter/ObjectDefinition/Database/Cubes/
Cube/Dimensions/Dimension."
> All I was doing was adding description to the properties of the dimension
> tables. I was doing this so that the report user can see pop-up descriptio
ns
> in Report Builder.
> I haven't been able to resolve this problem. Not only that - Now that I've
> received this error, I haven't been able to reset the solution file so tha
t I
> don't get this error. I have even removed all the description changes I
> made, but to no avail.
> Can anyone help me?
> --JP
>

error converting the char value BUT IT IS A CHAR ALREADY

SQL Server 2000 SP3a
Access 2000 SP3 (using adp)
Im going mildly loopy, hopefully someone can aid my sanity.
Table in SQL Server had a column (column_a tinyint), data contained is just
ones and zeros. In Enterprise Manager I changed the data type of column_a t
o
char(1), so that I could then go and replace the ones and zeros with 'Y' and
'N'
When I try and change one of the values, through either Access or Enterprise
Manager, it errors with:
"Syntax error converting the char value 'Y' to a column of data type int"
I don't understand, I've changed the columns data type from tinyint to
char(1), so surely these updates should work. Whats the problem with this?
I've taken the db offline, I've even tried booting the server (clutching at
straws). What might not have been updated correctly?
Any suggestions would be great!
Its these stupid things which should be a two second job, but end up taking
me hours. And yes, if I'd got it right in the first instance, then this
wouldn't have been a problem :)
Thanks for any replies.I would have thought that the logical way to proceed would have been:
1. Add a field of Char(1) to the table with a different name than the source
field
2. Update this new field to 'Y' and 'N' based on the source field's values
3. Remove the source field
4. Rename the new field to that of the source field.
I'm surprised that SQLEM would even let you change a TinyInt to a Char in th
e
first place. If it did, what values are in that field?
Thomas|||1- Be sure it has been changed. See deifnition using view
information_schema.columns
2 - Try doing the update from query analyzer.
update table1
set colB = substring('NY', cast(colB as int) + 1, 1)
AMB
"Steve'o" wrote:

> SQL Server 2000 SP3a
> Access 2000 SP3 (using adp)
> Im going mildly loopy, hopefully someone can aid my sanity.
> Table in SQL Server had a column (column_a tinyint), data contained is jus
t
> ones and zeros. In Enterprise Manager I changed the data type of column_a
to
> char(1), so that I could then go and replace the ones and zeros with 'Y' a
nd
> 'N'
> When I try and change one of the values, through either Access or Enterpri
se
> Manager, it errors with:
> "Syntax error converting the char value 'Y' to a column of data type int"
> I don't understand, I've changed the columns data type from tinyint to
> char(1), so surely these updates should work. Whats the problem with this
?
> I've taken the db offline, I've even tried booting the server (clutching a
t
> straws). What might not have been updated correctly?
> Any suggestions would be great!
> Its these stupid things which should be a two second job, but end up takin
g
> me hours. And yes, if I'd got it right in the first instance, then this
> wouldn't have been a problem :)
> Thanks for any replies.|||> Table in SQL Server had a column (column_a tinyint), data contained is
just
> ones and zeros. In Enterprise Manager I changed the data type of column_a
to
> char(1), so that I could then go and replace the ones and zeros with 'Y'
and
> 'N'
> When I try and change one of the values, through either Access or
Enterprise
> Manager, it errors with:
> "Syntax error converting the char value 'Y' to a column of data type int"
While I don't have any obvious suggestion to help with EM, Access has a bit
of odd behavior. If you "Link" a SQL Server table into an Access database,
Access "remembers" the schema of the table, so that if you then do something
to the schema of the SQL Server table, Access is not aware of the change.
The workaround that I have used is to delete the linked table in Access and
re-link to it. I also like to rename it to get rid of the stupid "dbo_"
prefix that Access adds.
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||Wow, several replies, thanks.
Thomas
I did not mention this, but there are several triggers and check consraints,
basically several dependencies to the column_a. I did try creating a new
column_a_new (char1), then tried renaming column_a to column_a_old, but it
wasn't having it because of the dependencies. In an Access .mdb you can get
away with this, but sql server appears a bit more rigid in this type of
workaround.
Alejandro
I made the changes a few days ago (I have been trying to fix it in that
time) and the server and cient machine have been booted a few times.
I double checked information_scheme.columns, great suggestion thanks, and
got this which seems a little odd to me as I set it to char(1), which is doe
s
display as in Access and EM.
ORDINAL_POSITION
16
COLUMN_DEFAULT
('N')
IS_NULLABLE
No
DATA_TYPE
nvarchar
CHARACTER_MAXIMUM_LENGTH
50
CHARACTER_OCTET_LENGTH
100
QA gives the same error when doing an update set
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'N' to a column of data type int.
Mike
Yeah, there are lots of little tricks which need to be remembered with
Access (especially 2000) and SQL Server :)
I am using an .adp which is a bit different to an .mdb with linked tables,
but F5 is definitely your freind :)
I have booted the client and server, as I've been messing around for a few
days, so it should be refreshed. And it occurs in EM+QA too.
Thanks to all replies, its sounding horribly like Im going to have to drop
all dependencies, then re-create them after deleting the old column and
adding a new one. Maybe use EM to create a .sql script of the objects
including drop_all first, then alter the script to use char, then run
it....What a hassle, certainly won't make this mistake again, hopefully ;)
PS, is it better to reply to each individual post, or top post with a msg
like this, or bottom post with a msg like this ie including replies to
several people?
Or does it not really matter?
"Mike Labosh" wrote:

> just
> to
> and
> Enterprise
> While I don't have any obvious suggestion to help with EM, Access has a bi
t
> of odd behavior. If you "Link" a SQL Server table into an Access database
,
> Access "remembers" the schema of the table, so that if you then do somethi
ng
> to the schema of the SQL Server table, Access is not aware of the change.
> The workaround that I have used is to delete the linked table in Access an
d
> re-link to it. I also like to rename it to get rid of the stupid "dbo_"
> prefix that Access adds.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>|||> I did not mention this, but there are several triggers and check consraints,d">
> basically several dependencies to the column_a. I did try creating a new
> column_a_new (char1), then tried renaming column_a to column_a_old, but it
> wasn't having it because of the dependencies. In an Access .mdb you can g
et
> away with this, but sql server appears a bit more rigid in this type of
> workaround.
The obvious answer is to drop the triggers and check constraints before you
run
through the steps I suggested and then re-add the triggers and check constra
ints
afterwards. It's not that difficult. You can use the query analyzer to scrip
t
the creation of the appropriate triggers and check constraints as well as sc
ript
the drop scripts for these. Besides, it is likely that these triggers and ch
eck
constraints need to change anyway.

> PS, is it better to reply to each individual post, or top post with a msg
> like this, or bottom post with a msg like this ie including replies to
> several people?
> Or does it not really matter?
Doubt it matters much.
Thomas|||On Mon, 11 Apr 2005 10:00:01 -0700, Steve'o wrote:

>PS, is it better to reply to each individual post, or top post with a msg
>like this, or bottom post with a msg like this ie including replies to
>several people?
>Or does it not really matter?
Hi Steve'o,
Trying to start a flame-war? <grin>
Top-posting vs bottom-posting can turn into a heated debate. I believe
that most Usenet regulars prefer bottom-posting or inline reply (quote,
reply, quote, reply, ...). However, MS Outlook defaults to top-posting,
as does (if I recall correctly) MS' internet portal to the MS related
groups.
Since this group is about an MS product, a relatively large number of
users use Outlook or MS' internet portal for access. As a result, you'll
see lots of top-posting here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Error converting datatypes

Hi,

This script gets run by a job every 3 mins, and it's falling over with an "Error converting varchar value... to column of datatype int", and I think it's on this line:

select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'

...where I'm trying to build up a string by dropping an ID number (datatype int) into it.

So I tried:

select @.sbj1='New ICNA Forum Post (ThreadID='+CAST(@.existingID AS varchar(100))+')'

and:

select @.sbj1='New ICNA Forum Post (ThreadID='+CONVERT(varchar(100), @.existingID)+')'

Both of these result in the job running successfully, but no emails get sent and the job history shows the error "Incorrect syntax near 'Forum'." On the good side, it's supposed to be looping through the email-sending bit 4 times (there are currently 4 users) and sure enough, it repeats that error message 4 times.

The full script follows below. I'd be hugely grateful if anyone could point out what I'm doing wrong, and how to do it right.

Cheers.

Declare @.hMessage varchar(255),@.msg_id varchar(255)
Declare @.MessageText varchar(8000),@.message varchar(8000)
Declare @.MessageSubject varchar(8000),@.subject varchar(8000)
Declare @.Origin varchar (8000), @.originator_address varchar(8000)

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN

exec master.dbo.xp_readmail
@.msg_id=@.hMessage,
@.message=@.MessageText OUT,
@.subject=@.MessageSubject OUT,
@.originator_address=@.Origin OUT

IF ((SELECT COUNT(*) FROM forum_users WHERE email = @.Origin) = 1) -- IF email from forum-recognised address
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF email has a thread ID
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.existingID)

-- Do mailing

declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1

while @.@.FETCH_STATUS=0
begin
select @.bdy1='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1

END
ELSE -- IF email has no thread ID
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500) -- Create a new thread record and use the resulting ID to add a thread_post record
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.newID)

-- Do mailing

declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2

while @.@.FETCH_STATUS=0
begin
select @.bdy2='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj2='New ICNA Forum Post (ThreadID='+@.newID+')'
exec master.dbo.xp_sendmail @.em2,@.bdy2,@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2

END
END

SET @.hMessage = NULL

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
ENDHave you tried substituing the xp_sendmail with SELECT just to see if you have formatted very thing correctly.|||Ah. Did I mention that my grasp of SQL and its debugging techniques was a little sparse?

Thanks very much for your help, but could you possibly explain how do do that?

Cheers.|||OK;

In your script you have a line like:
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
RewriteSELECT 'master.dbo.xp_sendmail', @.em1,@.bdy1,@.sbj1
Do this for all xp_sendmail. This may shine some light.|||Aha! Well, at least I've learnt how to get some debugging output. Unfortunately I'm none the wiser as to why sendmail isn't working.

It loops 4 times, once for each email address in my forum_users table. I'm sending the "trigger" email from a hotmail address (needs to be external to our network) and thus for each email it tries to send the subject and body look like:

@.sbj1:
New ICNA Forum Post (ThreadID=24)

@.bdy1:
New ICNA Forum Post: Friday morning test 1 __________________________________________________ _______________ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com

Which are pretty well exactly what I was expecting - so why on earth is it falling over? :(|||At last, got it working! It didn't like this line:

exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1

when I replaced it with:

exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1

it worked fine. I don't know why, and I don't care :) It works...

Error converting data type varchar to float.

Hi,

I am experencing a data type cast issue in T-SQL, basically our application comtains both numeric and non-numeric data in the same column, I want to retrieve the numeric data only.

Assuming a table has one column

create table TT(c1 varchar(20) )

and insert 2 rows into the table

insert into TT values('100')

insert into TT values('test')

Now if I want to do numeric comparison on column c1, I will get an error, e.g.

select * from TT where c1 >100

because SQL server trying to convert 'test' to a number impcilitly.

I tried to create an UDF to handle the non-numeric data conversion, e.g. if the data is numeric then return the number, if the data is non-numeric, then return a NULL.

create function numcast(@.value VARCHAR) returns numeric as

begin

begin try

declare @.rtnval numeric;

set @.rtnval = cast(@.value as numeric);

return(@.rtnval);

end try

begin catch

return(NULL);

end catch;

end;

There are 2 problems in above function, 1. it still failed on cast() function, 2. T-SQL function does support try-catch structure.

Does anybody know what the best solution to handle this kind of issues?

Many thanks

try this

select * from TT
where ISNUMERIC(c1 + 'e0')=1
and c1 > 100

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Use the ISNUMERIC function to make sure the data is numeric before doing your comparison:

SELECT * FROM TT WHERE ISNUMERIC(c1) = 1 and c1 > 100

The query optimizer will only check the second part of the criteria (c1 > 100) if the first part is true. Note that if you flip the criteria, you will end up with a cast error again.|||

you can't just use ISNUMERIC, ISNUMERIC has a 'bug' isnumeric returns 1 for '1e4' for example

run the code below, that is why I use isnumeric( + 'e0')

declare @.v varchar(50)
select @.v ='1e4'
select isnumeric(@.v), isnumeric(@.v + 'e0')

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks for the info. I've never run into the problem using ISNUMERIC but sure enough your example does return the wrong info.

Is this 'bug' documented anywhere by Microsoft?|||

Not sure if it is documented anywhere but here is another one for you 1d2

declare @.v varchar(50)
select @.v ='1d2'
select isnumeric(@.v), isnumeric(@.v + 'e0')

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

The isnumeric issue is not a bug. 1e4 is a valid number in scientific notation as well as 1d2, etc. The function works as expected, you just have to be aware of what it considers a number.

There is a better way to test if it is a valid integer, but, I am running short of time at the moment, so, this is untested but similiar to what you need using regular expression matching something like:

select col1 from table
where col1 LIKE '[0-9][0-9][0-9]'

|||

>>The isnumeric issue is not a bug.<<

Agreed

>> 1e4 is a valid number in scientific notation as well as 1d2, etc.<<

True

>> The function works as expected<<

Definitely false. But that is a limitation of the function. It is WAY too open. Vote for this as an improvement:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=177308

I wholeheartedly agree that a parameter for type would help.

>>you just have to be aware of what it considers a number.<<

True again :)

|||

I agree with both of you that why I said 'bug' and not bug ;)

And yes IsInteger would be a nice addition to have as a function or as Louis suggested adding a parameter for type could also work

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

>>And yes IsInteger would be a nice addition to have as a function or as Louis suggested adding a parameter for type could also work<<

Technically I was repeating what the poster in Connect said, but yes, either would be nice. A function per type?

|||Thank you all for your help, the information is so helpful to me.|||

The value "$1" also passes the isnumeric test, even though it can only be converted to a "money" datatype.

The problem with using LIKE '[0-9][0-9][0-9]' is that it doesn't account for variable length numbers.

I definitely vote for one function per type: isinteger, isdecimal, ismoney, etc.

Of course nowadays we can at least do something like this:

declare @.x varchar(10), @.y int

set @.x = ' 12x'

begin try select @.y = convert(int, @.x) end try

begin catch end catch

Ron

|||you can use col1 like '%[0-9]%'|||

>>you can use col1 like '%[0-9]%'

No you can't take a look at this

create table blah(col1 varchar(40))
insert into blah values('100')
insert into blah values('100B')
insert into blah values('1A00')
insert into blah values('A100')

select * from blah where col1 like '%[0-9]%'

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Error converting data type varchar to datetime.

Here is the stored procedure
the data type for the column b_trn_tentdte is decimal(8,0).

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure Prc_New_cus_wo_prev_posted
@.b_trn_tendte_Beg_Date datetime
,@.b_trn_tendte_End_Date datetime
@.b_trn_tendte_Beg_Date1 datetime
,@.b_trn_tendte_End_Date2 datetime

as

declare @.Beg_tentdte datetime
declare @.End_tentdte datetime
declare @.Beg_tentdte1 datetime
declare @.Beg_tentdte2 datetime

set @.Beg_tentdte = convert(varchar(10), @.b_trn_tendte_Beg_Date, 112)
set @.End_tentdte = convert(varchar(10), @.b_trn_tendte_End_Date, 112)
set @.Beg_tentdte1 = convert(varchar(10), @.b_trn_tendte_Beg_Date1, 112)
set @.Beg_tentdte2 = convert(varchar(10), @.b_trn_tendte_End_Date2, 112)

/* this query looks for customers with posted
transactions but none prior to the date range
in question */

Select distinct b_cus_cname
from
bar_cus_db_rec
,bar_trn_db_rec b
where b.b_trn_instid = ''
and b.b_trn_instid = b_cus_instid
and b.b_trn_actid = b_cus_cusid
and convert(varchar(10), b.b_trn_tentdte) between @.Beg_tentdte and @.End_tentdte
and not exists (select c.b_trn_actid
from bar_trn_db_rec c
where c.b_trn_instid = b.b_trn_instid
--and c.b_trn_tentdte between 19970901 and 20030229
and convert(varchar(10), c.b_trn_tentdte) between @.Beg_tentdte1 and @.Beg_tentdte2
and c.b_trn_actid = b.b_trn_actid)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

every time I run the query, I get this error message:
Error converting data type varchar to datetime.

thanks for your assistance.I am wondering if you are doing any data validation on your paramaters in you application before you call your sp.

If isDate(parameter) Then
'call your sp
Else
'prompt user for input
End if|||Disregard previous.

you are coverting your parameters to char and trying to assign them to a dattime variable.

set @.Beg_tentdte = convert(varchar(10), @.b_trn_tendte_Beg_Date, 112)
set @.End_tentdte = convert(varchar(10), @.b_trn_tendte_End_Date, 112)
set @.Beg_tentdte1 = convert(varchar(10), @.b_trn_tendte_Beg_Date1, 112)
set @.Beg_tentdte2 = convert(varchar(10), @.b_trn_tendte_End_Date2, 112)

remove your converts|||SQL Server will implicitly convert between character strings and datetime values if the strings are in the proper format. But in your case you are starting with a numeric value.
A numeric value such as 19970901 cannot be implicitly converted to a datetime datatype, even after casting as a character string. You will need to explicitly convert to datetime, like this:
SELECT CONVERT(datetime, CONVERT(varchar(10), c.b_trn_tentdte))

...and for good measure you should probably be more specific about the format, like this:
SELECT CONVERT(datetime, CONVERT(varchar(10), c.b_trn_tentdte), 112)|||...and if you stick around long enough, you "may" become a "Format Man"!|||sir! cannonball over the fore end sir.

error column

Hi,

There is an error output from one oledb destination which causes error to another which populated a table called tblFailedRows.

I have mapped the errorcolumn to the errorcolumn in tblFailedRows.

When looking at the errorcolumn in tblFailedRows I see just numbers not the column name.

How do I get the column name that causes the error?

Thanks

Here's a thread with information on this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509836&SiteID=1

error column

Hi,

Using SSIS, while importing (source i.e. .csv into destination i.e. sql server table), how is it possible to log the source COLUMN which causes the error in the row?

Thanks

Hello Arkiboys,

Many data flow components, including most sources and destinations, support an "Error Ouput" concept whereby you can work with the individual rows of data that could not be successfully processed by a component. This error output includes an ErrorCode and ErrorColumn that you can use in many cases to determine the column that is causing the error.

For more information, I'd suggest taking a look at this:

http://msdn2.microsoft.com/en-us/library/ms141679.aspx

-David

|||

Hi,

I followed the lonk.
Not sure why I do not get the error columns expanded as it says in the link.

Still do not know how to pull the column out i.e. I have a stored procedure which I would like to record the error columns. How do I get these error columns name and rows?

Thanks

Error Codes?

My data flow component is throwing an error and the only help I get is the following:

error code: -1071607694

error column: 257

What in the world does this mean? Can it get more cryptic than this?

Search is your friend.

http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0209072.html

You have a conversion issue.

Error after creating new database

New installation of SQLS 2000 on Win XP Pro
Created new database, created new table, then opened table - got "Unknown
error: 8007007F". No column headers are showing and no columns are editable.
Same with every table in the database. Same with every other default
database - Northwind, pubs, master etc.
Any ideas anyone?Andew
Download MDAC2.7.
"Andrew" <x@.x.com> wrote in message
news:#jG7aOs#DHA.2636@.TK2MSFTNGP09.phx.gbl...
> New installation of SQLS 2000 on Win XP Pro
> Created new database, created new table, then opened table - got "Unknown
> error: 8007007F". No column headers are showing and no columns are
editable.
> Same with every table in the database. Same with every other default
> database - Northwind, pubs, master etc.
> Any ideas anyone?
>

Error after creating new database

New installation of SQLS 2000 on Win XP Pro
Created new database, created new table, then opened table - got "Unknown
error: 8007007F". No column headers are showing and no columns are editable.
Same with every table in the database. Same with every other default
database - Northwind, pubs, master etc.
Any ideas anyone?Andew
Download MDAC2.7.
"Andrew" <x@.x.com> wrote in message
news:#jG7aOs#DHA.2636@.TK2MSFTNGP09.phx.gbl...
> New installation of SQLS 2000 on Win XP Pro
> Created new database, created new table, then opened table - got "Unknown
> error: 8007007F". No column headers are showing and no columns are
editable.
> Same with every table in the database. Same with every other default
> database - Northwind, pubs, master etc.
> Any ideas anyone?
>

error adding a column to a table

am trying to add a temporary column to a table(for the duration of the stored procedure)

code

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL

SET IDENTITY_INSERT [EpiSurv].[ECC] ON

INSERT INTO [EpiSurv].[ECC]

( [Name]

, [IsEnabled]

, [CreatedByUserID]

, [CreatedDateTime]

, [tCaseMigrationID]

)

SELECT DISTINCT placeofwk as Workplace

, 0 as [IsEnabled]

, 0 as [CreatedByUserID]

, getdate()

, CaseMigrationID

FROM tCaseMigration

WHERE (WSP1 = 3)

AND (placeofwk is not null and placeofwk <> '')

order by workplace

get back

Msg 207, Level 16, State 1, Procedure spMigration_Step4_Populate_ECCtable, Line 18

Invalid column name 'tCaseMigrationID'.

So your alter table works, and then your insert works, and then the proc fails during the query?

SELECT DISTINCT placeofwk as Workplace
, 0 as [IsEnabled]
, 0 as [CreatedByUserID]
, getdate()
, CaseMigrationID <<<<<-- should this be your newly created column name "tCaseMigration"?
FROM tCaseMigration <<<<<-- is this a correct table name. This is the column you created, is
there a table with the same name or should this be [EpiSurv].[ECC]?
WHERE (WSP1 = 3)
AND (placeofwk is not null and placeofwk <> '')
order by workplace

|||

dan

thx for the reply

i think the t-sql is "correct" - i am trying to add a temporary column on the ECC table to store the CaseMigrationID from the tCaseMigration table - the new temporary column name is tCaseMigrationID on the EpiSurv.ECC table

|||

Maybe you have just to add a semicolon (;) to your line:

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL ;

When i run the alter Table Statement and the Insert statement in the same batch, then i get a similar error message. By adding the semicolon, i have solved it.

But i haven't tried it using a stored procedure.

|||

Yes ..

You will get a compile time error.

When you create the SP the Table ECC doesn;t have the column tCaseMigrationID and the compilation will be fail and you SQL Server won't allow to create a SP..

The possible alternate solution is,

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL

SET IDENTITY_INSERT [EpiSurv].[ECC] ON

Exec ('INSERT INTO [EpiSurv].[ECC]

( [Name]

, [IsEnabled]

, [CreatedByUserID]

, [CreatedDateTime]

, [tCaseMigrationID]

)

SELECT DISTINCT placeofwk as Workplace

, 0 as [IsEnabled]

, 0 as [CreatedByUserID]

, getdate()

, CaseMigrationID

FROM tCaseMigration

WHERE (WSP1 = 3)

AND (placeofwk is not null and placeofwk <> '''')

order by workplace')