Solved

Using Local variable Nvarchar(max)

Posted on 2013-01-15
11
609 Views
Last Modified: 2013-01-23
I created  a routine to dynamically create tables in my current database from the structure found in another database.  I originally created it on my local machine and it works perfectly.  Then I went to a customer's machine who is also using SQL Server 2008, but only has 4 gigs in memory, and had problems.  

when I set the local variable  to nvarchar(max)  or nvarchar(4000)
The create statement is truncated

When I set the local variable to nvarchar(2000)
the statement creates properly for selected tables that do not have many columns

Why is this happening?  

Second question:  I also moved this procedure to a machine that has sql 2005 installed and I ran the proc to see what problems I would get.  The code works fine for all tables except two where the table structure is longer than 4000 bytes.  
Is there a way to get around this problem?  
I tried showing the len(@vsSQL) and for tables that created properly some of the lengths were over 4000 bytes and for the two tables that have problems, the length is over 4000, but not as long as a few tables that created properly.  Obviously, I can't depend on this count.  
Here is the code:
   declare @vsSQL nvarchar(max)
      declare @execsql nvarchar(max)
      declare @vsInsertSQL nvarchar(max)
      declare @vsTableName varchar(50)
      declare @cprdbName varchar(100), @dbName varchar(100)
      declare @serverName varchar(100), @primComp int

      set @primComp = (Select PrimaryComp from InitialSetup)

      set @dbName = (Select DatabaseName from Specifics where SiteNO = @primComp)
      --  Step 1 set database to bulk logged
      Select @execsql = 'Alter Database ' + @dbName + '  Set recovery Bulk_Logged;' + CHAR(10)
      --Print @execSQL
      execute sp_executesql @execSQL
      

      --  Step 2 determine location of the Production server name
      set @cprdbName = (Select ProductionCPRLocation from Specifics where SiteNO = @primComp)
      set @serverName = (Select ProductionServerName from Specifics where SiteNO = @primComp)
      --  Step 3 if the linked server name does not exist, add it using dynamic name
      Select @execsql = 'if  not exists(Select srvname from master.dbo.sysservers where srvname = ''' + @serverName + ''')' + CHAR(10) +
                  'begin       exec sp_addlinkedserver @server=''' + @serverName + '''  end'
      --Print @execSQL
      execute sp_executesql @execSQL

      -- Step 4 Create Synonyms for objects int the production database name
      Select @execsql = 'Create synonym dbObjects for [' + @serverName + '].' + @cprdbName + '.sys.objects'
       --Print @execSQL
      execute sp_executesql @execSQL
      Select @execsql = 'Create synonym dbColumns for [' + @serverName + '].' + @cprdbName + '.sys.Columns'
      --Print @execSQL
      execute sp_executesql @execSQL
      Select @execsql = 'Create synonym dbType for [' + @serverName + '].' + @cprdbName + '.sys.Types'
      --Print @execSQL
      execute sp_executesql @execSQL
      Select @execsql = 'Create synonym dbIndexes for [' + @serverName + '].' + @cprdbName + '.sys.indexes'
      --Print @execSQL
      execute sp_executesql @execSQL
      Select @execsql = 'Create synonym dbIndexCol for [' + @serverName + '].' + @cprdbName + '.sys.index_columns'
      --Print @execSQL
      execute sp_executesql @execSQL

----------------------------------------------------------------------------------------------------------------

      -- Step 6 generate Create table statements from production database
      Declare TCursor2 Cursor for Select TableName from CopyTables
      Open TCursor2
      Fetch TCursor2 into @vsTableName
      while @@FETCH_STATUS = 0
      begin
            
        --  Step 2 generate Create table statements from CPR7 database
        
        select @vsSQL = 'CREATE TABLE ' + @vsTableName +  ' ('
      
            select @vsSQL = @vsSQL +  sc.Name + ' ' +
            st.Name +
             case when st.Name in ('varchar','nvarchar','char','nchar') then '(' + cast(sc.max_length as varchar) + ') '
                    when st.name in ('numeric', 'decimal') then  '(' + CAST(sc.precision as varchar) + ',' + CAST(sc.scale as varchar) + ') '
                    else ' ' end
                    +
            case when sc.is_nullable = 1 then 'NULL' else 'NOT NULL' end + ','
            from dbObjects so
            join dbColumns sc on sc.object_id = so.object_id
            join dbType st on st.user_type_id = sc.user_type_id
             
            where so.name = @vsTableName
            order by  sc.column_id
             
             --    add Primary key constraint
             Select   @vsSQL = @vsSQL + 'Constraint [' + si.name + '2'  + '] PRIMARY KEY CLUSTERED ( '
             from dbObjects so
             join dbIndexes si
             on so.object_id = si.object_id and si.type_desc = 'CLUSTERED'
             where so.name = @vsTableName
             
            --  add Primary key columns
            Select   @vsSQL = @vsSQL + sc.name + case when sic.is_descending_key = 0 then ' ASC,  ' else ' DESC,  '  end
            from dbObjects so
             join dbIndexes si
             on so.object_id = si.object_id  and si.type_desc = 'CLUSTERED'
             join dbColumns sc
             on sc.object_id = so.object_id
             join dbIndexCol sic
             on sc.object_id = sic.object_id and sc.column_id = index_column_id and si.index_id = sic.index_id
             where so.name = @vsTableName
             order by key_ordinal



            select @execSQL = substring(@vsSQL,1,len(@vsSQL) - 1)  + '))'  
            Select @vsInsertSQL = Len(@vsSQL)
            Print @vsInsertSQL
            Print @execSQL
        
        execute sp_executesql @execSQL
          
        fetch next from TCursor2 into  @vsTableName  
      end
      Close TCursor2
      Deallocate TCursor2


------------------------------------------------------------------------------------------
      -- Step 7 Insert all rows from Productions version into cPR_Reporting

      Declare TCursor3 Cursor for Select TableName from CopyTables where tablename not in ('LineItemLinks', 'LineItemsAssigned')
      Open TCursor3
      Fetch TCursor3 into @vsTableName
      while @@FETCH_STATUS = 0
      begin
        -- Step 3 Insert all rows from Productions version into cPR_Reporting
            select @vsInsertSQL = 'INSERT INTO ' + @vsTableName  + ' (' + char(10)
      
            select @vsInsertSQL = @vsInsertSQL + ' ' + sc.Name + ','
             from dbObjects so
             join dbColumns sc on sc.object_id = so.object_id
             join dbType st on st.user_type_id = sc.user_type_id
             where so.name = @vsTableName
             order by
             sc.column_id

            select @vsInsertSQL = substring(@vsInsertSQL,1,len(@vsInsertSQL) - 1)  + ')' + char(10) + 'Select '
            select @vsInsertSQL = @vsInsertSQL + ' ' + sc.Name + ','
             from dbObjects so
             join dbColumns sc on sc.object_id = so.object_id
             join dbType st on st.user_type_id = sc.user_type_id
             where so.name = @vsTableName
             order by
             sc.column_id
             
             
             select @execSQL = substring(@vsInsertSQL,1,len(@vsInsertSQL) - 1) + char(10) + 'from [' +
                    @ServerName + '].' + @cprdbName + '.dbo.' + @vsTableName + CHAR(10)
             Print @execSQL
             execute sp_executesql @execSQL
        
        fetch next from TCursor3 into  @vsTableName  
      end
      Close TCursor3
      Deallocate TCursor3

      -- Step 8 drop synonyms
      DROP SYNONYM dbObjects
      Drop Synonym dbColumns
      Drop synonym dbType
      Drop synonym dbIndexes
      Drop synonym dbIndexCol
      
      -- Step 9 Alter database
      Select @execsql = 'Alter Database ' + @dbName + '  Set recovery simple;' + CHAR(10)
      -- Print @execSQL
      execute sp_executesql @execSQL


Any thoughts would be greatly appreciated.
0
Comment
Question by:LenTompkins
  • 7
  • 4
11 Comments
 
LVL 8

Expert Comment

by:venk_r
ID: 38781269
Are you selecting it, or executing it somewhere, or what?

SQL Server Management Studio defaults to only presenting a small amount of text that you select all at once. If you want to increase it, it's in Tools - Options - Query Results. It'll display more, up to 65,535 characters, if you tell it to.

Is that what you're running into?
0
 

Author Comment

by:LenTompkins
ID: 38782496
No,  If I have the local variable set to nvarchar(2000)
the create statement is perfect:
CREATE TABLE ADJTYPE (CPK_ADJTYPE int NOT NULL,TEXT_ varchar(20) NULL,CODE varchar(5) NULL,GLACCT varchar(20) NULL,GLDEPT varchar(10) NULL,DELFLAG int NOT NULL,TOUCHDATE datetime NULL,CHGBYHOST varchar(100) NULL,CREATEDON datetime NULL,CREATEDBY varchar(100) NULL,Constraint [CN__ADJTYPE_CPK_ADJTYPE2] PRIMARY KEY CLUSTERED ( CPK_ADJTYPE ASC))

if I have the local variable set to nvarchar(max)
the create statement is:
CREATE TABLE ADJTYPE (CREATEDBY varchar(100) NULL,Constraint [CN__ADJTYPE_CPK_ADJTYPE2] PRIMARY KEY CLUSTERED ( CPK_ADJTYPE ASC))

is there a setting in this version of SQL server that needs to be changed?
I also want you to know that the database is set to a compatability of sql 2005 even though the machine is SQL 2008.
0
 
LVL 8

Expert Comment

by:venk_r
ID: 38782623
Can you tell me the value by going to
SQL Server Management Studio Tools - Options - Query Results-SQL Server-Results to Text-Maximum number of characters displayed in each column ?
0
 

Author Comment

by:LenTompkins
ID: 38782862
The maximum number of characters displayed in each column is 256
0
 

Author Comment

by:LenTompkins
ID: 38782884
I changed it to 1000 and it had no effect on the query it generated.  It was still truncated.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:venk_r
ID: 38783030
I think there is some limitation with print command w.r.t nvarchar datatype

Some potential workarounds, but they might not fit your situation:

•add some logic to test the length of the variable and split into manageable chunks of 8000 or less varchars, and PRINT each one

•set 'Results to Text' and use select @x intead of using print command
0
 

Author Comment

by:LenTompkins
ID: 38783133
I looked at the Results to Text and it was changed to 8192.  Then I ran the query with Select @vsSQL instead of Print @vsSQL and I set the local variables to nvarchar(3900) and it runs properly.  
I changed the local variable to nvarchar(4000 )and the results are incorrect.
we are missing a number of columns.
CREATE TABLE ADJTYPE (CREATEDBY varchar(100) NULL,Constraint [CN__ADJTYPE_CPK_ADJTYPE2] PRIMARY KEY CLUSTERED ( CPK_ADJTYPE ASC))
0
 
LVL 8

Assisted Solution

by:venk_r
venk_r earned 500 total points
ID: 38785196
The datatype is not yet nvarchar(max) until assignment to @vsSQL
try intiating like below

set @vsSQL =''
set vsSQL =vsSQL + 'Create Table.....'
0
 

Author Comment

by:LenTompkins
ID: 38787334
Thank you for your assistance.  I tried setting all of the local variables before using any of them, and it didn't change the results.  I am thinking that I will change the way I create the statement.  I will set the size to 3900 and then determine how many rows are in the table.  If the number of rows exceeds a number I have yet to determine, I can generate the create statement for the first X columns to one local variable set to 3900 and then select the additional columns and the primary key to another local variable and then concatenate and execute them.  
If you have a better idea, I'd be glad to hear it.
0
 

Accepted Solution

by:
LenTompkins earned 0 total points
ID: 38794171
I solved the problem by creating two local variables of length 3900 and moved the first 150 columns into the first variable and the rest of the columns into the second variable.  I always add the primary key statements to the second variable and then I couldn't use:
execute sp_executesql  but used exec (@vsSql + @vsSQL1) and it worked for all tables.
0
 

Author Closing Comment

by:LenTompkins
ID: 38809145
I appreciate your continued response to my  problem
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

10 Experts available now in Live!

Get 1:1 Help Now