Data Delivery

I've created a number of .html, pivot tables using Access' Data Page
tools. These work great and by pointing my browser to these files,
users throughout my firm can access these pivot tables. I had assumed
that I could simply publish these pivot tables on the web to outside
users who are configured as active directory users. The .htm pivot
table is linked to a query in Access that is built from a SQL database.
We've separately built a system where users can crawl through our file
server from outside of our network. I placed the .htm file in a folder
that the user has access to and the application shows the file.
However, when the user clicks on the file, s/he gets these messages:
Data Access Pages has detected that your IE security settings will not
allow you to access data from a site considered to be insecure.
[
In order to access the data contained within the Data Access Page, you
need to:
1. Start IE
2. Choose Internet Options from the Tools menu
3. Click on the "Security" Tab
4. Click on the "Trusted Sites" icon
5. Click on the "Sites..." button
6. Uncheck the "Require server verification (https) for all sites in
the zone" checkbox
]
Our technology consultant indicated that there's no way for the data to
"bind" to the browser and it's impossible to expose these .htm, pivot
tables to users outside of our network. Does anyone know a way to
accomplish what I'm trying to do? I feel like it should be possible
since Microsoft created that product to build .htm pages?
Ryan
Any suggestions would be very helpfulRyan.Chowdhury@.gmail.com wrote:
> I've created a number of .html, pivot tables using Access' Data Page
> tools. These work great and by pointing my browser to these files,
> users throughout my firm can access these pivot tables. I had assumed
> that I could simply publish these pivot tables on the web to outside
> users who are configured as active directory users. The .htm pivot
> table is linked to a query in Access that is built from a SQL database.
>
> We've separately built a system where users can crawl through our file
> server from outside of our network. I placed the .htm file in a folder
> that the user has access to and the application shows the file.
> However, when the user clicks on the file, s/he gets these messages:
> Data Access Pages has detected that your IE security settings will not
> allow you to access data from a site considered to be insecure.
> [
> In order to access the data contained within the Data Access Page, you
> need to:
> 1. Start IE
> 2. Choose Internet Options from the Tools menu
> 3. Click on the "Security" Tab
> 4. Click on the "Trusted Sites" icon
> 5. Click on the "Sites..." button
> 6. Uncheck the "Require server verification (https) for all sites in
> the zone" checkbox
> ]
> Our technology consultant indicated that there's no way for the data to
> "bind" to the browser and it's impossible to expose these .htm, pivot
> tables to users outside of our network. Does anyone know a way to
> accomplish what I'm trying to do? I feel like it should be possible
> since Microsoft created that product to build .htm pages?
> Ryan
> Any suggestions would be very helpful
>
First off, you might have better luck asking in an Access group instead
of SQL Server. Second, let me say, YIKES!!! You allow outside access
to your file servers?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data Delivery

I've created a number of .html, pivot tables using Access' Data Page
tools. These work great and by pointing my browser to these files,
users throughout my firm can access these pivot tables. I had assumed
that I could simply publish these pivot tables on the web to outside
users who are configured as active directory users. The .htm pivot
table is linked to a query in Access that is built from a SQL database.
We've separately built a system where users can crawl through our file
server from outside of our network. I placed the .htm file in a folder
that the user has access to and the application shows the file.
However, when the user clicks on the file, s/he gets these messages:
Data Access Pages has detected that your IE security settings will not
allow you to access data from a site considered to be insecure.
[
In order to access the data contained within the Data Access Page, you
need to:
1. Start IE
2. Choose Internet Options from the Tools menu
3. Click on the "Security" Tab
4. Click on the "Trusted Sites" icon
5. Click on the "Sites..." button
6. Uncheck the "Require server verification (https) for all sites in
the zone" checkbox
]
Our technology consultant indicated that there's no way for the data to
"bind" to the browser and it's impossible to expose these .htm, pivot
tables to users outside of our network. Does anyone know a way to
accomplish what I'm trying to do? I feel like it should be possible
since Microsoft created that product to build .htm pages?
Ryan
Any suggestions would be very helpfulRyan.Chowdhury@.gmail.com wrote:
> I've created a number of .html, pivot tables using Access' Data Page
> tools. These work great and by pointing my browser to these files,
> users throughout my firm can access these pivot tables. I had assumed
> that I could simply publish these pivot tables on the web to outside
> users who are configured as active directory users. The .htm pivot
> table is linked to a query in Access that is built from a SQL database.
>
> We've separately built a system where users can crawl through our file
> server from outside of our network. I placed the .htm file in a folder
> that the user has access to and the application shows the file.
> However, when the user clicks on the file, s/he gets these messages:
> Data Access Pages has detected that your IE security settings will not
> allow you to access data from a site considered to be insecure.
> [
> In order to access the data contained within the Data Access Page, you
> need to:
> 1. Start IE
> 2. Choose Internet Options from the Tools menu
> 3. Click on the "Security" Tab
> 4. Click on the "Trusted Sites" icon
> 5. Click on the "Sites..." button
> 6. Uncheck the "Require server verification (https) for all sites in
> the zone" checkbox
> ]
> Our technology consultant indicated that there's no way for the data to
> "bind" to the browser and it's impossible to expose these .htm, pivot
> tables to users outside of our network. Does anyone know a way to
> accomplish what I'm trying to do? I feel like it should be possible
> since Microsoft created that product to build .htm pages?
> Ryan
> Any suggestions would be very helpful
>
First off, you might have better luck asking in an Access group instead
of SQL Server. Second, let me say, YIKES!!! You allow outside access
to your file servers?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data Compression Applicability

