run access query

I have a database front end using access, back end uses sql server. In front end, i have a query, how can i run this query in sql server.
Another querstion, the cliend need the data, but that is too big, it is about 91700 records, How can i retrieve it and send to them. I try to use access, but when i save it as .xls file, it told me there is too many record to save it. Anybody has any idea about the data? Thanks in advance.1) Use a Pass-Thru query? I'm not sure exactly what you mean, though.
2) Could you save the records to a .txt file? Maybe zip the file if you need to.|||I'd save the result set as a table within an MS-Access MDB file.

-PatP|||I have report in access front end called rptActive. When i run this report, it contain 91700 record, But i just export the data to my drive, But it gave me message that it has too many records. So i can't save it.|||i can save it as a table, but the client need to send to them as email format. how can i do that?|||They can email from an Access table, or you could split the result set into two or more Excel files for them.

-PatP|||i can email, but it is too big to send, i am going to split this data.
For the first question i mention, i had query call rptActive in access front end, how can i run this query in sql server? that is my question. Thanks for all your help.|||See MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/achowSendCommandsMSAccessSqlDatabasePassthroughQue S.asp).

-PatP|||thank you very much! What is the different between run on access link table and run directly on the sql server, the later one will save time, is that right? When to use it?|||The two environments (SQL Server versus MS-Access) are compatible, but quite different. Different rules, different benefits, different costs.

From a pure machine efficiency standpoint, a query run on SQL Server will always out perform the same query run against a linked table in MS-Access. However, that query will then run "pass through" to SQL Server, so it runs in a more complex and more technically demanding environment which means that the query may be harder to write.

I'm used to dealing with data on scales that MS-Access couldn't begin to handle. Because of that, I'm much more accustomed to SQL Server syntax than I am to the Jet syntax used by MS-Access. Not everyone sees data the way that I do (no comments from the peanut gallery!), so your comfort level may well be different than mine.

-PatP

rtrim/ltrim works good - but need some additional feedback

Using

select rtrim(ltrim(total)) as Total

from testtable

WHERE Total is NOT NULL

The results come back allright except for a small handful:

594
1242
17458
214
6971
29023
808
1
0
37
9
65
39
9
0
0
0
2
632
9
0
641
0
2438
148
2
1882
153
556
11839
616
1250
17709
187
6630
29548
803
7880
606
1600
1479
690
765
1630
3953
2418
9
75
1563
2
635
3
175
36
783
54
28
665
60
261
11995
679
1438
17548
118
6211
29543
797
7649
572
878
488
948
273
1756
2111
1205
2
54
1068
2
188
0
58

Showing me that there are still some spaces not getting trimmed off.. I would love to present this as trimmed as possible. Is there something to this that I am missing?

this this:

select rtrim(ltrim(cast(total as varchar(100)))) as Total

from testtable

WHERE Total is NOT NULL

|||

IF all of your values are in fact numbers, then use a cast instead of all the lrtim/rtrim.

Code Snippet

DECLARE @.MyTable table
( Col1 varchar(25)
)

INSERT INTO @.MyTable VALUES (' 29023')
INSERT INTO @.MyTable VALUES ('808')
INSERT INTO @.MyTable VALUES (' 1')

SELECT cast(Col1 as int) FROM @.MyTable

--
29023
808
1

|||

They are numbers within a string that looks to be compiled of binary specs of hell.

I am not seeing hex characters, but some binary. When I use your suggestion I get:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value '

1' to data type int.

The field value is varchar(200) and there is no way I can change without some other functions blowing up.

In the end - I have some vbscript which I will be applying to a page so that this numeric data can be summed up.

|||

Perhaps this function will 'solve' your issue -it should eliminate all non-numerical characters.

Code Snippet

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'fnNumbersOnly'
)
DROP FUNCTION dbo.fnNumbersOnly
GO

CREATE FUNCTION dbo.fnNumbersOnly
( @.InParam varchar(50) )
RETURNS varchar(50)
AS

BEGIN
IF patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
WHILE patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
SET @.InParam = Stuff( @.InParam, patindex( '%[^0-9]%', @.InParam), 1, '' )
END
END
RETURN @.InParam
END
GO

Then use it like this:

Code Snippet

SELECT dbo.fnNumbersOnly( Total ) FROM TestTable

|||

OMG! Yes! Too cool!

Thank you!

|||

The problem was almost certainly due to something like embedded tab characters or other non-space whitespace characters.

If you run:

SELECT ' ' + char(9) + ' 123'

you will see that the second 5 spaces after the TAB are left behind. LTrim (and probably RTrim) only seem to remove spaces.