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 limitation with sp_executesql on SQL Server, since you cannot use longer SQL strings than 4000 characters. If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in
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_031508'
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 FOR
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 FOR
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 + ''''' TO N'''''
+ @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 [MinIPToGSK1]
([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 dimension20.dbo.MinIPToGSK1"
queryout ';set @SQLStatement = @SQLStatement + @folderOut + '\IP2GSK.txt -T -t "," -c''
exec xp_cmdshell @BCPStatement
set @BCPStatement = ''bcp "select GeographicSK,ContinentName,CountryCode,StateProvinceName
from dimension20.dbo.IPGSKtogeo1" queryout ';set @SQLStatement = @SQLStatement + @folderOut + '\IPGSK2Geo.txt -T -t "," -c''
exec xp_cmdshell @BCPStatement
';
print @SQLStatement