Hi,

Can you tell me whether the compression intended for Katmai is suitable for OLTP applications?

I know that Oracle has had compression for a number of years, but it is not particularly useful for tables subject to a reasonable degree of inserts/updates. It would be great if Katmai compression could be used across the board with substantial space savings for every application type. What's the story ?

Many Thanks,

Derek

The main focus for this release is data warehouse scenarios, but we have a compression scheme that might be useful for OLTP applications as well.

We will be supporting compressing for both bulk load and normal DML operations.

More information will come when compression becomes part of a future CTP.

Thanks,

Data and index pages

As found in a book saying about Index pages:
'Unlike data pages, there is no limit to the total number of entries that
can be made on an index page.'
What is that mean ?
It's wrong. An index page has the exact same limit as a data page. There's
8k in a page minus the header size and the slot array and that's all the
space that available on the page for records (entries). There's a finite
limit defined using the minimum possible record size plus the slot array
overhead for each record - I'd work it out for you exactly but I can't
remember the minimum possible record size off the top of my head. The max is
somewhere around 310-320 records per page though.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uXr6ainqEHA.1644@.tk2msftngp13.phx.gbl...
> As found in a book saying about Index pages:
> 'Unlike data pages, there is no limit to the total number of entries that
> can be made on an index page.'
> What is that mean ?
>

Data and index pages

As found in a book saying about Index pages:
'Unlike data pages, there is no limit to the total number of entries that
can be made on an index page.'
What is that mean ?It's wrong. An index page has the exact same limit as a data page. There's
8k in a page minus the header size and the slot array and that's all the
space that available on the page for records (entries). There's a finite
limit defined using the minimum possible record size plus the slot array
overhead for each record - I'd work it out for you exactly but I can't
remember the minimum possible record size off the top of my head. The max is
somewhere around 310-320 records per page though.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uXr6ainqEHA.1644@.tk2msftngp13.phx.gbl...
> As found in a book saying about Index pages:
> 'Unlike data pages, there is no limit to the total number of entries that
> can be made on an index page.'
> What is that mean ?
>

Data access by creating functions

a number of time I have come accross developers using functions like

GetProduct()
GetProductTitle()
GetProductCategory()

to get the value of the data instead of just using queries / stored procedures

I have not understood why

can you please point to some good forum messages / blogposts / articles on this ?

Whats your take on this ?Dealing with functions that give direct results is easier programmatically than dealing with OUTPUT parameters in stored procedures.|||The concept of user defined functions is a great way to centralize code and they can come in very handy.

But in my experience they have one major drawback: performance.

When a user defined function needs to be performed on over a thousend rows (arbitrary boundery) the performance seems to decrease dramaticly. My guess it is because SQL Server has to switch from set-processing to row-by-row prossing.

AmitGeorge: look in the BOL under CREATE FUNCTION. That doesn't say anything about performance though.

Lex|||The use of UDFs will not cause the engine to stop using set-based processing. It's more likely due to the fact that UDF's tend to complex application-specific algorithms, otherwise they would be included in the standard stock of functions, right? And of course, there is no guarantee that the code in the UDF is GOOD code. MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."|||Hi Blindman,

A few months back I had to tune a stp which did a select which returned about 50 columns and about 25000 rows. Of these 50 columns 10 called a udf which only did the following:
CREATE FUNCTION convdate (@.dtMyDate DATETIME)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN CONVERT(VARCHAR(8), @.dtMyDate, 112)
END
After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

If SQL is still using set-based processing I can't explain why! Got any ideas?

Lex|||i was not talking about using UDFs

