Is installing MS Outlook a security risk?

I just took over a new project and trying to setup the environment up to best
practicing standards. I wanted to install MS Outlook to setup MAPI. My
manager thinks installing outlook on production server is really nuts. He
thinks it's a security risk and overall having MS Outlook on a production
server doesn't seem right. I have never heard of any kind of security risk or
any other internal problems MS Outlook can create in a production box with
SQL Server installed on it. Can one of the MVPs please confirm it for me and
if possible give any suggestions or arguments that I can use to convince him?
Thanks in advance for your support ...
BobBob wrote:
> I just took over a new project and trying to setup the environment up to best
> practicing standards. I wanted to install MS Outlook to setup MAPI. My
> manager thinks installing outlook on production server is really nuts. He
> thinks it's a security risk and overall having MS Outlook on a production
> server doesn't seem right. I have never heard of any kind of security risk or
> any other internal problems MS Outlook can create in a production box with
> SQL Server installed on it. Can one of the MVPs please confirm it for me and
> if possible give any suggestions or arguments that I can use to convince him?
> Thanks in advance for your support ...
> Bob
I assume this is a SQL 2000 server? I agree, Outlook has no place on a
server, but if you want to use SQL Mail, you have no choice. You do
have alternatives to SQL Mail, however. I've used a command-line mailer
called "BLAT" for years with great success. There is also an extended
stored procedure available for download called xp_smtp_sendmail, that is
nearly a drop-in replacement for xp_sendmail.
Of course, if you're on SQL 2005, this is all moot...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I don't find Outlook to be a security risk, I find it to be a stability
risk. Outlook is a lot of things, but a stable MAPI service provider isn't
on that list. I prefer to use xp_smtpmail from www.sqldev.net. It isn't a
drop-in replacement for SQLMail or SQLAgentMail, but you can make it do most
of the tasks that xp_sendmail supports. I usually add extra steps for
failure and success notification to my agent jobs, but there are several
ways to implement this code. It is a very stable add-in and has caused me
zero problems, even on large cluster installations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:3A2B8DAB-9424-4E86-9C53-C8BD6E0B1206@.microsoft.com...
>I just took over a new project and trying to setup the environment up to
>best
> practicing standards. I wanted to install MS Outlook to setup MAPI. My
> manager thinks installing outlook on production server is really nuts. He
> thinks it's a security risk and overall having MS Outlook on a production
> server doesn't seem right. I have never heard of any kind of security risk
> or
> any other internal problems MS Outlook can create in a production box with
> SQL Server installed on it. Can one of the MVPs please confirm it for me
> and
> if possible give any suggestions or arguments that I can use to convince
> him?
> Thanks in advance for your support ...
> Bob|||Sorry, I forgot to put SQL Server version .. yes it is SQL Server 2000 (SP4)
on Windows 2003 server
That's the confusion .. MS Outlook is also a Microsoft product then why
can't we have that on the server where SQL Server resides?
"Tracy McKibben" wrote:
> Bob wrote:
> > I just took over a new project and trying to setup the environment up to best
> > practicing standards. I wanted to install MS Outlook to setup MAPI. My
> > manager thinks installing outlook on production server is really nuts. He
> > thinks it's a security risk and overall having MS Outlook on a production
> > server doesn't seem right. I have never heard of any kind of security risk or
> > any other internal problems MS Outlook can create in a production box with
> > SQL Server installed on it. Can one of the MVPs please confirm it for me and
> > if possible give any suggestions or arguments that I can use to convince him?
> > Thanks in advance for your support ...
> >
> > Bob
> I assume this is a SQL 2000 server? I agree, Outlook has no place on a
> server, but if you want to use SQL Mail, you have no choice. You do
> have alternatives to SQL Mail, however. I've used a command-line mailer
> called "BLAT" for years with great success. There is also an extended
> stored procedure available for download called xp_smtp_sendmail, that is
> nearly a drop-in replacement for xp_sendmail.
> Of course, if you're on SQL 2005, this is all moot...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Bob wrote:
> Sorry, I forgot to put SQL Server version .. yes it is SQL Server 2000 (SP4)
> on Windows 2003 server
> That's the confusion .. MS Outlook is also a Microsoft product then why
> can't we have that on the server where SQL Server resides?
>
My personal reasons:
1. It's absurd to require a full blown mail client in order to send
SMTP messages from a database server. In the *nix world, there are
hundreds of tiny, dedicated SMTP components available if you need to
send an email message. Why couldn't Microsoft provide something so
simple without requiring their mail client?
2. Outlook is not a stable application, and I don't like having
unstable applications running on my servers, especially when they have
hooks into my database server.
3. Internet Explorer is also a Microsoft product, but I don't trust it,
even on a Microsoft operating system, therefore I don't use it. IE has
some serious flaws, as does Outlook. If something (virus/worm,
whatever) gets loose on my network, I don't want to have to worry about
my databases getting nailed by it.
4. There are simply better options available...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Bob wrote:
> I just took over a new project and trying to setup the environment up to best
> practicing standards. I wanted to install MS Outlook to setup MAPI. My
> manager thinks installing outlook on production server is really nuts. He
> thinks it's a security risk and overall having MS Outlook on a production
> server doesn't seem right. I have never heard of any kind of security risk or
> any other internal problems MS Outlook can create in a production box with
> SQL Server installed on it. Can one of the MVPs please confirm it for me and
> if possible give any suggestions or arguments that I can use to convince him?
> Thanks in advance for your support ...
> Bob
MAPI mail support is gone from SQL Server 2005. That's one more reason
why NOT to use it (the main reason has always been "It sucks!"). Think
xp_smtp_sendmailk, Notification Services or some other mail component.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||my confusion has come to an end after reading all these replies ... Thanks so
much all for your help
"David Portas" wrote:
> Bob wrote:
> > I just took over a new project and trying to setup the environment up to best
> > practicing standards. I wanted to install MS Outlook to setup MAPI. My
> > manager thinks installing outlook on production server is really nuts. He
> > thinks it's a security risk and overall having MS Outlook on a production
> > server doesn't seem right. I have never heard of any kind of security risk or
> > any other internal problems MS Outlook can create in a production box with
> > SQL Server installed on it. Can one of the MVPs please confirm it for me and
> > if possible give any suggestions or arguments that I can use to convince him?
> > Thanks in advance for your support ...
> >
> > Bob
> MAPI mail support is gone from SQL Server 2005. That's one more reason
> why NOT to use it (the main reason has always been "It sucks!"). Think
> xp_smtp_sendmailk, Notification Services or some other mail component.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ekTxHeFpGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Bob wrote:
> > I just took over a new project and trying to setup the environment up to
best
> > practicing standards. I wanted to install MS Outlook to setup MAPI. My
> > manager thinks installing outlook on production server is really nuts.
He
> > thinks it's a security risk and overall having MS Outlook on a
production
> > server doesn't seem right. I have never heard of any kind of security
risk or
> > any other internal problems MS Outlook can create in a production box
with
> > SQL Server installed on it. Can one of the MVPs please confirm it for me
and
> > if possible give any suggestions or arguments that I can use to convince
him?
> > Thanks in advance for your support ...
> >
> > Bob
> I assume this is a SQL 2000 server? I agree, Outlook has no place on a
> server, but if you want to use SQL Mail, you have no choice. You do
> have alternatives to SQL Mail, however. I've used a command-line mailer
> called "BLAT" for years with great success. There is also an extended
> stored procedure available for download called xp_smtp_sendmail, that is
> nearly a drop-in replacement for xp_sendmail.
Except they don't replace the best reason (in my opinion) to use outlook
which is to allow SQL Agent to send alerts, etc.
(I also have used blat btw, decent product.)
I don't think there's a problem with outlook on a SQL Server. Just make sure
it's only sending email and not receiving.
Generally I install the SMTP server that comes with IIS and have my Outlook
install send to this and have it relay to my "real" SMTP server.
This eliminates a lot of issues with Outlook locking up if it can't reach
the mail server.
I don't think the security risk is that large if you understand your goals
and what you're doing.
> Of course, if you're on SQL 2005, this is all moot...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OH33MhFpGHA.1796@.TK2MSFTNGP03.phx.gbl...
> I don't find Outlook to be a security risk, I find it to be a stability
> risk. Outlook is a lot of things, but a stable MAPI service provider
isn't
> on that list.
Just for the record, while I tend to agree, see my other email.
The BEST thing I ever did for Outlook was to put a local SMTP server on the
SQL box and set it up to relay only from that box to my main SMTP server.
This has GREATLY increased my stability. (to the point I really don't have
to think about SQL Mail at all. It pretty much just works.)
> I prefer to use xp_smtpmail from www.sqldev.net. It isn't a
> drop-in replacement for SQLMail or SQLAgentMail, but you can make it do
most
> of the tasks that xp_sendmail supports. I usually add extra steps for
> failure and success notification to my agent jobs, but there are several
> ways to implement this code. It is a very stable add-in and has caused me
> zero problems, even on large cluster installations.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:3A2B8DAB-9424-4E86-9C53-C8BD6E0B1206@.microsoft.com...
> >I just took over a new project and trying to setup the environment up to
> >best
> > practicing standards. I wanted to install MS Outlook to setup MAPI. My
> > manager thinks installing outlook on production server is really nuts.
He
> > thinks it's a security risk and overall having MS Outlook on a
production
> > server doesn't seem right. I have never heard of any kind of security
risk
> > or
> > any other internal problems MS Outlook can create in a production box
with
> > SQL Server installed on it. Can one of the MVPs please confirm it for me
> > and
> > if possible give any suggestions or arguments that I can use to convince
> > him?
> > Thanks in advance for your support ...
> >
> > Bob
>

