Skip to main content
Dynamics 365 Community / Forums / Finance forum / Error converting varch...
Finance forum
Unanswered

Error converting varchar to Bigint TSQL

editSubscribe (1) ShareShare
ReportReport
Posted on by 2,368
Hi Folks,
 
Can you please fix below query to work, I keep getting error converting data type varchar to big int when trying to run via runclass/Job.
declare @dbo varchar(50) set @dbo = 'Dbo.seq_' declare @tableID varchar(10) set @tableID = 'SOMETABLEID' declare @sql varchar(500) set @sql = 'alter sequence' +@dbo+@tableID + 'minvalue' +CAST(YOURRECIDValue as bigint) +'restart with'  +CAST(YOURRECIDValue as bigint) exec (@sql)
 
 
Thanks
Mav.
  • Andre Arnaud de Calavon Profile Picture
    Andre Arnaud de Cal... 283,068 Super User on at
    Error converting varchar to Bigint TSQL
    Hi Mav,
     
    What exact business issue are you trying to solve? What should be the result of the query? Do you want to read data or write data to a table?
  • Community member Profile Picture
    Community member 2,368 on at
    Error converting varchar to Bigint TSQL
    Hi Andre,
     
    Yes I have the X++ code which generates the statement. All other statements with same x++ code works just fine.
    It is only this code which gives the error and even when I run the code directly in sql I get same error . 
    Error in SQL 
    Shared in previous post 

    SQL query
    Shared in previous port

    X++ code
    Shared below.




    Error in X++ 
    [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type varchar to bigint.




     public static int64  alterRecId(TableId _tableId, int64 _maxRecId)
        {
            //Run SQL to alter seq id
            //alter sequence dbo.seq_7051 minvalue alterRecId restart with alterRecId
            int64 alterRecId = _maxRecId+1;
            const str sqlStatementText = "declare @dbo varchar(50) set @dbo = 'Dbo.seq_' declare @tableID varchar(10)  set @tableID = @SelectedTableId declare @sql varchar(500) set @sql = 'alter sequence ' +@dbo+@tableID + 'minvalue ' + @AlterRecId + 'restart with'  + @AlterRecId exec (@sql)";
            var connection = new Connection();
            Statement statement = connection.createStatement();
            Map paramMap = SqlParams::create();
            paramMap.add('SelectedTableId', _tableId);
            paramMap.add('AlterRecId', alterRecId);
            var results= statement.executeQueryWithParameters(sqlStatementText, paramMap);
            results.next();
            str result1 = results.getString(1);
            alterRecId = str2Int64(result1);
            statement.close();
            return alterRecId;
        }
    Thanks
    Mav
  • Andre Arnaud de Calavon Profile Picture
    Andre Arnaud de Cal... 283,068 Super User on at
    Error converting varchar to Bigint TSQL
    Hi Mav,
     
    Can you explain if you have X++ coding that generates this SQL command? What is this SQL about? Where exactly do you get the error? 

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,068 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,591 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans