• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

SQL bigint - problem with count(*)

I have a table that has a clientid (bigint) along with other client information like firstname (nvarchar), lastname(nvarchar), agentid(nvarchar) etc. For some strange reason when I query it with a count(*) I get an error  'Error converting data type varchar to bigint.'

So this works

select *  FROM dbo.vClientsPlans where ([AgentId] = 'alice.chan@mtgarc.ca' and (FirstName like '%'
+  'Gun'  +'%' and FirstName is not null))

this breaks

select count(*)  FROM dbo.vClientsPlans where ([AgentId] = 'alice.chan@mtgarc.ca' and (FirstName like '%'
+  'Gun'  +'%' and FirstName is not null))

Any ideas what would cause this?
0
boukaka
Asked:
boukaka
1 Solution
 
tigin44Commented:
the queries identical, what is the error message you get?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree, that should not cause the issue.
anyhow, please try to use COUNT_BIG: http://msdn.microsoft.com/en-us/library/ms190317.aspx
0
 
tigin44Commented:
do you try to assing the returned value to avariable? if so check the variable type...
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
FredTangCommented:
Please try it,I think the code as follow will works,
select count(*) as rows_count  FROM dbo.vClientsPlans where ([AgentId] = 'alice.chan@mtgarc.ca' and (FirstName like '%'
+  'Gun'  +'%' and FirstName is not null))
0
 
VovinECommented:
You need to use COUNT_BIG:

select count_big(*)  FROM dbo.vClientsPlans where ([AgentId] = 'alice.chan@mtgarc.ca' and (FirstName like '%'
+  'Gun'  +'%' and FirstName is not null))

and make sue it is assigned to bigint (not int!) where the result is used.
0
 
boukakaAuthor Commented:
Wow, everyone answers so quickly. I will respond.
tigin44 - the difference between the queries is the Select count(*) vs Select *  - error message is : 'Error converting data type varchar to bigint.'

angelII - I tried count_big - same result

tigin44 - I don't assign anything to anything I run the queries exactly as you see them, nothing more

fredtang - I'm sorry but that returns the same error.

It's very strange - perhaps there is some garbage data in the table that's creating a false error.
0
 
boukakaAuthor Commented:
By the way, this error started "all of a sudden", the query used to work perfectly until my "partner" starting working on the database but she doesn't know what she did to cause it.
0
 
boukakaAuthor Commented:
She has just told me that this is not the first time this has happened to her.
She says "Last time I had this problem I have re-built all the indexes" to fix it.
What I'm worried about is, if it's happening randomly, it's going to drive the users crazy. Without knowing what is causing it, we can't avoid it.
... very strange...
0
 
boukakaAuthor Commented:
FYI - we found the issue - there was corrupt data in the table - 2 records were messed up, we took the view apart and rebuilt it piece by piece from scratch until we found where it was breaking, then fixed the records causing it and now it's ok. Thank you to everyone who responded.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now