Is installation secure ?

We can find a lot of recommandations about how to secure a SQL*Server configuration.

Does anyone have scripts to do it ?
Any advices or links are welcomeHi

Have you checked out
http://www.microsoft.com/sql/techin...ity/default.asp

and the baseline security analyser
http://www.microsoft.com/technet/tr...ls/mbsahome.asp

John

"Kona" <kona_iron@.yahoo.fr> wrote in message
news:a1de658a.0402261821.4e275230@.posting.google.c om...
> We can find a lot of recommandations about how to secure a SQL*Server
configuration.
> Does anyone have scripts to do it ?
> Any advices or links are welcome|||kona_iron@.yahoo.fr (Kona) wrote in message news:<a1de658a.0402261821.4e275230@.posting.google.com>...
> We can find a lot of recommandations about how to secure a SQL*Server configuration.
> Does anyone have scripts to do it ?
> Any advices or links are welcome

You can download the Microsoft Security Baseline Analyzer, and the
Microsoft SQL Best Practices Analyzer - both will highlight common
security issues. You might also find this link helpful:

http://www.sqlsecurity.com/DesktopDefault.aspx

Simon

IS install after SP1 fails

Subject says it all; I need to install IS, but the installer says v.2047 of IS is already installed; it it NOT installed. When I go to add/remove programs, under the Sql Server 2005, it allows me to try to install IS, but that also fails, as no components are found for installation. This is SS dev edition, W2003.

I can uninstall SS2005 and start over, but is there a quicker way to fix this?

I don't think this is a known issue. If time to solution is your top priority, probably uninstall/reinstall is the way to go. Otherwise, post links to both the RTM Setup logfiles (%Program Files%\Microsoft SQL Server\90\Setup Bootstrap\Files) and the SP1 Setup logfiles (%Windir%\Hotfix) and we'll see if we can't help you figure out what's going on here.

Paul

|||

I'm kind of curious; I'm not sure which log files you need (and they are too large to post?). The hotfix.log is from April (sp1), and here is a snip:

04/25/2006 08:10:44.168 Found Report Server 2005 product definition
04/25/2006 08:10:45.371 Found DTS 2005 product definition
04/25/2006 08:10:46.871 Determining QFE level for product instance
04/25/2006 08:10:46.917 Failed to read associated hotfix build information for the following file: C:\Program Files\Microsoft SQL Server\90\DTS\\Binn\DTSPipeline.dll
04/25/2006 08:10:46.933 Found following QFE level for product instance : 1399
04/25/2006 08:10:46.933 Determining GDR branching Hotfix for product instance
04/25/2006 08:10:46.949 Failed to read associated hotfix build information for the following file: C:\Program Files\Microsoft SQL Server\90\DTS\\Binn\DTSPipeline.dll
04/25/2006 08:10:46.964 No GDR branch Hotfix found for product instance
04/25/2006 08:10:46.996 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
04/25/2006 08:10:47.011 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
04/25/2006 08:10:47.027 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
04/25/2006 08:10:47.042 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
04/25/2006 08:10:47.105 Attempting to check IA 64 platform
04/25/2006 08:10:47.121 Result of IA64 check: 0
04/25/2006 08:10:47.136 Last error: 0

And there is a good logn sqlsetyp0006_qsqlca1_support.log, note the line that says:

MSI (s) (0C:60) [07:14:04:228]: File: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\Microsoft.SqlServer.DTS.UpgradeAdvisor.dll; Won't Overwrite; Won't patch; Existing file is of an equal version

logging stopped: 6/16/2006 7:14:06 ===

|||

Yes, the logs will be too big to post here. Instead, you'll need to post a link to the files.

I think this is your scenario; please correct me if I'm wrong:

1. You installed RTM, but didn't include IS in the install. [Log files for the original install will be in the %Program Files%\Microsoft SQL Server\... location]

2. You installed SP1 [Log files will be in the %Windir%\Hotfix location]

3. You attempted to add IS to the product by going to Add/Remove Programs (ARP) and selecting "Change" for the SQL Server 2005 entry. [Log files will also be in the %Program Files%\Microsoft SQL Server\... location]

Does this make sense?

|||

1. yes

2. yes

3. yes, after install of IS from media failed.

'post a link'....sorry, I don't get how to allow you to securely access an internal server?

Is Inner join an UNION / INTERSECT / EXCEPT

hi
can any one help me to solve this question?intersect

