Solved

Stupid problem with BigInt converting!!!

Posted on 2006-11-08
31
3,695 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

831 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