Partial database copy - kind of replication

Hi,
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary :(.
Thanks:
PeterPeter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>

Partial database copy - kind of replication

Hi,
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary .
Thanks:
Peter
Peter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary .
> Thanks:
> Peter
>
|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary .
> Thanks:
> Peter
>
|||Unless the dataflow is in both directionns, and involves
autonomy or conflicts, I would recommend transactional
over merge, as it is significvantly faster and less
resource-intensive.
If you want a non-replication version, then
binary_checksums can be used although there is some
discussion as to whether these can always be relied upon
100%. Redgate do a DataCompare tool to do this type of
thing also.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Parent Child Query

I have a table and some same data.
entity_id | parent_entity_id | entity_name
-- -- --
4 0 Address Control
5 4 Address Line 1
6 4 Address Line 2
7 4 City
8 4 State
9 4 Zip Code
10 8 Maine
11 8 Massachusetts
12 8 New Hampshire
13 8 Rhode Island
14 8 Vermont
15 8 Connecticut
I'd like to pass in the entity_id, in this case 4 and get back all of the
children and grandchildren and I possibly down N, but would be happy with
grandchildren at this point as I dont know if deeper is needed.
If someone could point me to an article maybe or old post. I have looked
around some but see a lot of Oracle stuff.
Thanks"Brian" <brian@.nospam.com> wrote in message
news:uzKYuJqnFHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a table and some same data.
> entity_id | parent_entity_id | entity_name
> -- -- --
> 4 0 Address Control
> 5 4 Address Line 1
> 6 4 Address Line 2
> 7 4 City
> 8 4 State
> 9 4 Zip Code
> 10 8 Maine
> 11 8 Massachusetts
> 12 8 New Hampshire
> 13 8 Rhode Island
> 14 8 Vermont
> 15 8 Connecticut
> I'd like to pass in the entity_id, in this case 4 and get back all of the
> children and grandchildren and I possibly down N, but would be happy with
> grandchildren at this point as I dont know if deeper is needed.
> If someone could point me to an article maybe or old post. I have looked
> around some but see a lot of Oracle stuff.
> Thanks
>
Get Celko's books on Trees and Hierarchies. Other items of interest, you
can google for nested set theory.
Rick Sawtell|||The following example includes a function that can be used to find
descendants and ascendants of an item in a hierarchy.
http://milambda.blogspot.com/2005/0...or-monkeys.html
A vital element is missing from your model - prevention of circular
references.
ML

Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:

>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is no
t
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

Parse output from xp_cmdshell

I would like to write the output from (Exec master..xp_cmdshell 'dir
E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each line of
the output variable to see if (wildcard.BAK) exist. If (wildcard.BAK) exis
t
I append this code to my existing script.
Please help me create this script.
Thanks,
-- directory full backup listing
Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
Volume in drive E is NWORLBD01A Dumps
Volume Serial Number is DRV-73B6
NULL
Directory of E:\NewOrleans
NULL
05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
1 File(s) 168,968,064 bytes
0 Dir(s) 295,359,264 bytes free
NULLJoe K. wrote:
> I would like to write the output from (Exec master..xp_cmdshell 'dir
> E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each
> line of the output variable to see if (wildcard.BAK) exist. If
> (wildcard.BAK) exist I append this code to my existing script.
> Please help me create this script.
> Thanks,
> -- directory full backup listing
> Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
>
> Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
> Volume in drive E is NWORLBD01A Dumps
> Volume Serial Number is DRV-73B6
> NULL
> Directory of E:\NewOrleans
> NULL
> 05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
> 1 File(s) 168,968,064 bytes
> 0 Dir(s) 295,359,264 bytes free
> NULL
Create Table #output (output varchar(1000))
insert into #output
Exec master..xp_cmdshell 'dir C:'
Select * from #output
drop table #output
David Gugick
Imceda Software
www.imceda.com|||Look at this example:
create table #ipconfig(line varchar(2000))
insert into #ipconfig
execute xp_cmdshell 'ipconfig.exe'
select line from #ipconfig
drop table #ipconfig
YOu can "parse" that table with
Select * form #ipconfig where line like '%Something%'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
news:4D55228E-D4E2-4EA0-A533-1B4556C5232F@.microsoft.com...
> I would like to write the output from (Exec master..xp_cmdshell 'dir
> E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each line
> of
> the output variable to see if (wildcard.BAK) exist. If (wildcard.BAK)
> exist
> I append this code to my existing script.
> Please help me create this script.
> Thanks,
> -- directory full backup listing
> Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
>
> Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
> Volume in drive E is NWORLBD01A Dumps
> Volume Serial Number is DRV-73B6
> NULL
> Directory of E:\NewOrleans
> NULL
> 05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
> 1 File(s) 168,968,064 bytes
> 0 Dir(s) 295,359,264 bytes free
> NULL
>|||Create temp table and change my path to you path and it will give you
directory returned data in a table then you extract the peaces you need.
Create TABLE #temp
(
data nvarchar (1000)
)
Insert #temp
Exec master..xp_cmdshell 'dir \\server\backup\sql\FULL_Backup\*(FULL)*
.BKP'
select * from #temp
"Jens Sü?meyer" wrote:

> Look at this example:
> create table #ipconfig(line varchar(2000))
> insert into #ipconfig
> execute xp_cmdshell 'ipconfig.exe'
> select line from #ipconfig
> drop table #ipconfig
>
> YOu can "parse" that table with
> Select * form #ipconfig where line like '%Something%'
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:4D55228E-D4E2-4EA0-A533-1B4556C5232F@.microsoft.com...
>
>

Parse Column and Sort

Hi can anyone give your idea on how this can be implemented.
I have a table which has a column whose values looks like
Page 10 Line 1
Document
1
3
Sheet: Landscape
Sheet: Part 2
Page: 29 Line: 2
Page91
Page 10
Sheet: Sheet1
i need to sort by Page number and line number if no line number then page
number alone or if no page number then sort by sheet number else normal sort
Can anyone throw some light on itBoss
Why not do that on client?
CREATE TABLE #Test
(
col1 VARCHAR(15)
)
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END DESC
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> Hi can anyone give your idea on how this can be implemented.
> I have a table which has a column whose values looks like
> Page 10 Line 1
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Part 2
> Page: 29 Line: 2
> Page91
> Page 10
> Sheet: Sheet1
> i need to sort by Page number and line number if no line number then page
> number alone or if no page number then sort by sheet number else normal
sort
>
> Can anyone throw some light on it
>|||Can't you clean up the data and create a table where these attributes
are properly represented in atomic columns? Storing meaningful data in
this form is an extremely poor and inconvenient design. That's why it's
usual to scrub and transform external data before it is loaded into
tables.
If a redesign is impossible for you then take a look at CHARINDEX and
PATINDEX. You can extract the different string components into separate
calculated columns and then sort on those.
If you need more help, please post DDL (CREATE TABLE statement
including keys and constraints), some sample data (as INSERT
statements) and show your required end result.
David Portas
SQL Server MVP
--|||Hi Thanks for your reply
It dosent seem to return correct records
CREATE TABLE #Test
(
col1 VARCHAR(25)
)
-- drop table #test
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page: 29 Line: 1')
INSERT INTO #Test VALUES ('Page: 29 Line: 10')
INSERT INTO #Test VALUES ('Page: 2 Line: 18')
INSERT INTO #Test VALUES ('Page: 71 Line: 1')
INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page1')
INSERT INTO #Test VALUES ('Page 20')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Page 8')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
INSERT INTO #Test VALUES ('Sheet: Sheet21')
INSERT INTO #Test VALUES ('Sheet: Sheet10')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
The result is
Document
1
3
Sheet: Landscape
Sheet: Sheet1
Sheet: Sheet21
Sheet: Sheet10
Page: 201 Line: 1000 - this is in wrong order
Page1
Page 8
Page91
Page 10
Page 20
Page 10 Line 1
Page 10 Line 1
Page 10 Line 1
Page: 2 Line: 18 - this is in wrong order
Page: 29 Line: 1
Page: 29 Line: 2
Page: 71 Line: 1 - this is in wrong order
Page: 29 Line: 10
"Uri Dimant" wrote:

> Boss
> Why not do that on client?
> CREATE TABLE #Test
> (
> col1 VARCHAR(15)
> )
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END DESC
>
>
>
> "Boss" <Boss@.discussions.microsoft.com> wrote in message
> news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> sort
>
>|||SELECT col1 FROM
(
SELECT replace(col1,' ','')as col1 FROM #Test
) AS Der
ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
If it does not help ,post expected result, as I and David stated you will be
better off to do that on the client side, but befor that clean up the table
from redundant data
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:B175FA7A-14E9-4A57-A018-F7B48C805937@.microsoft.com...
> Hi Thanks for your reply
> It dosent seem to return correct records
> CREATE TABLE #Test
> (
> col1 VARCHAR(25)
> )
> -- drop table #test
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page: 29 Line: 1')
> INSERT INTO #Test VALUES ('Page: 29 Line: 10')
> INSERT INTO #Test VALUES ('Page: 2 Line: 18')
> INSERT INTO #Test VALUES ('Page: 71 Line: 1')
> INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page1')
> INSERT INTO #Test VALUES ('Page 20')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Page 8')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> INSERT INTO #Test VALUES ('Sheet: Sheet21')
> INSERT INTO #Test VALUES ('Sheet: Sheet10')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END ASC
> The result is
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Sheet1
> Sheet: Sheet21
> Sheet: Sheet10
> Page: 201 Line: 1000 - this is in wrong order
> Page1
> Page 8
> Page91
> Page 10
> Page 20
> Page 10 Line 1
> Page 10 Line 1
> Page 10 Line 1
> Page: 2 Line: 18 - this is in wrong order
> Page: 29 Line: 1
> Page: 29 Line: 2
> Page: 71 Line: 1 - this is in wrong order
> Page: 29 Line: 10
>
> "Uri Dimant" wrote:
>
page
normal

Parametric

Hello,

I'm using SQL 2005 and reporting services.

I have several parameters, the report show two parameters in the same line and I would like to present three parameters in the same line.

how can I do that ?

This is not natively supported.

The closest you can get is to write your own parameter frontend application and then e.g. use the RS report viewer controls to send the parameters to the report server and execute the report in "remote" mode. Other options include to send the parameters via URL-access from your custom frontend application.

-- Robert