what textbook are you using?|||:( sorry i do not have got good text book. can you refer me one online? i have done some clients projects. but no theoretical knowledge i do have. thanx for your help .|||here's a really good online resource for your syntax questions: BOL (http://msdn2.microsoft.com/en-us/library/ms189826.aspx)|||It is really help full|||This thread gives me the willies for some reason. *shiver*

Is Index useful?

Hi
In a table A (currently holding approx. 4700 rows but will
expand during use) the primary key is a combination of
three columns col1, col2, col3.
The primary key is indexed with a clustered index
(FillFactor 90).
On the columns col1, col2 an nonclustred index have been
added (FillFactor 90) too, this because I guess these two
columns together form a foreign key to another table B
(currently holding approx 1000 rows - number will raise).
The two indexes are both sorted ascending for all values.
My assumption is that the nonclustered index are of no use
and only adds overhead to inserts etc., since both col1
and col2 allready are indexed (in same sequence and sort
order) in the clustered index, so a query on col1 and/or
col2, and a join between table A and B can already use the
clustered index values for the col1, col2 to speed up the
query and the join using a merge join.
Infact the DBCC SHOWSTATISTIC returns the exact same
result (density etc.) for both the clusterd and
nonclustered index (propably because the nonclustered
index after having scanned its only structure will point
to the clustered index anyway and use this afterwards - to
my recollection).
But does the extra col3 of the clustered index make it
useless for queries/order by/joins on only col1, col2?
or
Can I - with a good nights sleep in mind - just delete the
nonclustered index as it is superflous?
Kind regards
Jakob PerssonJacob
On which column(s) do you have clustered index?
What is mostly WHERE caluse in your queries?
If all three columns are participated in your query you probably want to
consider using COVERING index.
A clustered index is more useful where you try to retrive a set of data ( a
lot of rows) on other hand a non clustered index will be more useful where
you retrieve a single row.
Again it is depends upon you requriments, you will have to monitor your
query to find out an appropritate indexes
SET STATISTICS IO may be useful ,also look at excution plan of the query.
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:084601c3b8bc$173b71c0$a501280a@.phx.gbl...
> Hi
> In a table A (currently holding approx. 4700 rows but will
> expand during use) the primary key is a combination of
> three columns col1, col2, col3.
> The primary key is indexed with a clustered index
> (FillFactor 90).
> On the columns col1, col2 an nonclustred index have been
> added (FillFactor 90) too, this because I guess these two
> columns together form a foreign key to another table B
> (currently holding approx 1000 rows - number will raise).
> The two indexes are both sorted ascending for all values.
> My assumption is that the nonclustered index are of no use
> and only adds overhead to inserts etc., since both col1
> and col2 allready are indexed (in same sequence and sort
> order) in the clustered index, so a query on col1 and/or
> col2, and a join between table A and B can already use the
> clustered index values for the col1, col2 to speed up the
> query and the join using a merge join.
> Infact the DBCC SHOWSTATISTIC returns the exact same
> result (density etc.) for both the clusterd and
> nonclustered index (propably because the nonclustered
> index after having scanned its only structure will point
> to the clustered index anyway and use this afterwards - to
> my recollection).
> But does the extra col3 of the clustered index make it
> useless for queries/order by/joins on only col1, col2?
> or
> Can I - with a good nights sleep in mind - just delete the
> nonclustered index as it is superflous?
> Kind regards
> Jakob Persson
>|||Jakob,
>But does the extra col3 of the clustered index make it
>useless for queries/order by/joins on only col1, col2?
No. The index with col1, col2, col3 is just as useful as the one with col1, col2. So your thinking
is spot on. One thing is of course if col1 and col2 are very narrow and col3 is very wide, but that
is obvious.
Another, more important issue, is that the index on col1, col2 is an NC index which has other
characteristics than a clustered index. The optimizer might use this index to cover queries (or
partially cover), which can have significant performance gains compared to a clustered index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:084601c3b8bc$173b71c0$a501280a@.phx.gbl...
> Hi
> In a table A (currently holding approx. 4700 rows but will
> expand during use) the primary key is a combination of
> three columns col1, col2, col3.
> The primary key is indexed with a clustered index
> (FillFactor 90).
> On the columns col1, col2 an nonclustred index have been
> added (FillFactor 90) too, this because I guess these two
> columns together form a foreign key to another table B
> (currently holding approx 1000 rows - number will raise).
> The two indexes are both sorted ascending for all values.
> My assumption is that the nonclustered index are of no use
> and only adds overhead to inserts etc., since both col1
> and col2 allready are indexed (in same sequence and sort
> order) in the clustered index, so a query on col1 and/or
> col2, and a join between table A and B can already use the
> clustered index values for the col1, col2 to speed up the
> query and the join using a merge join.
> Infact the DBCC SHOWSTATISTIC returns the exact same
> result (density etc.) for both the clusterd and
> nonclustered index (propably because the nonclustered
> index after having scanned its only structure will point
> to the clustered index anyway and use this afterwards - to
> my recollection).
> But does the extra col3 of the clustered index make it
> useless for queries/order by/joins on only col1, col2?
> or
> Can I - with a good nights sleep in mind - just delete the
> nonclustered index as it is superflous?
> Kind regards
> Jakob Persson
>

is importing a dtsx file Necessary

Ok, I'm actually adding a SSIS job to my job agent on my test SQL server. Noticed that when I go to my job agent --> add new job, under the steps option, I click new. this then takes me to the new job step window. When I select

Type as SQL Server Integrated Services, I then see some new tabs at the bottom of the form. Under package source I can select File System, SQL Server, or SSIS Package Store, then I have to select the location of the dtsx file.

So my question is, since I can select the actual file (package) I want to run from here, do I really have to import a package to the file system or MSDB under the SQL Integration Services on the server?

It appears to me that its kind of the same thing.

I'm new to this SSIS, SQL DB work, so I'm learning as I go. . . .Yes, you do. If the file doesn't reside on the server, how will the agent job be able to find it?

I believe that when you select filesystem in the Agent job step, it's showing you your local filesystem, not that of the server.

Is IIS always required for replication?

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

Yes IIS is very much required for Merge or RDA with SQL Server. However, setting up IIS is not a big thing as we have a "Configure Web Synchronization" wizard that is very helpful for you in this regard.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||Hi

And how setting up a replication bettween a Windows Mobile 5 Pocket PC and a Windows XP Home Edition PC when IIS can't be install on Home Edition ?

?

Thanks for help
Jo?l

|||

1) What is your backend database?

2) Can I know what is the problem in moving to Win XP Professional Edition?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||1) To replace synchronisation between Access/PocketAccess (not more supported with WM5), we use know a MSDE database (on PC) with a SQL Server CE 2.0 database (on PocketPC). The replication work fine on Windows XP Pro, but on Windows XP Home there is no IIS to do the replication.

2) Our customer have Windows XP Home Edition. I'm ok with you that's not very "professional", but we can't tell to our customer : "Hey guys, you must move your forty PC to Windows XP Pro, because our software don't run on XP Home". They will say us "bye bye".

And they have already some old PocketPC (before WM5) with this application installed and it works with XP Home ... that's a big problem for us.

Thanks for helping us

|||I have heard that we can synchronize using ActiveSync is it correct or not, because on Online Book I read we can synchronize using IIS. I need to synchronize my database on Pocket PC to my Desktop PC without using IIS (if we have the cradle why we need access to internet from Pocket PC).

And if synchronized only can work with IIS than use the old way try to send each data by client server application. Its a silly things, but thats the only way if we can only synchronize using IIS.

Is IIS always required for replication?

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

Yes IIS is very much required for Merge or RDA with SQL Server. However, setting up IIS is not a big thing as we have a "Configure Web Synchronization" wizard that is very helpful for you in this regard.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||Hi

And how setting up a replication bettween a Windows Mobile 5 Pocket PC and a Windows XP Home Edition PC when IIS can't be install on Home Edition ?

?

Thanks for help
Jo?l|||

1) What is your backend database?

2) Can I know what is the problem in moving to Win XP Professional Edition?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||1) To replace synchronisation between Access/PocketAccess (not more supported with WM5), we use know a MSDE database (on PC) with a SQL Server CE 2.0 database (on PocketPC). The replication work fine on Windows XP Pro, but on Windows XP Home there is no IIS to do the replication.

2) Our customer have Windows XP Home Edition. I'm ok with you that's not very "professional", but we can't tell to our customer : "Hey guys, you must move your forty PC to Windows XP Pro, because our software don't run on XP Home". They will say us "bye bye".

And they have already some old PocketPC (before WM5) with this application installed and it works with XP Home ... that's a big problem for us.

Thanks for helping us|||I have heard that we can synchronize using ActiveSync is it correct or not, because on Online Book I read we can synchronize using IIS. I need to synchronize my database on Pocket PC to my Desktop PC without using IIS (if we have the cradle why we need access to internet from Pocket PC).

And if synchronized only can work with IIS than use the old way try to send each data by client server application. Its a silly things, but thats the only way if we can only synchronize using IIS.

Is IDENTITY_INSERT OFF safe?

