Check Fort Nocs Networks I know for sure they allow remote connections, but I have no idea about pricing.

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

SET @MenuSeed = (Select Top 1 MenuOrder From Pages Order By MenuOrder DESC )

OR

Select Top 1 @MenuSeed = MenuOrder From Pages Order By MenuOrder DESC

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Are you still having troubles with this?

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Is this what you are looking for ?

SELECT name, compatibility_level FROM sys.databases

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

I don't know why you people are so scared of XML :)

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

You're welcome :) So... what's the next project about?

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Well... Let's just hope he is not using the whole alphabet for the Symbol field :)

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Yes, it is possible :)

CREATE TABLE #T (ID varchar(2), Symbol varchar(1));
INSERT INTO #T(ID, Symbol) SELECT '01', 'A'
INSERT INTO #T(ID, Symbol) SELECT '01', 'B'
INSERT INTO #T(ID, Symbol) SELECT '01', 'C'
INSERT INTO #T(ID, Symbol) SELECT '02', 'B'
INSERT INTO #T(ID, Symbol) SELECT '02', 'D'

SELECT
ID,
STUFF(
(SELECT ' and ' + Symbol FROM #T WHERE ID = t.ID FOR XML PATH(''))
, 1
, 5
, ''
) AS Symbols
FROM #T AS t
GROUP BY ID

DROP TABLE #T

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

SELECT @XMLDOC.value('count(/DATAMATRIX/FSDATAMATRIX[FIELD1=sql:variable("@XCOUNTRY")])','int')

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

SET @XMLDOC.modify('
replace value of (/DATAMATRIX/FSDATAMATRIX/FIELD2[../FIELD1="BELGIUM"]/text())[1]
with sql:variable("@XMAXPPM")
')

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) WITH ( DM_OBJECT_TYPE VARCHAR(100) 'DM_OBJECT_TYPE' , FIELD1 VARCHAR(100) 'FIELD1' , FIELD2 VARCHAR(100) 'FIELD2' , FIELD3 VARCHAR(100) 'FIELD3' , FIELD4 VARCHAR(100) 'FIELD4' , FIELD5 VARCHAR(100) 'FIELD5' )

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

David Mujica wrote:

If you really only have 20 or so records

If you have 20 records and you only need 1, why load other 19 records if you know that you'll throw them away anyway ? What if you have 10000 users loading extra 19 records everytime ?

David Mujica wrote:

save yourself lots of headaches with complex SQL

I cannot agree with this ... I prefer the headaches :)

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

This is what I would use in SQL 2000 :

/* TEST DATA */
/****************************/
CREATE TABLE #T (id INT IDENTITY(1,1), aField NVARCHAR(10) ) ;
INSERT INTO #T (aField) SELECT 'one' ;
INSERT INTO #T (aField) SELECT 'two' ;
INSERT INTO #T (aField) SELECT 'three' ;
INSERT INTO #T (aField) SELECT 'four' ;
INSERT INTO #T (aField) SELECT 'five' ;
INSERT INTO #T (aField) SELECT 'six' ;
INSERT INTO #T (aField) SELECT 'seven' ;

/* TEST LIST */
/****************************/
SELECT IDENTITY(INT, 1 , 1) AS RowNo, 0+id AS id, aField
INTO #Z
FROM #T
ORDER BY aField DESC

/* SORTED LIST */
/****************************/
SELECT * FROM #Z

/* EXTRACT FROM SORTED LIST */
/****************************/
SELECT * FROM #Z WHERE RowNo IN ( 2 , 4 ) ;
/* */
DROP TABLE #T ;
DROP TABLE #Z ;

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Well ... if you are using SQL 2005 ... I think this is what you are looking for :)

/* TEST DATA */
/****************************/
CREATE TABLE #T (id INT IDENTITY(1,1), aField NVARCHAR(10) ) ;
INSERT INTO #T (aField) SELECT 'one' ;
INSERT INTO #T (aField) SELECT 'two' ;
INSERT INTO #T (aField) SELECT 'three' ;
INSERT INTO #T (aField) SELECT 'four' ;
INSERT INTO #T (aField) SELECT 'five' ;
INSERT INTO #T (aField) SELECT 'six' ;
INSERT INTO #T (aField) SELECT 'seven' ;

/* SORTED LIST */
/****************************/
SELECT * FROM #T ORDER BY aField DESC

/* EXTRACT FROM SORTED LIST */
/****************************/
;WITH myTable AS (
SELECT
ROW_NUMBER() OVER( ORDER BY aField DESC ) AS RowNo
, id
, aField
FROM #T
)
SELECT *
FROM myTable
WHERE RowNo IN ( 2 , 4 ) ;
/****************************/

DROP TABLE #T ;

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

modified on Friday, July 25, 2008 8:38 AM

Member 3084160 wrote:

What do you think is this a coding horror?

YEP! :)

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

I wonder why everyone runs away from XML... :) Let me know if you find a more elegant way. Maybe you have also time to run some performance tests ;)

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

mcmilan wrote:

finally I want to hide the buttons when I click the print option.

That's a good idea!

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

( You are using SQL2005, right? :) ) Try this:

;WITH RecipientsByMail (MailId, RecipientList) AS (
select mx.Id AS MailId ,
( select AddressTable.Name+','
from MailTable
join RecipientTable on RecipientTable.MailTableId = MailTable.Id
join AddressTable on AddressTable.Id = RecipientTable.AddressTableId
WHERE MailTable.Id = mx.Id
FOR XML PATH('')
) AS RecipientList
from MailTable as mx
)
select m.Id, m.Created, m.Subject, sender.Name, r.RecipientList
from MailTable as m
join SenderTable as s on s.MailTableId = m.Id
join AddressTable as sender on sender.Id = s.AddressTableId
join RecipientsByMail as r on r.MailId = m.Id ;

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

As there is no *DATE* data type in SQL 2005, you cannot store a date in the form that you want. The only options you have to store a date (and time) are DATETIME and SMALLDATETIME (see Data Types[^]) If you need only the day part of a date I suggest you to use SMALLDATETIME ...

DECLARE @d DECIMAL(15,6) ;SET @d = 20080721.150825

SELECT
@d AS [as DECIMAL]
, CAST( LEFT(@d ,8) AS SMALLDATETIME ) AS [as SMALLDATETIME]
, CAST( LEFT(@d ,8) AS DATETIME ) AS [as DATETIME]
, CONVERT( NVARCHAR(10), CAST( LEFT(@d ,8) AS DATETIME ) , 121 ) AS [as NVARCHAR(10)]

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

This is what you need, BUT ... next time, post some code that you tried here so we know that we are not doing all your job :)

SELECT
Date
, [2] AS [Total 2]
, [3] AS [Total 3]
FROM YOUR_TABLE_NAME
PIVOT (
COUNT( Status )
FOR Status IN ( [2] , [3] )
) AS p