Solved

Stupid problem with BigInt converting!!!

Posted on 2006-11-08
31
3,634 Views
Last Modified: 2008-01-09
SQL 2005.

Why is it doesnt work:

SELECT  CAST(1 AS bigint) AS a

gives an error:

"Cannot call methods on bigint."
0
Comment
Question by:vadim63
  • 14
  • 11
  • 5
  • +1
31 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17900304
works for me - SQL 2000, SQL 2005 express, SQL 2005 64 bit
0
 
LVL 10

Author Comment

by:vadim63
ID: 17900314
No, SQL 2005 32.
0
 
LVL 20

Expert Comment

by:Sirees
ID: 17900320
Works for me too on SQL 2005 32
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900331
Like I said, 2005 express (32) and 64 bit Enterprise
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900369
perhaps you want to post more code - or is that the statement that is failing!?
0
 
LVL 10

Author Comment

by:vadim63
ID: 17900376
Ok, If I start it in a new query window, it works fine, but if I run it like

SELECT  CAST(1 AS bigint) AS a
from mytable

it gives an error.

BTW,

SELECT  CAST(1 AS int) AS a
from mytable

works fine also. The problem with bigint only.
0
 
LVL 10

Author Comment

by:vadim63
ID: 17900389
There's no more code. There's the only statement.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17900417
I presume that your database is in a compatibility mode of 70 or lower... when bigints were not available...
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900432
That should also work.
0
 
LVL 10

Author Comment

by:vadim63
ID: 17900447
It was started on SQL 2000. But now (I checked it) the compatibility level is SQL 2005 (90)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900504
works on compatibility mode of 70
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900508
Is that the entire error message, or is there more?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17900522
can you post the table structure, please?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17900537
>works on compatibility mode of 70
I confirm that it works in 70... was just an idea...
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900567
Good idea - I hadn't thought of it, and had to test it
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 29

Expert Comment

by:Nightman
ID: 17900576
did it in 2K as well, just to be sure ;)
0
 
LVL 10

Author Comment

by:vadim63
ID: 17900588
The entire message is:

SQL Execution Error.
Executed SQL statement: SELECT CAST (1 AS bigint).ToString() AS a FROM aaa
Error Source: .Net SqlClient Data Provider
Error Message: Cannot call methods on bigint.

The table structure:

ID                smallint
PhoneTXT    varchar(15)
phonedig      bigint
Tex              varchar(MAX)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900672
Where are you executing this statement from? Not Query analyser
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900782
Are you using the View Designer in Management Studio? This fails there. Appears to be a bug in the Net SqlClient Data Provider.

I recommend that you create the view from T-SQL using Query Analyser.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17900928
What is that ToString?
in TSQL you don't need that...

SELECT CAST (1 AS bigint).ToString() AS a FROM aaa
SELECT CAST( CAST (1 AS bigint) as varchar(20)) AS a FROM aaa
0
 
LVL 10

Author Comment

by:vadim63
ID: 17900977
I did not type "ToString()". What I did is: "SELECT CAST (1 AS bigint) AS a FROM aaa"

And "ToString()" shows up in error message only. That's why I called this problem - stupid.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17900986
ToString() is automatically inserted in the View Designer in Management Studio - googled a little and found another similar post (http://www.dbtalk.net/microsoft-public-sqlserver-programming/sql-execution-error-cannot-call-217064.html).

Tried it and got the same error.
0
 
LVL 10

Author Comment

by:vadim63
ID: 17901004
What a hell?

SELECT     CAST(CAST(1 AS bigint) AS varchar(20)) AS a FROM  aaa  - works
but
SELECT     CAST(1 AS bigint) AS a FROM   aaa  - not
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17901015
Also suggests you will get the same error in a Visual Studio 2005 Database project.

So stupid error is correct - looks like a bug in VS or the .Net SqlClient Data Provider (not sure which).

As I said, use T-SQL to create the view instead.
0
 
LVL 10

Author Comment

by:vadim63
ID: 17901036
SELECT     CAST(1 AS int) AS a FROM   aaa   works also
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17901061
Yes - I thought that you wanted bigint though? (although for a value off 1 you could also use tinyint).
0
 
LVL 10

Author Comment

by:vadim63
ID: 17901111
I need a bigint. And value "1" is for testing only.
0
 
LVL 10

Author Comment

by:vadim63
ID: 17901118
Ok, I guess, there's no solution for it. How should assign the points? Split it?
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17901143
There is a solution - use T-SQL to create the view, not the designer. You can still select from the view if it is created this way, can't you?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17901145
how do you run this query exactly?
0
 
LVL 10

Author Comment

by:vadim63
ID: 17901173
Yes, you right, it doesn't work in designer only. T-SQL does the thing.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 55
SQL FUll Text Not returning any results 2 17
SQL Server Question 5 29
Alter a column in sql 34 0
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now