Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stupid problem with BigInt converting!!!

Posted on 2006-11-08
31
Medium Priority
?
3,952 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

722 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