I am in the middle of a DS migration between two different hardware platforms. One of the requirements was to migrate the numerous custom DS console filters between the two servers. Since I didnt see anything in the KB or here, I wrote up the SQL on how I moved the filters between the servers.
You dont need to use BCP to create the CSVs, it was more for the fun of it
. If you read the comments between the SQL, you'll see how the processes were broken down.
Code:
/*
Activate xp_cmdshell for calling external program bcp.exe
*/
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
/*
Export known filters to CSV file. Notice that the express database prefix has two periods
*/
DECLARE @sql VARCHAR(8000)
SELECT @sql = 'bcp express..filter out c:\filter.csv -c -t, -T -S' + @@servername
EXEC xp_cmdshell @sql
/*
Export known filter conditions to CSV file. ##Filtertemp is a global temporary table name to be
exposed for use by bcp.exe. Temp table is dropped.
*/
SELECT
fc.filter_id
,fc.item_seq
,fc.field_type
,fc.oper_type
,fc.key_value
,fc.drive
,fc.conjunct
INTO ##Filtertemp
FROM express..filter f
JOIN express..filter_condition fc
ON f.filter_id = fc.filter_id
WHERE fc.key_value != ''
DECLARE @sql VARCHAR(8000)
SELECT @sql = 'bcp ##Filtertemp out c:\filtercon.csv -c -t, -T -S' + @@servername
EXEC xp_cmdshell @sql
DROP TABLE ##Filtertemp
/*
I removed the canned DS Console filters manually, then copied the CSV files local to the
new RDP server with a local SQL instance
*/
/*
Bulk import the new filters
*/
BULK
INSERT filter
FROM 'c:\temp\filter.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
/*
Bulk import the new filter conditions
*/
BULK
INSERT filter_condition
FROM 'c:\temp\filtercon.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
Linear Mode