I am talking about functions in the client language like VB, PHP, Asp, Java etc|||After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

If SQL is still using set-based processing I can't explain why! Got any ideas?Sorry. I just don't get the same results. I used these objects:set nocount on
go

CREATE FUNCTION dbo.convdate (@.dtMyDate DATETIME)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN CONVERT(VARCHAR(8), @.dtMyDate, 112)
END

GO

CREATE TABLE TestDates (DateID int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED, DateValue datetime NOT NULL)

Insert into TestDates (DateValue)
select dateadd(Hour, Ones + Tens + Hundreds + Thousands + TenThousands, '2000-01-01')
from (select 0 as Ones
Union select 1
Union select 2
Union select 3
Union select 4
Union select 5
Union select 6
Union select 7
Union select 8
Union select 9) Ones,
(select 0 as Tens
Union select 10
Union select 20
Union select 30
Union select 40
Union select 50
Union select 60
Union select 70
Union select 80
Union select 90) Tens,
(select 0 as Hundreds
Union select 100
Union select 200
Union select 300
Union select 400
Union select 500
Union select 600
Union select 700
Union select 800
Union select 900) Hundreds,
(select 0 as Thousands
Union select 1000
Union select 2000
Union select 3000
Union select 4000
Union select 5000
Union select 6000
Union select 7000
Union select 8000
Union select 9000) Thousands,
(select 0 as TenThousands
Union select 10000
Union select 20000
Union select 30000
Union select 40000
Union select 50000
Union select 60000
Union select 70000
Union select 80000
Union select 90000) TenThousands
order by Ones + Tens + Hundreds + Thousands + TenThousands

Running these statements, I get virtually no difference in execution times:declare @.StartTime datetime
set @.StartTime = getdate()

-- select CONVERT(VARCHAR(8), DateValue, 112)
-- from TestDates

select dbo.convdate(DateValue)
from TestDates

select datediff(ms, @.StartTime, GetDate())
Regardless, you can check the execution plans for each statement and see that they are identical.|||i was not talking about using UDFs

I am talking about functions in the client language like VB, PHP, Asp, Java etc

you got the wrong forum then.|||MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."

it appears 2005 does this by making sucky code suck more after the "upgrade".

Data access by creating functions

A number of time I have come accross developers using functions like

GetProduct()
GetProductTitle()
GetProductCategory()

to get the value of the data instead of just using queries / stored procedures

Not UDFs but functions in VB and in DLLs

I have not understood why

can you please point to some good forum messages / blogposts / articles on this ?

Whats your take on this ?The main reason for this is to separate your code into layers. When you create this function, then you could have different database implementations inside of the function, like data from SQL Server or Oracle, but to outside world, that uses this function, it is still the same value. This design simplifies switching between databases and does not affect the code that calls these functions

DAC doen't work with explicit port number

Hello everybody,

After install SQL 2005 I noticed than DAC (Dedicated Admin Connection) is started with dynamic port.

2006-08-08 12:19:47.870 Server Server is listening on [ 'any' <ipv4> 14330].
2006-08-08 12:19:47.880 Server Server is listening on [ 'any' <ipv4> 1433].
2006-08-08 12:19:47.880 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\A ].
2006-08-08 12:19:47.880 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$A\sql\query ].
2006-08-08 12:19:47.900 Server Server is listening on [ 'any' <ipv4> 1212].
2006-08-08 12:19:47.900 Server Dedicated admin connection support was established for listening remotely on port 1212.

My question:
Is possible fix port for DAC?
How can I use DAC behind a firewall if I don't know witch port it will use?

Ok, the BOL say: If SQL Server is configured to accept remote administration connections, the DAC must be initiated with an explicit port number:
sqlcmd –Stcp:<server>,<port> (By the way, not is need -A parameter ?)

but it doen't work for me!

Por default connection I have:
C:\sqlcmd -A -SSERVER01\A -Usa -P123456 -dmaster

sys.dm_exec_connections say:
connect_time net_transport client_tcp_port local_tcp_port
-- - --
2006-08-08 12:24:05.750 TCP 3143 1433
2006-08-08 12:49:56.513 TCP 3273 1212

Specifying port I have:
C:\sqlcmd -A -Stcp:SERVER01,1434 -Usa -P123456 -dmaster

but I get "Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired."

PS: I try with any port but always receive timeout !!!

May someone help please..

Thx
Nilton Pinheiro

Hello peoples,

Please....

Is possible fix port for DAC?
How can I use DAC behind a firewall if I don't know witch port it will use?

thx.
Nilton Pinheiro