What if your dynamic SQL statement is too long?

// From http://www.sommarskog.se/dynamic_ SQL .html#sp_executesqlong
// Not tested yet.
There is a limitationSp_executesqlOnSQLServer, since you cannot use longerSQLStrings than 4000 characters. If you want to useSp_executesqlDespite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrapSp_executesqlIn Exec () :

 
Declare @ sql1 nvarchar (4000), @ sql2 nvarchar (4000), @ State char (2) Select @ State = 'CA' select @ sql1 = n' select count (*) 'select @ sql2 = n' from DBO. authors where State = @ state' exec ('exec sp_executesql n''' + @ sql1 + @ sql2 + ''', n' @ State char (2 )'', @ State = ''' + @ State + '''')
 

I spend a whold day to write a script today. Since I am not a experienced script writer, I will
Paste it here to remind me of something maybe useful in future:

 
Set
 
Nocount on
 
Declare
 
@ Folderin varchar (500)
 
Declare
 
@ Folderout varchar (500)
 
Declare
 
@ Iptablename varchar (100)
 
Set
 
@ Folderin = '$ (folderin )'
 
Set
 
@ Folderout = '$ (folderout )'
 
Set
 
@ Iptablename = '$ (iptablename )'
 
-- Set @ folderin = 'G: \ v-yukxin \ litespeedfiles'
 
 
-- Set @ folderout = 'G: \ v-yukxin \ ipquova'
 
 
-- Set @ iptablename = 'IP _ 000000'
 
 
Declare
 
@ Backfiles as table
 
 
(Fname varchar (255 ));
Declare
 
@ Sqlstatement varchar (max)
 
Declare
 
@ Tempstatement nvarchar (4000)
 
Declare
 
@ Sourcefiles varchar (8000)
 
Set
 
@ Tempstatement = stuff ('dir \ *. Bak */B ', 5, 0, @ folderin );
 
Insert
 
Into @ backfiles
 
Exec
 
Xp_cmdshell @ tempstatement
 
 
 
 
Declare
 
Filecursor cursor
 
Select
 
Fname from @ backfiles
 
Where
 
Fname is not null;
 
 
 
 
Declare
 
@ Backupfile varchar (500 );
 
Open
 
Filecursor
 
Fetch
 
Next from filecursor into @ backupfile
 
Set
 
@ Sqlstatement = 'exec master. DBO. xp_restore_database @ database = ''dimension20 '','
Set
 
@ Sourcefiles =''
 
While
 
@ Fetch_status = 0
 
Begin
 
 
 
Set @ sqlstatement = @ sqlstatement + '@ filename = n''' + @ folderin +' \ '+ @ backupfile + ''','
 
 
Set @ sourcefiles = @ sourcefiles + '@ filename = n''' + @ folderin +' \ '+ @ backupfile + ''','
 
 
Fetch next from filecursor into @ backupfile
 
End
 
 
Close
 
Filecursor;
 
Deallocate
 
Filecursor;
 
Set
 
@ Sqlstatement = @ sqlstatement + '@ filenumber = 1, @ with = n'''recovery '',
@ With = n'''nounload'', @ with = n' stats = 10 '', @ with = n' replace '','
 
Set
 
@ Sourcefiles = substring (@ sourcefiles, 1, Len (@ sourcefiles)-1)
 
Set
 
@ Tempstatement = 'exec master. DBO. xp_restore_filelistonly' + @ sourcefiles
 
Declare
 
@ Dbinfo as table
 
(
 
 
Logicalname
 
Varchar (max ),
 
Physicalname
 
Varchar (max ),
 
[Type]
 
Varchar (max ),
 
Filegroupname
 
Varchar (max) null,
 
[Size]
 
Bigint,
 
[Maxsize]
 
Bigint
 
);
 
 
Insert
 
Into @ dbinfo exec sp_executesql @ tempstatement
 
Declare
 
Dbinfocursor cursor
 
Select
 
Logicalname, physicalname from @ dbinfo
 
Declare
 
@ Lname varchar (max)
 
Declare
 
@ Pname varchar (max)
 
Open
 
Dbinfocursor
 
Fetch
 
Next from dbinfocursor into @ lname, @ pname
 
While
 
@ Fetch_status = 0
 
Begin
 
 
 
Set @ pname = reverse (@ pname)
 
 
Set @ pname = left (@ pname, patindex ('% \ %', @ pname)-1)
 
 
Set @ pname = reverse (@ pname)
 
 
 
 
 
Set @ sqlstatement = @ sqlstatement + '@ with = ''move n ''' + @ lname + ''' '''''
 
 
+ @ Folderout + '\' + @ pname + ''''''','
 
 
Fetch next from dbinfocursor into @ lname, @ pname
 
End
 
 
Set
 
@ Sqlstatement = substring (@ sqlstatement, 1, Len (@ sqlstatement)-1 );
 
Close
 
Dbinfocursor;
 
Deallocate
 
Dbinfocursor;
 
Set
 
@ Sqlstatement = @ sqlstatement +
'
 
 
Go
 
 
Alter database dimension20 set recovery simple;
 
 
Use [dimension20];
 
 
/*
 
 
Step 1
 
 
Create new IP table.
 
 
We need to change the null columns into ''' or-1, which will make the join much faster.
 
 
*/
 
 
-- Drop table [IP1]
 
 
Create Table [DBO]. [IP1] (
 
 
[MinIP] [bigint] Null,
 
 
[Continent] [varchar] (255) Collate SQL _latin1_general_cp1_ci_as null,
 
 
[Countryiso] [varchar] (2) Collate SQL _latin1_general_cp1_ci_as null,
 
 
[State] [varchar] (100) Collate SQL _latin1_general_cp1_ci_as null,
 
 
[Regioncf] [smallint] Null,
 
 
[Statecf] [smallint] Null,
 
 
[Citycf] [smallint] Null,
 
 
[Zipcf] [smallint] Null,
 
[Areacodecf] [smallint] Null
 
 
) On [primary]
 
 
Insert [DBO]. [IP1]
 
 
(MinIP, continent, countryiso,
 
 
State, regioncf, statecf,
 
 
Citycf, zipcf, areacodecf)
 
 
Select
 
 
MinIP, isnull (continent, ''') as continent, isnull (countryiso, ''') as countryiso,
 
 
Isnull (State, ''') As State, regioncf, statecf,
 
 
Citycf, zipcf, areacodecf
 
 
From'
 
;
 
Set
 
@ Sqlstatement = @ sqlstatement + @ iptablename;
 
Set
 
@ Sqlstatement = @ sqlstatement + ';
 
Update [IP1] Set regioncf =-1 where regioncf is null
 
 
Update [IP1] Set statecf =-1 where statecf is null
 
 
Update [IP1] Set citycf =-1 where citycf is null
 
 
Update [IP1] Set zipcf =-1 where zipcf is null
 
Update [IP1] Set areacodecf =-1 where areacodecf is null
 
 
/*
 
 
Step 2
 
 
Generate the location table.
 
 
*/
 
 
-- Drop table [ipgsktogeo1]
 
 
Create Table [DBO]. [ipgsktogeo1] (
 
 
[Geographicsk] [int] identity (1, 1) not null,
 
 
[Continentname] [nvarchar] (256) Collate SQL _latin1_general_cp1_ci_as not null,
 
 
[Countrycode] [varchar] (2) Collate SQL _latin1_general_cp1_ci_as not null,
 
 
[Stateprovincename] [nvarchar] (256) Collate SQL _latin1_general_cp1_ci_as null,
 
 
[Regioncf] [int] Null,
 
 
[Citycf] [int] Null,
 
 
[Zipcf] [int] Null,
 
 
[Areacodecf] [int] Null,
 
 
[Createddatetime] [datetime] not null constraint [df_ipgsktogeo_createddatetime]
Default (getutcdate ()),
 
[Modifieddatetime] [datetime] Null,
 
 
Constraint [ipgsktogeo1_pk] primary key clustered
 
 
(
 
 
[Geographicsk] ASC
 
 
) With (ignore_dup_key = OFF) on [primary]
 
 
) On [primary]
 
 
 
 
 
Insert [DBO]. [ipgsktogeo1]
 
 
(Continentname, countrycode, stateprovincename, regioncf, citycf, zipcf, areacodecf,
Createddatetime, modifieddatetime)
 
 
Select
 
 
Continent as continentname,
 
 
Countryiso as countrycode,
 
 
State as stateprovincename,
 
 
Regioncf,
 
 
Citycf,
 
 
Zipcf,
 
 
Areacodecf,
 
 
Getdate () as createddatetime,
 
 
Getdate () as modifieddatetime
 
 
From IP1
 
Group by continent, countryiso, state, regioncf, citycf, zipcf, areacodecf
 
 
/*
 
 
Step 3
 
 
Set up the map between MinIP and location table.
 
 
*/
 
 
Create Table [DBO]. [miniptogsk1] (
 
 
[MinIP] [bigint] not null,
 
 
[Geographicsk] [int] Null,
 
 
[Createddatetime] [datetime] not null constraint [df_miniptogs_createddatetime]
Default (getutcdate ()),
 
 
[Modifieddatetime] [datetime] Null
 
 
) On [primary]
 
 
Insert [miniptoactive1]
 
 
([MinIP], [geographicsk], [createddatetime], [modifieddatetime])
 
 
Select IP. MinIP, Loc. [geographicsk], Loc. createddatetime, Loc. modifieddatetime
 
 
From IP1 as IP
 
 
Inner join ipgsktogeo1 Loc
 
 
On Ip. continent = LOC. continentname
 
And IP. countryiso = LOC. countrycode
 
 
And IP. State = LOC. stateprovincename
 
 
And IP. regioncf = LOC. regioncf
 
 
And IP. citycf = LOC. citycf
 
 
And IP. zipcf = LOC. zipcf
 
 
And IP. areacodecf = LOC. areacodecf
 
 
/*
 
 
Step 4
 
 
BCP out the data to files
 
 
*/
 
 
Declare @ bcpstatement varchar (8000 );
 
 
Set @ bcpstatement = ''BCP" select MinIP, geographicsk from dimensionmongodbo. miniptogsk1"
Queryout'
 
;
 
Set
 
@ Sqlstatement = @ sqlstatement + @ folderout + '\ ip2gsk.txt-T-t ","-C''
 
Exec xp_cmdshell @ bcpstatement
 
 
Set @ bcpstatement = ''BCP" select geographicsk, continentname, countrycode, stateprovincename
From dimensionmongodbo. ipgsktogeo1 "queryout'
 
;
 
Set
 
@ Sqlstatement = @ sqlstatement + @ folderout + '\ ipgsk2geo.txt-T-t ","-C''
 
Exec xp_cmdshell @ bcpstatement
 
 
'
 
;
 
Print
 
@ Sqlstatement