Solved

Stupid problem with BigInt converting!!!

Posted on 2006-11-08
31
3,580 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

705 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

21 Experts available now in Live!

Get 1:1 Help Now