// 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 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 ONdeclare
@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 @backFilesEXEC
xp_cmdshell @TempStatement
DECLARE
FileCursor CURSOR FORSELECT
FName FROM @backFileswhere
FName is not null;
DECLARE
@BackUpFile Varchar ( 500 );OPEN
FileCursorFETCH
NEXT FROM FileCursor INTO @BackUpFileSET
@SQLStatement = 'exec master.dbo.xp_restore_database @database = ''dimension20'','SET
@SourceFiles = ''WHILE
@@FETCH_STATUS = 0BEGIN
SET @SQLStatement = @SQLStatement + '@filename = N''' + @folderIn + '\' + @BackUpFile + ''', ' SET @SourceFiles = @SourceFiles + '@filename = N''' + @folderIn + '\' + @BackUpFile + ''', ' FETCH NEXT FROM FileCursor INTO @BackUpFileEND
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 ' + @SourceFilesdeclare
@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 @TempStatementDECLARE
DbInfoCursor CURSOR FORSELECT
LogicalName , PhysicalName FROM @dbInfodeclare
@lname varchar ( max )declare
@pname varchar ( max )open
DbInfoCursorfetch
next from DbInfoCursor into @lname , @pnamewhile
@@FETCH_STATUS = 0begin
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
'
;