HOM: MS SQL Server 2000 -> MS SQL Server 2005 [sp sap_use_var_MAX]

[Former Member]

Hi guys,

Procedure

Homogeneous System Copy on SQL Server

Source Platform

Windows 2003 Server x86

SQL Server 2000 SP4

SQL_Latin1_General_CP850_BIN2

SAP R/3 4.7 x200

SAP Kernel 6.40 Patch 347 x86 (Sep 10 2010)

SAP_BASIS 620 Patch 69

Target Platform

Windows 2008 Server x64

SQL Server 2005 SP3

SQL_Latin1_General_CP850_BIN2

SAP R/3 4.7 x200

SAP Kernel 6.40 Patch 347 x64 (Sep 10 2010)

SAP_BASIS 620 Patch 69

Symptom

When running STM (SAP Tools for SQL Server) on the target server I get the following error:

- Errors when executing sql command: (Microsoft)(ODBC SQL Server Driver)(SQL Server)Could not find stored procedure u2018sap_use_var_MAXu2019.

Further Analysis

I'm able to start the SAP system.

Tx SICK returns the following:

- Wrong long datatypes. Perform SQL Server after upgrade steps. Please see note 126973

Troubleshooting

Note 126973 - SICK messages with MS SQL Server

Solution:

Proceed as described in Note 1291861

Note 1291861 - SICK message: Wrong long datatypes

2. If the problem occurred following a system copy from SQL Server 2000 to SQL Server 2005 or later then execute the following statements:

setuser 'sid'

exec sap_use_var_MAX

Where 'sid' is the SAPSID of your system in lower case.

setuser 'dev'

exec sap_use_var_MAX

Msg 2812, Level 16, State 62, Line 2

Could not find stored procedure 'sap_use_var_MAX'.

Request

I'm thinking if any one of you is able to access an SAP system on SQL Server (2005 or other) with the above mentioned stored procedure present you could scipt it to a txt file and post it here in order for me to create it manually on my system.

I've checked several SAP Notes on this subject and none of them explains how to create this store procedure from scratch, they all just assume it's already there and tell you to execute it.

Thank you.

Bruno Pereira

[Former Member]

I was able to solve this issue just now the following way:

- A friend of mine scripted it to a file which I then used to create the sap_use_var_MAX on the target database.

Here is a copy of that file:

USE [<SID>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [<sid>].[sap_use_var_MAX] as
begin
  declare @tabname sysname
  declare @colname sysname
  declare @datatype sysname
  declare @nullflag nvarchar(1)
  declare @cmd nvarchar(1024)
  declare @n_altered int
  declare c cursor for
    select t.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,
              nullflag = substring(c.IS_NULLABLE,1,1)
      from INFORMATION_SCHEMA.COLUMNS c,
           INFORMATION_SCHEMA.TABLES t
      where c.TABLE_NAME = t.TABLE_NAME AND
            c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
            t.TABLE_TYPE like '%TABLE%' AND
            c.TABLE_SCHEMA = schema_name() AND
            c.DATA_TYPE IN ('text','ntext','image')
      order by t.TABLE_NAME,c.COLUMN_NAME
  open c
  set @n_altered = 0
  fetch next from c into @tabname,@colname,@datatype,@nullflag
  while (@@fetch_status <> -1)
  begin
    if (@@fetch_status <> -2)
    begin
      set @cmd = N'alter table [' + @tabname +
                  N'] alter column [' + @colname +
                  N'] '
      if @datatype = N'text'
        set @cmd = @cmd + N'varchar(MAX)'
      else if @datatype = N'ntext'
        set @cmd = @cmd + N'nvarchar(MAX)'
      else
        set @cmd = @cmd + N'varbinary(MAX)'
      if @nullflag = N'N'
          set @cmd = @cmd + ' NOT NULL'
      else
          set @cmd = @cmd + ' NULL'
      -- print @cmd
      exec( @cmd )
      set @n_altered = @n_altered + 1
    end
    fetch next from c into @tabname,@colname,@datatype,@nullflag
  end
  close c
  deallocate c
  select convert(varchar,@n_altered) + N' columns were altered'
end -- sap_use_var_MAX
 

Mind you, you'll have to change <SID> and <sid> acoordingly, considering also if your db is dbo schema or sid schema owned!

Thank you for your help nonetheless!

Bruno Pereira

[sebastian_dusch]

Hi,

are you using the latest STM?

Best,

Sebastian

[Former Member]

Yes,

I just got them from:

https://websmp210.sap-ag.de/msplatforms

SQL Server

Title..................................................Changed..........Size (KB)

SAP Tools for MS SQL Server.........10.06.2010.......170308