EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\mailservername\public folders'
Error received:The OLE DB provider "exoledb.DataSource.1" has not been registered.
Does the Exchange Ole DB Driver exist on the SQL Server machine? I am assuming the 2 services are not on the same machine. If not, you may need to install the Exchange SDK or management tools on the SQL Server to get the OleDB driver installed.
EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\\.\backofficestorage\mailservername\public folders'
Error received:The OLE DB provider "exoledb.DataSource.1" has not been registered.
Does the Exchange Ole DB Driver exist on the SQL Server machine? I am assuming the 2 services are not on the same machine. If not, you may need to install the Exchange SDK or management tools on the SQL Server to get the OleDB driver installed.
I would like to create more lines by concatenating values.
When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +
'example'> the result is <This is an example> (on the same line).
I woul like to get:
<This
is
an
example> (each 'word' on a new line, but in 1 field)
Whis SQL statement do i have to use?"Hans" <hans.de.korte@.prominent.nl> wrote in message
news:ae7dcba4.0402200557.1942ab24@.posting.google.c om...
> Hello,
> I would like to create more lines by concatenating values.
> When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +
> 'example'> the result is <This is an example> (on the same line).
> I woul like to get:
> <This
> is
> an
> example> (each 'word' on a new line, but in 1 field)
> Whis SQL statement do i have to use?
See CHAR() in Books Online.
Simon|||It looks like you are trying to format a string in SQL. It is always a
good practice to do this kind of formatting in the application. Given
that in order to add a line break as part of the string you need to
use the char function. char(13)+char(10) make a line break i.e. line
feed and carriage return. To answer your example you can try
declare @.cf varchar(2)
set @.cf=' '+char(13)+char(10)+' '
select 'This' + @.cr + 'is' + @.cf + 'an' + @.cf + 'example'
If you don't want to use the variable then you substitute the variable
with the expression ' '+char(13)+char(10)+' '. As I mentioned before
it is not a best practice to do this kind of formatting at database
level.
Ramesh
hans.de.korte@.prominent.nl (Hans) wrote in message news:<ae7dcba4.0402200557.1942ab24@.posting.google.com>...
> Hello,
> I would like to create more lines by concatenating values.
> When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +
> 'example'> the result is <This is an example> (on the same line).
> I woul like to get:
> <This
> is
> an
> example> (each 'word' on a new line, but in 1 field)
> Whis SQL statement do i have to use?
Hi,
I am getting the following error.
while creating Instance of Notification Service.
Hi,This is probably becuase your instance definition file or application definition file is not compatible with SQL NS schemas.
Following link contains complete template of instance and application definition file. Make sure your xml adheres to it.
http://msdn2.microsoft.com/en-us/library/ms145917.aspx
http://msdn2.microsoft.com/en-US/library/ms145313.aspx
Shamir
|||As Shamir mentioned, both the ICF and ADF must be well-formed and valid XML documents. They are validated against an XSD schema in your SSNS installation folder.
Keep in mind that XML is case-sensitive.
HTH...
Joe
Hi,
I am getting the following error.
while creating Instance of Notification Service.
Hi,This is probably becuase your instance definition file or application definition file is not compatible with SQL NS schemas.
Following link contains complete template of instance and application definition file. Make sure your xml adheres to it.
http://msdn2.microsoft.com/en-us/library/ms145917.aspx
http://msdn2.microsoft.com/en-US/library/ms145313.aspx
Shamir|||As Shamir mentioned, both the ICF and ADF must be well-formed and valid XML documents. They are validated against an XSD schema in your SSNS installation folder.
Keep in mind that XML is case-sensitive.
HTH...
Joe
Hi,
I am getting the following error.
while creating Instance of Notification Service.
Hi,This is probably becuase your instance definition file or application definition file is not compatible with SQL NS schemas.
Following link contains complete template of instance and application definition file. Make sure your xml adheres to it.
http://msdn2.microsoft.com/en-us/library/ms145917.aspx
http://msdn2.microsoft.com/en-US/library/ms145313.aspx
Shamir
|||As Shamir mentioned, both the ICF and ADF must be well-formed and valid XML documents. They are validated against an XSD schema in your SSNS installation folder.
Keep in mind that XML is case-sensitive.
HTH...
Joe
SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173725223.934126.197140@.30g2000cwc.googlegro ups.com...
Quote:
Originally Posted by
With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?
>
HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.
Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.
Quote:
Originally Posted by
SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
>
Thanks for the info!
Quote:
Originally Posted by
>
Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.
>
HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.
>
Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.
>
Quote:
Originally Posted by
SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
I'm unable to find documentation for the CREATE INDEX statement for SQL Mobile. DROP INDEX is documented however. Is this a known issue or am I missing something? This can be seen here:
http://msdn2.microsoft.com/en-us/library/ms173440(en-US,SQL.90).aspx
If you scroll the left pane up a little, you will notice that CREATE INDEX is not in the tree. Am I missing something?
Thanks.Thanks Ravi for catching this. And we are aware of it too.
We already have a bug opened in our Bug Database for this.
The next web site refresh should pickup the fix.
Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation|||Yes, the topic will be available in the December download version of SQL Server Books Online.
In the meantime, you can use the CREATE INDEX topic in the full version of Books Online : http://msdn2.microsoft.com/en-us/library/ms188783(en-US,SQL.90).aspx
Regards,|||
Finally it is live in SQL Mobile Books Online @. http://msdn2.microsoft.com/en-us/library/ms345331(en-US,SQL.90).aspx
Thanks,.
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
I'm unable to find documentation for the CREATE INDEX statement for SQL Mobile. DROP INDEX is documented however. Is this a known issue or am I missing something? This can be seen here:
http://msdn2.microsoft.com/en-us/library/ms173440(en-US,SQL.90).aspx
If you scroll the left pane up a little, you will notice that CREATE INDEX is not in the tree. Am I missing something?
Thanks.Thanks Ravi for catching this. And we are aware of it too.
We already have a bug opened in our Bug Database for this.
The next web site refresh should pickup the fix.
Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation|||Yes, the topic will be available in the December download version of SQL Server Books Online.
In the meantime, you can use the CREATE INDEX topic in the full version of Books Online : http://msdn2.microsoft.com/en-us/library/ms188783(en-US,SQL.90).aspx
Regards,|||
Finally it is live in SQL Mobile Books Online @. http://msdn2.microsoft.com/en-us/library/ms345331(en-US,SQL.90).aspx
Thanks,.
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
"dfurtney" <dfurtney@.hotmail.com> wrote in message
news:1105492090.497574.233360@.z14g2000cwz.googlegr oups.com...
> SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
> that we need to add some indexes for. In a test, it took over 12 hours
> to CREATE a new INDEX against this table. One of us suggested that we
> create a temp table with the new index and copy the data from the old
> table into the new one, then rename it. I understand this took 15
> minutes. Why the heck would it be faster to move the data and build
> multiple indexes incrementally vs adding an index??|||Hi
This seems unlikely.
You did not mention if the table is a heap (i.e. there is no clustered
index).
You probably ran into a case where the data in the old table was so out
of order that building the additional index was constatnly splitting
pages.|||dfurtney (dfurtney@.hotmail.com) writes:
> SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
> that we need to add some indexes for. In a test, it took over 12 hours
> to CREATE a new INDEX against this table. One of us suggested that we
> create a temp table with the new index and copy the data from the old
> table into the new one, then rename it. I understand this took 15
> minutes. Why the heck would it be faster to move the data and build
> multiple indexes incrementally vs adding an index??
12 hours to create an index for three million rows sounds abnormal.
Of course, if the table did not have a clustered index, but already had
several non-clustered index, and you added a clustered index, then it
will take some time, but still not 12 hours.
One possible reason, is that the CREATE INDEX process was blocked by
another process most of the time.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The table, vehicle_history, had a clustered primary key and 2
additional indexes. We were adding an additional index on a single
integer column. That column was filled with a single default value of
0 in this case. It was running on a dedicated QA server.
We want to add this index to speed up a query against the new column.
But we found it was taking much longer than we expected to add the
index. The only thing that seemed somewhat unusual is the size of the
table.|||dfurtney (dfurtney@.hotmail.com) writes:
> The table, vehicle_history, had a clustered primary key and 2
> additional indexes. We were adding an additional index on a single
> integer column. That column was filled with a single default value of
> 0 in this case. It was running on a dedicated QA server.
> We want to add this index to speed up a query against the new column.
> But we found it was taking much longer than we expected to add the
> index. The only thing that seemed somewhat unusual is the size of the
> table.
Does all three million rows have 0 in this column? In that case it would
not be a very good index.
I have no idea whether large amount of duplicate values could be reason
that creating the index so long. I still lean towards that there was some-
thing else, for instance blocking, that was the cause. It simply doesn't
take 12 hours to create a non-clustered index on a three-million row table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The query/index would be used by a subset of our customers that utilize
a specific feature of the product. For them, the values would be
non-zero, of course, and the resulting index quite selective. However,
we were going to add the index for all customers since we generally
don't know what functionaly they will be utilizing. This dataset was
from a customer not using that function.
I thought it was awfully long and one of my co-workers was going to do
some testing. What order of time would you expect?|||dfurtney (dfurtney@.hotmail.com) writes:
> The query/index would be used by a subset of our customers that utilize
> a specific feature of the product. For them, the values would be
> non-zero, of course, and the resulting index quite selective. However,
> we were going to add the index for all customers since we generally
> don't know what functionaly they will be utilizing. This dataset was
> from a customer not using that function.
> I thought it was awfully long and one of my co-workers was going to do
> some testing. What order of time would you expect?
The below script which emulates the situation you have described ran
in eight minutes on my workstation, a 2.8 GHz HT box with 1 GB of RAM
(but with SQL Server constrained to some 120 MB), running Windows XP SP2.
The particular part of creating a non-clustered index on a column with
non-variant values took 30 seconds. (But then all data was in cache.)
Of course, not only number of rows count, but the size of the rows as
well, since wider the rows, the more pages you get. Then again, for
the sorting phase there are still only three million rows.
It occurred to me that one thing you could have run into is autogrow.
If the database is 300 GB, and you have 10% autogrow and this happens to
set in during the index creation, you're in for a pause. Initializing
30 GB of data does take some time. Not 12 hours though. 20-30 minutes
may be expected.
use master
go
drop database klump
go
create database klump
go
use klump
go
select TOP 3000000 klumpid = identity(int, 1, 1),
slaskcol = 0,
a.* into klump
from Northwind..Orders a
cross join Northwind..Orders b
cross join Northwind..Orders c
go
ALTER TABLE klump ADD CONSTRAINT pk_klump PRIMARY KEY (klumpid)
go
CREATE INDEX orderidix ON klump (OrderID)
CREATE INDEX customerid ON klump (CustomerID)
go
SELECT getdate()
go
CREATE INDEX slaskix ON klump(slaskcol)
go
SELECT getdate()
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Although this happened twice on two machines when analyzing the upgrade
script via profiler -- our latest attempts to isolate what is happening
ended up not reproducing our earlier results. The indexes are building
in 2 minutes when tested in isolation outside the script. The only
operation which we have reliably reproduced as slow is adding a column
with a default value to a large table - which is taking on the order of
20 minutes in the million row range. We do this a number of times. We
don't yet have an explanation for why the script took so long, although
we are running the analysis one more time as I speak.
Sorry to have presented you with a problem that didn't reproduce - but
we were about to make some drastic changes based on the faulty
assumption that building million row indexes was much more expensive
then it really is. I appreciate the help you folks have provided!|||dfurtney (dfurtney@.hotmail.com) writes:
> Although this happened twice on two machines when analyzing the upgrade
> script via profiler -- our latest attempts to isolate what is happening
> ended up not reproducing our earlier results. The indexes are building
> in 2 minutes when tested in isolation outside the script. The only
> operation which we have reliably reproduced as slow is adding a column
> with a default value to a large table - which is taking on the order of
> 20 minutes in the million row range. We do this a number of times.
This sounds like a perfectly normal time for such an operation. Since
this column has to be copied into every row, the entire table has to
be rewritten. And unless every page bas bytes to spare for the new column,
you also get rows rearranged, and it is not a simple update in place.
In this case, it can sometimes be better to create a new table and
copy data over. (This in fact what we always do in our update scripts,
although that more has to do with the greater flexibility this
technique offers.)
> Sorry to have presented you with a problem that didn't reproduce - but
> we were about to make some drastic changes based on the faulty
> assumption that building million row indexes was much more expensive
> then it really is. I appreciate the help you folks have provided!
Oh, never mind! I think your concern was very valid, and I am glad to
have helped by telling you that it must have been a false alarm.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I suggest the real issue is:
> The only operation which we have reliably reproduced as slow is
adding a column
> with a default value to a large table - which is taking on the order
of
> 20 minutes in the million row range. We do this a number of times.
Why would you frequently add columns to a three million row table?
As far as selectivity goes, you don't gain any advantage to having a
default value of 0 vs. having a default value of NULL - it is a
disadvantage because you are frequently adding columns to a 3,000,000
row table. If the default value is NULL, SQL Server does not have to
rebuild the table when you add the value - NULL is nothing as far as
SQL Server is concerned.
The statistics histogram, based on only a select few companies having
this feature, would look something like:
_________________________||||Point well taken regarding NULL vs 0. The reason we use 0 instead of
NULL is because most of the software and reporting is "confused" by
nulls. MFC recordsets return the inconvenient tvalue of
#define AFX_RFX_LONG_PSEUDO_NULL (0x4a4d4120L)
for null integers unless you specifically write code to check for null.
We have a large, old codebase which has no null handling code.
This is a function/service pack, so we commonly add a number of new
fields to support new functionality. In our 3rd test, the upgrade
script took 3 hours - which is in the "normal" range. I guess we are
going to attribute the earlier results as anomalous for now and monitor
for another occurrence. The index is useless for customers not
utilizing the feature, however, if the cost is only 2 minutes, it is
easier to just add the index for all customers. For those customers
using the feature/index, it is highly selective.