Say I have a sproc that is occasionally used to restore deleted records to a
table.
--If I use
SET IDENTITY_INSERT myTable ON
--Then insert the records...
INSERT INTO myTable(ID, AnyField)
VALUES (2,'hello);
--Then I return table to original state
SET IDENTITY_INSERT myTable OFF
During that process, what would happen if another user was attempting to
insert records into the same table?
Even if my transaction only takes .2 milliseconds, is it possible another
user will get an error, or is SQL Server smart enough to delay their
transaction(s) [lock the table] until mine is complete?
Thanks,
ChrisOther updaters are not blocked by SET IDENTITY_INSERT ON. But this is not a
problem since the IDENTITY_INSERT ON applies only to the connection that
runs it. The identity column is handled normally in all other connections
(unless, of course, that connection has also set IDENTITY_INSERT ON.
So, yes, it is safe to use.
Tom
"Chris" <rooster575@.hotmail.com> wrote in message
news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Say I have a sproc that is occasionally used to restore deleted records to
> a table.
> --If I use
> SET IDENTITY_INSERT myTable ON
> --Then insert the records...
> INSERT INTO myTable(ID, AnyField)
> VALUES (2,'hello);
> --Then I return table to original state
> SET IDENTITY_INSERT myTable OFF
> During that process, what would happen if another user was attempting to
> insert records into the same table?
> Even if my transaction only takes .2 milliseconds, is it possible another
> user will get an error, or is SQL Server smart enough to delay their
> transaction(s) [lock the table] until mine is complete?
> Thanks,
> Chris
>|||Thanks Tom!
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:WsmdnSkiXZ4uqBvZnZ2dnUVZ_vqdnZ2d@.co
mcast.com...
> Other updaters are not blocked by SET IDENTITY_INSERT ON. But this is not
> a problem since the IDENTITY_INSERT ON applies only to the connection that
> runs it. The identity column is handled normally in all other connections
> (unless, of course, that connection has also set IDENTITY_INSERT ON.
> So, yes, it is safe to use.
> Tom
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
>|||ACID
Isolation

Is HierarchyUniqueNameStyle Enumeration Working?

Hi,

When I am performing a drillthrough on my cubes I discover the header of my drillthrough report of all column consist of [<DimensionUniqueName>].[<HierarchyName>]. I am trying to make the header of all columns to contain only [<HierarchyName>].

After reading http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.hierarchyuniquenamestyle.aspx, I set this property in my cube to ExcludeDimensionName, however, it does not solve remove the DimensionUniqueName from my drillthrough report.

Anyone has any idea how to remove DimensionUniqueName from the header of my drillthrough report?

I am having the same problem. hierarchyuniquenamestyle set to ExcludeDimensionNam doesn't work neither.

Is having a trigger that inserts a row in Table A, when a row in same table is inserted by

I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.

Thanks

Hey,

I would prefer stored procedure; the reason is I have no control over triggers, so it makes it easier from a maintenance perspective on me, which is what I think you may also be under that restraint? I don't know how much slowing down it would do; I would think a lot of that depends on volume of data as well.

If you are inserting the main data using a stored procedure, then that stored procedure can do two inserts. You can also group them in a transaction so if one fails, both statements are rolled back for safety purposes.

Microsoft includes a lot of things: they include xp_cmdshell which most DBA's disable because of the power of it. So they will include things even though they may not be the best to implement...

|||

Actually, your DBA is correct. I always recommend, if possible, opting for stored procedures as opposed to triggers. The trigger will save you time if you are inserting rows into the table from many different sources in many different ways. But, if the only point of entry is a stored procedure, there's no reason not to place it in the sp. The farther back in SQL Server version you go, the less reliable triggers seem to get - and, yes, they can place locks on tables. Microsoft included them because they have their place - but it is a mistake to use them as substitutes for logical flow.

This shouldn't be a lot of extra work on you. If you aren't using a stored procedure already, they are much faster and far more secure than "on page" SQL. If you have insert statements in various parts of your application inserting to the same table, then you should be encapsulating them into a stored procedure anyway! Your DBA's job is to protect the efficiency and cleanliness of your database. Adding triggers unnecessarily affects both.

Is getting table-comments out of SQL Server with selects possible?

Hi,
We have used Enterprise Manager to add comments to all our column
definitions. Unfortunately, the only place we can see these comments are in
the windows of Enterprise Manager. We would like to be able to query for
these comments, just like it is possible to query for the names of all
tables and columns from systables, sysobjects, information_schema.tables
etc.
For example
use master
select * from information_schema.columns
So, anyone who can say where our comments are stored (we sure hope they are
stored inside the database in more or less clear text somewhere).
Best regards,
Helge G Solheim, MCSD
Computas
hgs at computasnospam dot comIt is in sysproperties, but the supported measures you find if you search BOL for "extended
properties". For instance the fn_listextendedproperty() function.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Helge G Solheim" <hgs@.nospam.org> wrote in message news:OdmqwJCiDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We have used Enterprise Manager to add comments to all our column
> definitions. Unfortunately, the only place we can see these comments are in
> the windows of Enterprise Manager. We would like to be able to query for
> these comments, just like it is possible to query for the names of all
> tables and columns from systables, sysobjects, information_schema.tables
> etc.
> For example
> use master
> select * from information_schema.columns
> So, anyone who can say where our comments are stored (we sure hope they are
> stored inside the database in more or less clear text somewhere).
> Best regards,
> Helge G Solheim, MCSD
> Computas
> hgs at computasnospam dot com
>

Is generating replica at night a best practice and what is the cos

Generating 5 fresh different publications (each hold data for one distinct
region) over night, so client can download their selection replica.
Now what is the cost for such thing?
What is the over head per 1MB of data in terms of system tables and in terms
of download times on a typical intranet/internet.
Personal experience will be excellent input.
How fast is the connection? How fast are the boxes? What type of
Replication? Lots of variables. Test, test, test.
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:A252FA00-25CE-413D-BDE0-7830BF3A7DD5@.microsoft.com...
> Generating 5 fresh different publications (each hold data for one distinct
> region) over night, so client can download their selection replica.
> Now what is the cost for such thing?
> What is the over head per 1MB of data in terms of system tables and in
terms
> of download times on a typical intranet/internet.
> Personal experience will be excellent input.
>

Is full-replication of *all* databases possible?

Hi All,
I am a newbie to MS SQL Server 2000 but I really need to solve this issue:
We need to daily replicate all databases within our MS SQL Server 2000 to
another MS SQL Server 2000 - the amount of databases is not fixed because
new databases are created on a daily basis.
--> Is it possible to create a job that replicates all databases from one MS
SQL Server to another and where do I have to configure it? (I already looked
at the abonnement- and replication-assistent in the enterprise manager but I
couldn't find any option to automatically daily replicate *all* databases..)
I hope somebody can help me! Any hints are highly appreciated.
cheers, jan
On Thu, 27 Oct 2005 17:26:25 +0200, "Jan Rsner" <jan.roesner@.web.de>
wrote:
>I am a newbie to MS SQL Server 2000 but I really need to solve this issue:
>We need to daily replicate all databases within our MS SQL Server 2000 to
>another MS SQL Server 2000 - the amount of databases is not fixed because
>new databases are created on a daily basis.
>--> Is it possible to create a job that replicates all databases from one MS
>SQL Server to another and where do I have to configure it? (I already looked
>at the abonnement- and replication-assistent in the enterprise manager but I
>couldn't find any option to automatically daily replicate *all* databases..)
>I hope somebody can help me! Any hints are highly appreciated.
If it's only "daily" you might just ship backups or something.
No, there's no way to automagically have a new database "replicate"
itself, some kind of setup is going to be required.
HTH,
J.

Is Full Text Struggling...

H there,
We have a query that is taking too long to run, which uses Full Text
(MSSQL2000).
The query below, when using this clause takes between 11 seconds and 10
minutes! QA thnks that the full text search will cost 70% of the query cost.
CONTAINS( Article_text, '("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR
("CJD") OR ("E Coli") OR ("E-Coli") OR ("Food Additives") OR ("Food Safety")
OR ("Genetically Modified Foods") OR ("Kwik Save") OR ("Proof of age card")
OR ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews") AND NOT ("Ahold")
AND NOT ("Beth Israel") AND NOT ("European equity preview") AND NOT
("European stocks may decline") AND NOT ("European stocks may rise") AND NOT
("mediaplex") AND NOT ("UK Stocks Factors")'))
The query below, when using this clause, takes only between 0.02 secs and 2
secs. QA thnks that the full text search will cost 50% of the query cost.
CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
"culture") OR ("alcopops" AND "designated driver initiative") OR ("alcopops"
AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
"price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
driving")')
Why is the second query instant and the first taking ages?
Sometimes, the longer queries such as the first one here can take *much*
longer to run (like, 5 minutes). We're hoping that we can get even the
queries with more expressions to run inside a few seconds. In fact, they did
when we had < 400,000 rows.
Is it possible that our full text indexing is just set up wrong, or that our
hardware isn't sufficient?
Some additional facts that may help...
The table (and FT index) only have 800,000 rows
We run 116 of these queries in a row, directly after each other.
We've cleared out all stop words, since we want to index on anything.
The server has 1GB RAM, single P4 processor, 8GB free space across 2 raid
disks.
Whilst the queries are running, I'm not seeing massive memory use.
Any help much appreciated. Please let me know if you need more info.
Tobes
I would suspect its all the search arguments and Boolean logic you have
which is causing the problems especially the AND NOTs.
Note that this
CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
"culture") OR ("alcopops" AND "designated driver initiative") OR
("alcopops"
AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
"price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
driving")')
is equivalent to the simpler
CONTAINS( Article_text, '"alcopops" AND ("advertising" OR "culture" OR
"designated driver initiative" OR "drink driving" OR "legislation" OR
"price" OR "pricing" OR "underage driving")')
Revisting the AND NOTs, basically the way this is processed is all matches
are returned for the first part
("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR ("CJD") OR ("E Coli") OR
("E-Coli") OR ("Food Additives") OR ("Food Safety") OR ("Genetically
Modified Foods") OR ("Kwik Save") OR ("Proof of age card") OR
("Somerfield") OR ("Supermarkets") OR ("Wine Reviews")
and then you trim rows which contain
AND NOT ("Ahold") AND NOT ("Beth Israel") AND NOT ("European equity
preview") AND NOT ("European stocks may decline") AND NOT ("European stocks
may rise") AND NOT
("mediaplex") AND NOT ("UK Stocks Factors")'))
This trimming is very expensive.
One thing you might do is sp_fulltext_service 'resource_usage' to 5. This
might help slightly.
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
"Tobin Harris" <tobin@._do_not_spam_tobinharris.com> wrote in message
news:42af07ca$0$2588$da0feed9@.news.zen.co.uk...
> H there,
> We have a query that is taking too long to run, which uses Full Text
> (MSSQL2000).
> The query below, when using this clause takes between 11 seconds and 10
> minutes! QA thnks that the full text search will cost 70% of the query
cost.
> CONTAINS( Article_text, '("Food" AND "Supermarkets") OR ("CITIZEN CARD")
OR
> ("CJD") OR ("E Coli") OR ("E-Coli") OR ("Food Additives") OR ("Food
Safety")
> OR ("Genetically Modified Foods") OR ("Kwik Save") OR ("Proof of age
card")
> OR ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews") AND NOT
("Ahold")
> AND NOT ("Beth Israel") AND NOT ("European equity preview") AND NOT
> ("European stocks may decline") AND NOT ("European stocks may rise") AND
NOT
> ("mediaplex") AND NOT ("UK Stocks Factors")'))
> The query below, when using this clause, takes only between 0.02 secs and
2
> secs. QA thnks that the full text search will cost 50% of the query cost.
> CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
> "culture") OR ("alcopops" AND "designated driver initiative") OR
("alcopops"
> AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
> "price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
> driving")')
> Why is the second query instant and the first taking ages?
> Sometimes, the longer queries such as the first one here can take *much*
> longer to run (like, 5 minutes). We're hoping that we can get even the
> queries with more expressions to run inside a few seconds. In fact, they
did
> when we had < 400,000 rows.
> Is it possible that our full text indexing is just set up wrong, or that
our
> hardware isn't sufficient?
> Some additional facts that may help...
> The table (and FT index) only have 800,000 rows
> We run 116 of these queries in a row, directly after each other.
> We've cleared out all stop words, since we want to index on anything.
> The server has 1GB RAM, single P4 processor, 8GB free space across 2 raid
> disks.
> Whilst the queries are running, I'm not seeing massive memory use.
> Any help much appreciated. Please let me know if you need more info.
> Tobes
>
>
|||Hi Hilary,
Thank you for the reply. Yes, the queries run significantly quicker without
the NOTs. I had tried the other boolen expressions in a more compact form,
but that made little difference unfortunately. I guess the full text search
engine may do it's own optimising to clean up our verbose queries!
In two weeks we'll be throwing more hardware at the program (doubling ram,
increasing disk capacity, and introducing dual Xeon processors), so
hopefully that will make the situation better.
Our main problem is that we want scalability. At the moment we have 116
"projects", each with their own queries that run one by one. These are
taking hours to run (some queries quick, some looooong!). Do you think we
may benefit from running more than one query in parrallel? For example, have
two processes executing 58 queries each?
Thanks again for your help.
Tobes
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ex7e8bQcFHA.720@.TK2MSFTNGP15.phx.gbl...
>I would suspect its all the search arguments and Boolean logic you have
> which is causing the problems especially the AND NOTs.
> Note that this
> CONTAINS( Article_text, '("alcopops" AND "advertising") OR ("alcopops" AND
> "culture") OR ("alcopops" AND "designated driver initiative") OR
> ("alcopops"
> AND "drink driving") OR ("alcopops" AND "legislation") OR ("alcopops" AND
> "price") OR ("alcopops" AND "pricing") OR ("alcopops" AND "underage
> driving")')
> is equivalent to the simpler
> CONTAINS( Article_text, '"alcopops" AND ("advertising" OR "culture" OR
> "designated driver initiative" OR "drink driving" OR "legislation" OR
> "price" OR "pricing" OR "underage driving")')
> Revisting the AND NOTs, basically the way this is processed is all matches
> are returned for the first part
> ("Food" AND "Supermarkets") OR ("CITIZEN CARD") OR ("CJD") OR ("E Coli")
> OR
> ("E-Coli") OR ("Food Additives") OR ("Food Safety") OR ("Genetically
> Modified Foods") OR ("Kwik Save") OR ("Proof of age card") OR
> ("Somerfield") OR ("Supermarkets") OR ("Wine Reviews")
> and then you trim rows which contain
> AND NOT ("Ahold") AND NOT ("Beth Israel") AND NOT ("European equity
> preview") AND NOT ("European stocks may decline") AND NOT ("European
> stocks
> may rise") AND NOT
> ("mediaplex") AND NOT ("UK Stocks Factors")'))
> This trimming is very expensive.
> One thing you might do is sp_fulltext_service 'resource_usage' to 5. This
> might help slightly.
> --
> 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
> "Tobin Harris" <tobin@._do_not_spam_tobinharris.com> wrote in message
> news:42af07ca$0$2588$da0feed9@.news.zen.co.uk...
> cost.
> OR
> Safety")
> card")
> ("Ahold")
> NOT
> 2
> ("alcopops"
> did
> our
>

Is Forms Authentication available on Standard

I have been looking at the forms authentication sample that creates a custom login page. After looking at the licencing am I right in saying that this can only be implemented using SQL server enterprise edition as standard edition does not have the security extension API. If this is the case is there any way to custom authenticate a user when using URL calls that is not windows Authentication?I have found the answer which is simply no. Back to Crystal Reports then. Good luck getting this off the ground when you have to buy Enterprise Edition just to securely provide reports over the web that have some kind of functionality.
"Ryan Smith" wrote:
> I have been looking at the forms authentication sample that creates a custom login page. After looking at the licencing am I right in saying that this can only be implemented using SQL server enterprise edition as standard edition does not have the security extension API. If this is the case is there any way to custom authenticate a user when using URL calls that is not windows Authentication?|||Web services does not support everything as it only provides a snapshot of
the report and does not have drill down functionality. If you have a report
that has drill downs (95% of my reports) in it and you run a webservice for
it, when the user clicks on the drill down they get an error.
"Richard" wrote:
> You can still build your own web app (report manager) and use the web service
> to communitate with it. I have gone over the api for the ws and it supports
> everyting you need. Yes it's a bit of work up front but there are no
> licensing issues at all
> "Ryan Smith" wrote:
> > I have found the answer which is simply no. Back to Crystal Reports then. Good luck getting this off the ground when you have to buy Enterprise Edition just to securely provide reports over the web that have some kind of functionality.
> >
> > "Ryan Smith" wrote:
> >
> > > I have been looking at the forms authentication sample that creates a custom login page. After looking at the licencing am I right in saying that this can only be implemented using SQL server enterprise edition as standard edition does not have the security extension API. If this is the case is there any way to custom authenticate a user when using URL calls that is not windows Authentication?

Is FOR XML EXPLICIT still an accepted technique?

I have inherited (someone else built it) an ASP IIS site attached to a SQL
Server 2000 database. It is quite a large web site job and I don't want to
rewrite it in .NET. I don't have the time to do that and I am not familiar
with .NET. Our company still uses VB6 for our products.
The remote site allows the user to select recordsets which currently can be
emailed as HTML or TEXT and also downloaded in an Excel (XLS) file. My job
is to create XML from the recordset, transmit it to the client browser
(which is part of a VB program) and have the client program load it into the
local SQL Server database.
I am new at using XML and have done considerable reading (my head hurts).
Some of the books are a couple years old. The recordsets are composed of
header records from the main table and child records (one to many) from 3
other tables.
I am leaning toward using FOR XML EXPLICIT in conjunction with ADODB stream
sent to the Response object. I have gotten a simple FOR XML AUTO program to
work properly and send the stream back to the browser, but now I need to
shape the more complicated XML properly.
I just want to make sure that the FOR XML EXPLICIT will not become "legacy"
code in the next few years. I have looked at using XML Views briefly, but do
not like the setup required on the SQL server to use them. It will be a
hosted remote server that houses the IIS ASP code and the SQL database.
So before I spend weeks writing and debugging the process, I want to make
sure I haven't missed some spectacular new, reliable and "easy" method of
accomplishing the same thing.
Also I plan on using the Transact/SQL OPENXML function to write the
resulting XML to the database at the client site.
There is one other problem. Using the ADODB stream sent to the Response
object results in the XML remaining "hidden" (such that a blank page appears
in the browser) which is fine...except I don't know how to access it. I
have experience using XML data islands (in HTML pages) to populate SQL
Server and also opening XML files on disk and writting to SQL Server.
Thanks for your help in advance...
For XML Explicit is definitely supported in the next release of SQL Server.
There is also a For XML Path option in the next release that would be easier
for you to use but anything you do in Explicit mode should work for the
foreseeable future. OpenXML is also fully supported in SQL Server 2005.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:e5SLwHbaEHA.3684@.TK2MSFTNGP09.phx.gbl...
>I have inherited (someone else built it) an ASP IIS site attached to a SQL
> Server 2000 database. It is quite a large web site job and I don't want to
> rewrite it in .NET. I don't have the time to do that and I am not familiar
> with .NET. Our company still uses VB6 for our products.
> The remote site allows the user to select recordsets which currently can
> be
> emailed as HTML or TEXT and also downloaded in an Excel (XLS) file. My job
> is to create XML from the recordset, transmit it to the client browser
> (which is part of a VB program) and have the client program load it into
> the
> local SQL Server database.
> I am new at using XML and have done considerable reading (my head hurts).
> Some of the books are a couple years old. The recordsets are composed of
> header records from the main table and child records (one to many) from 3
> other tables.
> I am leaning toward using FOR XML EXPLICIT in conjunction with ADODB
> stream
> sent to the Response object. I have gotten a simple FOR XML AUTO program
> to
> work properly and send the stream back to the browser, but now I need to
> shape the more complicated XML properly.
> I just want to make sure that the FOR XML EXPLICIT will not become
> "legacy"
> code in the next few years. I have looked at using XML Views briefly, but
> do
> not like the setup required on the SQL server to use them. It will be a
> hosted remote server that houses the IIS ASP code and the SQL database.
> So before I spend weeks writing and debugging the process, I want to make
> sure I haven't missed some spectacular new, reliable and "easy" method of
> accomplishing the same thing.
> Also I plan on using the Transact/SQL OPENXML function to write the
> resulting XML to the database at the client site.
> There is one other problem. Using the ADODB stream sent to the Response
> object results in the XML remaining "hidden" (such that a blank page
> appears
> in the browser) which is fine...except I don't know how to access it. I
> have experience using XML data islands (in HTML pages) to populate SQL
> Server and also opening XML files on disk and writting to SQL Server.
> Thanks for your help in advance...
>

is folder App_data uses to store database by .net 2.0 hosting companies?

I am workin on a website for me and my friend. I want to host it at a hosting company. In asp.net 2.0 projects are build with the App_data folder, which can stores data source such as a SQL Server 2005 database. But when i want to host the site at a hosting company, will the database goed into this folder?

I also have a hosting company and dealing with the same problem.

The company has a SQL server that is different than the web server so; you have to create the database in the SQL server and then have your asp codes connect to that server by a connection string. You have to have the IP address of the server, your username and password to make that work.

Hope this helps.

is first item in the group

Hi all,
I have the report layout like this:
Group1
Item1
Item2
...
Group2
Item1
Item2
...
...
I would like to alternate the background color on Items and Item1 always
starts with the same color and alternate from there for the rest of the
items in that group. How can I do it? I use IIF(RowNumber(Nothing), color1,
color2) but it does not work with item1 being the same color for every
group. Please advice. Thanks.I got it. Thanks.
"testing" <@.hotmail.com> wrote in message
news:%23MVQfSzIFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have the report layout like this:
> Group1
> Item1
> Item2
> ...
> Group2
> Item1
> Item2
> ...
> ...
> I would like to alternate the background color on Items and Item1 always
> starts with the same color and alternate from there for the rest of the
> items in that group. How can I do it? I use IIF(RowNumber(Nothing),
color1,
> color2) but it does not work with item1 being the same color for every
> group. Please advice. Thanks.
>

Is Express will be launched with Main Product

Hi,

Is SQL Server Express 2008 will be launched with main product (SQL Server 2008) or later ?

Anybody has any idia ?

Express will be released in a later CTP and will ship with SQL Server 2008.

Is Express will be launched with Main Product

Hi,

Is SQL Server Express 2008 will be launched with main product (SQL Server 2008) or later ?

Anybody has any idia ?

Express will be released in a later CTP and will ship with SQL Server 2008.

is Exists faster than a Join

From http://www.sql-server-performance.co...sis_tuning.asp
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?
The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon
|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>
|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ
|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>
|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon
|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:

>From http://www.sql-server-performance.co...sis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan
|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

is Exists faster than a Join

From http://www.sql-server-performance.c...ysis_tuning.asp
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:

>From http://www.sql-server-performance.c...ysis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.
4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

is Exists faster than a Join

From http://www.sql-server-performance.com/query_analysis_tuning.asp
B) Can standard JOIN's be replaced with EXISTS statements?
If you need to join tables to ensure XYZ value exists or a specific set of
rows exists, then considering porting to EXISTS or NOT EXISTS statements.
At the same time is may also alleviate the need for DISTINCT clauses.
So is a Join really potentiall slower?The Exists clause can be faster than a join because it just searches long
enough to find the first "Hit" as opposed to returning all rows that meet
the join criteria.
Greg Jackson
PDX, Oregon|||Could you please elaborate? An Exists will meet all the rows that meet the
query criteria. Given that the criteria should return the same data as a
join, how could it be faster?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eZWWYB7aFHA.3848@.TK2MSFTNGP10.phx.gbl...
> The Exists clause can be faster than a join because it just searches long
> enough to find the first "Hit" as opposed to returning all rows that meet
> the join criteria.
>
> Greg Jackson
> PDX, Oregon
>|||I say
SELECT * FROM Main
WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
then it will only check for the first hit.
still probably not making sense.
show us YOUR query and I can probably explain a bit better
GAJ|||create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table1 (T1C2) values ('T1C2-2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
select t1.T1C1,T1C2
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
or
select t1.T1C1,T1C2
from #table1 t1
where exists
(select * from #Table2 t2
where t2.T2C1 = T1.T1C1)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23fZBCd7aFHA.464@.TK2MSFTNGP15.phx.gbl...
>I say
> SELECT * FROM Main
> WHERE Exists(SELECT 1 FROM MyJoinTable a WHERE a.foo = main.foo)
> then it will only check for the first hit.
> still probably not making sense.
> show us YOUR query and I can probably explain a bit better
>
> GAJ
>|||that's faster than a Join.
the where clause will return TRUE as soon as it finds a SINGLE record in
TABLE 2 that mathces Table1 (It will not bother returning more than 1 record
that matches to table 1)
A join will return ALL Records in T2 that match T1. IF there are millions of
records in each table, this would be exponentially faster.
Greg Jackson
PDX, Oregon|||On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
>From http://www.sql-server-performance.com/query_analysis_tuning.asp
>B) Can standard JOIN's be replaced with EXISTS statements?
>If you need to join tables to ensure XYZ value exists or a specific set of
>rows exists, then considering porting to EXISTS or NOT EXISTS statements.
>At the same time is may also alleviate the need for DISTINCT clauses.
>
>So is a Join really potentiall slower?
Hi Chris,
The question is actually irrelevant. A join is different: a query such
as
SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
will never return more than one copy of each row in a. Using a join
instead, such as this:
SELECT a.*
FROM a
JOIN b ON b.xx = a.xx
might return multiple copies of rows in a for values of xx that are not
unique in table b.
If no value of xx is ever duplicated in b, then the results will be the
same, but the EXISTS version might still be faster. Since SQL Server
doesn't know that there is only one row in b with a particular value of
xx, it still has to search the rest of the table after encoutering one;
if EXISTS is used, the search is aborted after the first match.
If SQL Server knows that values of b.xx are unique (because there is a
PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
the same - the optimizer MIGHT decide to use the knowledge of this
constraint and createt the same execution plan it'll also use for the
EXISTS version.
I have never seen or heard of any situation where the JOIN would be
fafster than the "equivalent" EXISTS version. And I don't think I ever
will.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo and Chris,
"Hugo Kornelis" wrote:
> On Tue, 7 Jun 2005 15:11:21 -0700, ChrisR wrote:
> >From http://www.sql-server-performance.com/query_analysis_tuning.asp
> >
> >B) Can standard JOIN's be replaced with EXISTS statements?
> >If you need to join tables to ensure XYZ value exists or a specific set of
> >rows exists, then considering porting to EXISTS or NOT EXISTS statements.
> >At the same time is may also alleviate the need for DISTINCT clauses.
> >
> >So is a Join really potentiall slower?
> Hi Chris,
> The question is actually irrelevant. A join is different: a query such
> as
> SELECT a.*
> FROM a
> WHERE EXISTS (SELECT * FROM b WHERE b.xx = a.xx)
> will never return more than one copy of each row in a. Using a join
> instead, such as this:
> SELECT a.*
> FROM a
> JOIN b ON b.xx = a.xx
> might return multiple copies of rows in a for values of xx that are not
> unique in table b.
> If no value of xx is ever duplicated in b, then the results will be the
> same, but the EXISTS version might still be faster. Since SQL Server
> doesn't know that there is only one row in b with a particular value of
> xx, it still has to search the rest of the table after encoutering one;
> if EXISTS is used, the search is aborted after the first match.
> If SQL Server knows that values of b.xx are unique (because there is a
> PRIMARY KEY or UNIQUE constraint on b.xx), then the performance MIGHT be
> the same - the optimizer MIGHT decide to use the knowledge of this
> constraint and createt the same execution plan it'll also use for the
> EXISTS version.
> I have never seen or heard of any situation where the JOIN would be
> fafster than the "equivalent" EXISTS version. And I don't think I ever
> will.
>
I think you have to try the statement both ways.
I'm not sure about EXISTS, but I have a fairly complex multi-way join that
is much faster with
a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
than with
WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Regards,
Jonathan|||On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Also, be careful of the Optimizer Estimated Execution Plans. I especially
see this in comparisons between LEFT OUTER JOINS versus the use of EXISTS or
NOT EXISTS.
Even though the JOIN may produce a better ESTIMATED execution cost, it will
often create a HASH table for the JOIN, where the EXISTS solution will often
use a Nested Loop JOIN. The HASH will ALWAYS create temporary object, which
will have physical impact that the Optimizer does not take into account.
You're best bet would be to SET STATISTICS time and I/O on and run them both
ways, making sure to flush the Buffer Pool before each execution. This will
give you ACTUAL run information, not just ESTIMATED.
Sincerely,
Anthony Thomas
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mnfha11l8fukurbcocmi9rg30s47q0lj6j@.4ax.com...
On Thu, 9 Jun 2005 12:52:03 -0700, Jonathan Levine wrote:
(snip)
>I'm not sure about EXISTS, but I have a fairly complex multi-way join that
>is much faster with
>a LEFT JOIN b on a.id = b.id ... WHERE b.id is null
>than with
>WHERE NOT EXISTS (select 1 from b where a.id = b.id)
Hi Jonathan,
Thanks for adding this. This is not the first time I witness or hear
reports of this either. It seems that the SQL Server development team
put most effort in the optimization of join strategies, since they are
used most often in typical queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Is Excel really Excel

I have a client who uses Reporting Services for their reports. They
prefer Excel format, but what comes back doesn't seem to be a real Excel
file. It will only load in Office 2003 for the PC, everything else,
including Macs, thinks they are some XML file. For large reports,
several thousands rows that produce 52 MB files, do not load at all and
will cause desktops to crap out. What kind of Excel comes back from RS?Hi,
I think you need to install Service Pack 1 for Reporting services as it
introduces excel export for excel pre Office 2003.
Although Service Pack 2 is out now so you can just install that (it already
contains Service Pack 1).
"No One" wrote:
> I have a client who uses Reporting Services for their reports. They
> prefer Excel format, but what comes back doesn't seem to be a real Excel
> file. It will only load in Office 2003 for the PC, everything else,
> including Macs, thinks they are some XML file. For large reports,
> several thousands rows that produce 52 MB files, do not load at all and
> will cause desktops to crap out. What kind of Excel comes back from RS?
>|||Is this chosen with the same format specifier or a different one?
NH wrote:
> Hi,
> I think you need to install Service Pack 1 for Reporting services as it
> introduces excel export for excel pre Office 2003.
> Although Service Pack 2 is out now so you can just install that (it already
> contains Service Pack 1).
> "No One" wrote:
>
>>I have a client who uses Reporting Services for their reports. They
>>prefer Excel format, but what comes back doesn't seem to be a real Excel
>>file. It will only load in Office 2003 for the PC, everything else,
>>including Macs, thinks they are some XML file. For large reports,
>>several thousands rows that produce 52 MB files, do not load at all and
>>will cause desktops to crap out. What kind of Excel comes back from RS?|||I dont understand your question.
"No One" wrote:
> Is this chosen with the same format specifier or a different one?
> NH wrote:
> > Hi,
> >
> > I think you need to install Service Pack 1 for Reporting services as it
> > introduces excel export for excel pre Office 2003.
> >
> > Although Service Pack 2 is out now so you can just install that (it already
> > contains Service Pack 1).
> >
> > "No One" wrote:
> >
> >
> >>I have a client who uses Reporting Services for their reports. They
> >>prefer Excel format, but what comes back doesn't seem to be a real Excel
> >>file. It will only load in Office 2003 for the PC, everything else,
> >>including Macs, thinks they are some XML file. For large reports,
> >>several thousands rows that produce 52 MB files, do not load at all and
> >>will cause desktops to crap out. What kind of Excel comes back from RS?
> >>
>|||When you choose Excel prior to the service packs it came out in an XML
format known and supported by Excel. But, it had to be Excel 2003. With the
service pack they went to binary format of Excel which is supported by Excel
2000 and greater. When you select Excel after the service pack is applied
you will get the binary format.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"No One" <aintnoway@.blahblahblah.com> wrote in message
news:adbml2-jp9.ln1@.gandalf.grey-net.com...
> Is this chosen with the same format specifier or a different one?
> NH wrote:
>> Hi,
>> I think you need to install Service Pack 1 for Reporting services as it
>> introduces excel export for excel pre Office 2003. Although Service Pack
>> 2 is out now so you can just install that (it already contains Service
>> Pack 1).
>> "No One" wrote:
>>
>>I have a client who uses Reporting Services for their reports. They
>>prefer Excel format, but what comes back doesn't seem to be a real Excel
>>file. It will only load in Office 2003 for the PC, everything else,
>>including Macs, thinks they are some XML file. For large reports,
>>several thousands rows that produce 52 MB files, do not load at all and
>>will cause desktops to crap out. What kind of Excel comes back from RS?|||Thanks.
Is there anyway to tell if the stream returned in empty?
Bruce L-C [MVP] wrote:
> When you choose Excel prior to the service packs it came out in an XML
> format known and supported by Excel. But, it had to be Excel 2003. With the
> service pack they went to binary format of Excel which is supported by Excel
> 2000 and greater. When you select Excel after the service pack is applied
> you will get the binary format.
>|||It also works in OpenOffice. Very good.
Bruce L-C [MVP] wrote:
> When you choose Excel prior to the service packs it came out in an XML
> format known and supported by Excel. But, it had to be Excel 2003. With the
> service pack they went to binary format of Excel which is supported by Excel
> 2000 and greater. When you select Excel after the service pack is applied
> you will get the binary format.
>

Is Excel ASOLEDB9 taking advantage of cube partitioning?

Hi,

I wonder if Excel ASOLEDB9 is benefiting from cube partitioning?
Some queries are very slow and the Excel generated code look not that good

I tried to pick some queries from SQL Server Profiler and run them in an mdx query window and I get syntax errors.

This leaves me perplex since I have the feeling that people try endless queries through their Excel pivot cube, then, after a while they cancel the Excel query because it takes forever, then the server remain stuck on a high level of CPU usage.

Is it because the syntax error or is it because they just ask for too much data?
Is canceling an Excel pivot data refresh enough to stop the server's query processing?

Yesterday night, it was so bad (100% CPU) that I had to restart the server.

Thanks,

Philippe

Cube partitioning is server-side, so all clients should benefit from it.

When you cancel a query in Excel, you dont cancel it server-side. Check out the following thread for more info on this.

|||Guys,
This is going to be a big problem.
Queries cancelled by the user keep running on the server.

This kills the server and there is no way that someone would spend time trying to manually trace these runaway queries and manually cancel them on the server.

It is also a big issue to have to restart the server everyday just because of these runaway queries.

I would like to see a fix for it in SP2 with a high Priority rating.

This is a server killer.

Probably the biggest bug ever in SSAS2005.

Philippe|||

If anything, this is probably a Excel bug.

If you are really struggeling with this you could try to write some custom code that identifies long running queries (look at the activityviewer sample application). Then you could cancel these queries with a xmla cancel command. Finally schedule your code to run every ten minutes or so with SQL Server Agent.

Is Enterprize manager secure for remote admin?

Hello all,

An ASP.NET website hosting service allows the use of Enterprize Manager to manage the backend database of a hosted asp.net website. This is not done across a VPN and I don't think it could be done on SSL so the question is : How Secure is that? would this be ok for learning but not Ecommerce? Or is it an encrypted session and I paranoid?

-Thanks
HeywadeI wouldn't be overly enthusiastic about storing sensitive e-commerce info in there, no. I think you can rig it to communicate with IPSec, but it depends what the host supports - ask them.

Is editing possible in QA 2000 debugger?

Hi,

Just started using the debugger in Query Analyser in SQL Server 2000.

Is there a way I can edit the code? It's nice to be able to step through the code,
but I don't seem to be able to edit it.

Cheers!

Eric.

use the alter statement

you can use it with Sps, table, view, udf etc

here some example

http://doc.ddart.net/mssql/sql70/aa-az_5.htm

you can also right click on the object in the object browser

and click on script object to new window as and then

clcik on alter

|||You can't edit in debug mode, as Joey says you need to issue an Alter proc command from the editor

Is DTS Support in 2005 Going Away Permanently at Some Point?

I hope this is the right forum to post my question in.

Does anyone know if or when Microsoft will discontinue support for 2000 DTS Legacy packages within SQL Server 2005? We’re in the process of migrating from 2000 to 2005 and have a lot of packages to migrate to Integration Services. We’re having problems migrating the packages because quite a few of them are complex and won’t run after migrating to IS without a major rewrite. Right now it seems that those packages will run just fine under the Legacy folder on the 2005 instance with the data connections pointed to 2005 databases. We’d just like to plan appropriately if we absolutely have to migrate those packages to IS at some point soon. We realize that we'll need to create new packages using IS, though.
So I’d appreciate it if anyone has heard anything to please let me know. I apologize if this has been asked before, but I couldn’t seem to find any posts on it.

Thanks!

Here's some info

http://blogs.conchango.com/jamesrowlandjones/archive/2007/02/18/08.04.08-is-SQL2K-day.aspx

|||Thanks!|||

Hi, your question was good and info noted before was good as well.
I thought I would point you to the official deprecation notice as well.

http://msdn2.microsoft.com/en-us/library/ms403408.aspx

Its intentionally vauge regarding timelines but the idea is to inform you of general direction and changes. You question was if at some point SQL '2005' would not support DTS 2000 pacakges, I think its fair to say no, because 2005 is out the door and in customer hands.

The real question is for how lmany future versions of SQL server support DTS 2000 pacakges. There obviously has to be a limit for practicality reasons. MSFT would like to support everything all the time but thats not realistic as it would continually hinder inovations and changes. So, the reason the depecation noitce is vauge on timeline as the exact version support is stopped may depend on how versions evolve with new features and such. Things will not just get pulled out from under you, but there is a realistic limit. The intent of the notice is similar to the blog, to inform you its needed at some point and formulating a strategy sooner rather than later is a good thing.

hope that helps

Is DTC required in a SQL 2000/Windows 2003 cluster ?

Hi,
If the SQL environment is not going to use distributed transactions, is it
still necessary to configure a DTC resource on the cluster ?
Thanks
In theory, you can run SQL 2000 clustered without DTC. In practice, it can
limit functionality. I generally configure a DTC resource, just to simplify
installation and maintenance.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"SteveO" <SteveO@.discussions.microsoft.com> wrote in message
news:332351AB-0806-4F9A-94DF-C5F929F5B7D0@.microsoft.com...
> Hi,
> If the SQL environment is not going to use distributed transactions, is it
> still necessary to configure a DTC resource on the cluster ?
> Thanks
|||Yes. You will always configured DTC into the cluster. There are other
services which will use it.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"SteveO" <SteveO@.discussions.microsoft.com> wrote in message
news:332351AB-0806-4F9A-94DF-C5F929F5B7D0@.microsoft.com...
> Hi,
> If the SQL environment is not going to use distributed transactions, is it
> still necessary to configure a DTC resource on the cluster ?
> Thanks
|||I hate to nitpick. But technically, you don't have to if you don't need it.
Linchi
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23YUT2d2IGHA.2912@.tk2msftngp13.phx.gbl...
> Yes. You will always configured DTC into the cluster. There are other
> services which will use it.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "SteveO" <SteveO@.discussions.microsoft.com> wrote in message
> news:332351AB-0806-4F9A-94DF-C5F929F5B7D0@.microsoft.com...
>
|||Well, you'd have to take that up with the failover clustering team. It is
their explicit recommendation for ALL clusters that you will install a
failover cluster instance in.
Not having it there is going to cause several errors during the upgrade as
well as throwing additional errors during a fresh install. I haven't tested
enough permutations to say whether it is needed or not, so at this point, I
tend to trust the people who have spent thousands of hours testing hundreds
of different configurations. They know the code a lot better than me since
they wrote it. If that's the recommendation, I'm sticking into in this
case, because I have no way of proving that it shouldn't be followed.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
news:%23Dc$G%238IGHA.2064@.TK2MSFTNGP09.phx.gbl...
>I hate to nitpick. But technically, you don't have to if you don't need it.
> Linchi
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23YUT2d2IGHA.2912@.tk2msftngp13.phx.gbl...
>
|||I have 15 SQL Clusters in Production without MSDTC in the cluster, the local
service is disabled. No issues. Our application simply does not use it!
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uuCH0K9IGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Well, you'd have to take that up with the failover clustering team. It is
> their explicit recommendation for ALL clusters that you will install a
> failover cluster instance in.
> Not having it there is going to cause several errors during the upgrade as
> well as throwing additional errors during a fresh install. I haven't
> tested enough permutations to say whether it is needed or not, so at this
> point, I tend to trust the people who have spent thousands of hours
> testing hundreds of different configurations. They know the code a lot
> better than me since they wrote it. If that's the recommendation, I'm
> sticking into in this case, because I have no way of proving that it
> shouldn't be followed.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
> news:%23Dc$G%238IGHA.2064@.TK2MSFTNGP09.phx.gbl...
>
>