Solved

Stupid problem with BigInt converting!!!

Posted on 2006-11-08
31
3,729 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 143

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 143

Expert Comment

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

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
 
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 143

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 143

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 40
Nested Case statement 4 37
Run an action on recently added records to a table 13 60
SQL create line numbers for data sampling 11 27
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 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