do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

Do a lot of linked tables cause block?

Hello, everyone:
There are a lot of Access and Excel tables linked to my SQL Server (SQL2K SP3 on W2K). The end users update those likned tables. I am wondering if there is the block problem. If yes, how to prevent that? Thanks.
ZYTNo, It should not cause any problems. How did you bring it into sql2k

DMO reports product level as sp3 instead of sp3a

Hi,
1. I am using the "ProductLevel" property of the "SQLServer2" SQL DMO object
to determine the product level of my SQL server 2000 installation. However
even when I have installed "SQL Server 2000 sp3a" on my machine, this
property still reports the server product level as sp3 (instead of sp3a). Is
there a workaround for this using which I can get the exact product level?
2. I am using the "xp_msver" sproc to get the SQL server version. The SQL
server 2000 installation with sp3 installed has version no= 8.0.760. This
version is reported by xp_msver. However when "sp3a" is installed on this,
the version is still reported as "8.0.760" when it should be "8.0.761". Is
this a bug in the execution of xp_msver?
Plz reply ASAP.
ThanxOn Mon, 14 Mar 2005 12:42:16 +0530, Onkar Walavalkar wrote:
(snip)
Hi Onkar,
As far as I know, SP3a and SP3 are the same WRT the server part; the
only differences are in the client tools. That's why the server will
never report SP3a, and that's why both SP3 and SP3a correspond to
version 8.00.760.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

DLL and Xtended Proc...

Hello,
I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL server
2K SP3. I've a class module with only one public funtion in it.
I want to use the function in this dll so I decalred my DLL as an extended
proc in SQL . apprently SQL doesn't recognize the function in my dll. How
can I use it? What's the best way to do it?
Best regards,I'm not sure if a VB6 DLL can be registered as an extended stored procedure
this way.
If you want to use the functionality of your DLL from SQL Server, you could
use the sp_OA* set of procedures for the same. Refer to
http://www32.brinkster.com/srisamp/sqlarchives.asp for articles on COM
accessibility. You can refer to the articles titled "Using COM Objects in
SQL Server" and "Extending SQL Server with COM Objects".
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Philippe RUELLO" <pruello@.tibco.fr> wrote in message
news:OMjpLlYuDHA.2712@.tk2msftngp13.phx.gbl...
> Hello,
> I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL
server
> 2K SP3. I've a class module with only one public funtion in it.
> I want to use the function in this dll so I decalred my DLL as an extended
> proc in SQL . apprently SQL doesn't recognize the function in my dll. How
> can I use it? What's the best way to do it?
> Best regards,
>|||Just to confirm - VB only produces COM type .dlls, not call level type which
are required for extended stored procedures. So you cannot register VB .dlls
for use as extended stored procedures in SQL Server.
The other method that SriSamp has suggested (sp_OACreate) is the only way to
access your VB .dll directly from T-SQL.
Regards,
Greg Linwood
SQL Server MVP
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:#j7#xrYuDHA.1576@.TK2MSFTNGP11.phx.gbl...
> I'm not sure if a VB6 DLL can be registered as an extended stored
procedure
> this way.
> If you want to use the functionality of your DLL from SQL Server, you
could
> use the sp_OA* set of procedures for the same. Refer to
> http://www32.brinkster.com/srisamp/sqlarchives.asp for articles on COM
> accessibility. You can refer to the articles titled "Using COM Objects in
> SQL Server" and "Extending SQL Server with COM Objects".
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Philippe RUELLO" <pruello@.tibco.fr> wrote in message
> news:OMjpLlYuDHA.2712@.tk2msftngp13.phx.gbl...
> > Hello,
> > I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL
> server
> > 2K SP3. I've a class module with only one public funtion in it.
> > I want to use the function in this dll so I decalred my DLL as an
extended
> > proc in SQL . apprently SQL doesn't recognize the function in my dll.
How
> > can I use it? What's the best way to do it?
> > Best regards,
> >
> >
>|||Thanks for the answer.
I tried to use that methos but i've some troubles using the attachements
with CDONT.Newmail
I could use Xp_sendmail, but I want to modify the parameter From.
Have some clues?
"SriSamp" <ssampath@.sct.co.in> a écrit dans le message de
news:%23j7%23xrYuDHA.1576@.TK2MSFTNGP11.phx.gbl...
> I'm not sure if a VB6 DLL can be registered as an extended stored
procedure
> this way.
> If you want to use the functionality of your DLL from SQL Server, you
could
> use the sp_OA* set of procedures for the same. Refer to
> http://www32.brinkster.com/srisamp/sqlarchives.asp for articles on COM
> accessibility. You can refer to the articles titled "Using COM Objects in
> SQL Server" and "Extending SQL Server with COM Objects".
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Philippe RUELLO" <pruello@.tibco.fr> wrote in message
> news:OMjpLlYuDHA.2712@.tk2msftngp13.phx.gbl...
> > Hello,
> > I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL
> server
> > 2K SP3. I've a class module with only one public funtion in it.
> > I want to use the function in this dll so I decalred my DLL as an
extended
> > proc in SQL . apprently SQL doesn't recognize the function in my dll.
How
> > can I use it? What's the best way to do it?
> > Best regards,
> >
> >
>|||How about xp_smtp_sendmail from www.SQLDev.Net?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Philippe RUELLO" <pruello@.tibco.fr> wrote in message news:uXn1yUZuDHA.2408@.tk2msftngp13.phx.gbl...
> Thanks for the answer.
> I tried to use that methos but i've some troubles using the attachements
> with CDONT.Newmail
> I could use Xp_sendmail, but I want to modify the parameter From.
> Have some clues?
> "SriSamp" <ssampath@.sct.co.in> a écrit dans le message de
> news:%23j7%23xrYuDHA.1576@.TK2MSFTNGP11.phx.gbl...
> > I'm not sure if a VB6 DLL can be registered as an extended stored
> procedure
> > this way.
> > If you want to use the functionality of your DLL from SQL Server, you
> could
> > use the sp_OA* set of procedures for the same. Refer to
> > http://www32.brinkster.com/srisamp/sqlarchives.asp for articles on COM
> > accessibility. You can refer to the articles titled "Using COM Objects in
> > SQL Server" and "Extending SQL Server with COM Objects".
> > --
> > HTH,
> > SriSamp
> > Please reply to the whole group only!
> > http://www32.brinkster.com/srisamp
> >
> > "Philippe RUELLO" <pruello@.tibco.fr> wrote in message
> > news:OMjpLlYuDHA.2712@.tk2msftngp13.phx.gbl...
> > > Hello,
> > > I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL
> > server
> > > 2K SP3. I've a class module with only one public funtion in it.
> > > I want to use the function in this dll so I decalred my DLL as an
> extended
> > > proc in SQL . apprently SQL doesn't recognize the function in my dll.
> How
> > > can I use it? What's the best way to do it?
> > > Best regards,
> > >
> > >
> >
> >
>

Distribution Data File Growth

Recently rebuilt Windows 2003 OS, SQL Server 2000 sp3, 3 publishers of
various shapes and sizes.
I am almost certain that I re-created the distribution database using the
file properties before everything was moved to the new OS. I ended up having
to drop and recreate replication because I didn't back up the publishers
with the keep_replication switch. So, I dropped the distribution database
and created a new one. But, for some reason the data file seems to be
growing and growing. This behavior is unexpected. How can I determine the
cause of this growth? The subscribers are certainly receiving the
transactions. We have other sql servers with multiple publishers and a
single distribution database but the data file for it stays small.
Michelle,
have a look at the msrepl_commands table and see if this is the cause of the
large size. If it is, it could be that you have a subscriber who hasn't
synchronized in a while, or the distribution cleanup agent is disabled, or
you have an anonymous subscriber, so the commands remain until the retention
period is reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||run a dbcc opentran in your distributon database to see what happens.
The keep replication switch was designed to be enable disaster recovery
of your transactional replication solution. You can use it to restore
publications on a server but only so you can script out the
publications or view them. Don't expect to use the keep_replication
swithc on a new server and have everything work. You need to restore
the distribution, master, and msdb databases as well.
|||Thanks. I had all of the pieces (msdb, distribution, master, etc.). All
databases were restored but since I didn't back up the publishers with the
keep_replication switch, I couldn't get replication 'kicked off' again. I
re-marked them after the restore for replication and all of the jobs were
succeeding. However, the log reader was not finding any transactions.
No open transactions in distribution. I'll see where Paul's suggestion leads
me...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113509425.813891.157720@.z14g2000cwz.googlegr oups.com...
> run a dbcc opentran in your distributon database to see what happens.
> The keep replication switch was designed to be enable disaster recovery
> of your transactional replication solution. You can use it to restore
> publications on a server but only so you can script out the
> publications or view them. Don't expect to use the keep_replication
> swithc on a new server and have everything work. You need to restore
> the distribution, master, and msdb databases as well.
>
|||Results from msrepl_commands table:
publisher db_id count (xact_seqno)
1 20867
2 1002769
3 159454
Ran distribution clean up agent job (has been running successfully, every 10
minutes):
publisher db_id count (xact_seqno)
1 20866
2 996394
3 158467
The counts are all lower. I added an output file to the job which states:
Removed 74 replicated transactions consisting of 245 statements in 30
seconds (10 rows/sec). Retention max looks to be set at 72 hours (default, I
assume - I don't think that we changed this in the old system).
I'll just keep monitoring this for now. Maybe a 1 GB data file for this
distribution database is not out of line and I no longer have access to the
old system to compare anything.
Thanks,
Michelle
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uWKIq2SQFHA.248@.TK2MSFTNGP15.phx.gbl...
> Michelle,
> have a look at the msrepl_commands table and see if this is the cause of
the
> large size. If it is, it could be that you have a subscriber who hasn't
> synchronized in a while, or the distribution cleanup agent is disabled, or
> you have an anonymous subscriber, so the commands remain until the
retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Well over a million records is quite a lot, but I'd be surprised if this
amounts to 1GB. Running, sp_spaceused will give the exact ratio of empty to
used space in the database. If the subscriber(s) have synchronized and are
up to date, you could reduce the retention period and run the cleanup agent
to remove a big chunk of this data but this will only work if anonymous
subscribers aren't enabled.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distributed transaction aborted by MSDTC

hi,
i'm implementing a distributed database(partitioned view) system using 3
linked MSSQL 2000 server(SP3),
when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN, I
get the error "Distributed transaction aborted by MSDTC", or sometime
"Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction."
the http://support.microsoft.com/?kbid=834849 says this only happened if one
of the linked server is MSSQL Server 7.0, but all my servers are 2000 Server
with SP3, so can somebody help to resolve this?
If you are running Windows XP or Windows Server 2003 you can use DTC tracing
to find out why the transaction aborts, otherwise you can use SQL Profiler
and include the DTC transaction event class.
Do you have a trigger on one of the tables that is involved in the
transaction?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"eugeneng" <eugeneng@.discussions.microsoft.com> wrote in message
news:C4FC5E8E-B128-459D-9914-78B8CE2AD795@.microsoft.com...
> hi,
> i'm implementing a distributed database(partitioned view) system using 3
> linked MSSQL 2000 server(SP3),
> when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN,
> I
> get the error "Distributed transaction aborted by MSDTC", or sometime
> "Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction."
> the http://support.microsoft.com/?kbid=834849 says this only happened if
> one
> of the linked server is MSSQL Server 7.0, but all my servers are 2000
> Server
> with SP3, so can somebody help to resolve this?
>
>

Distributed transaction aborted by MSDTC

hi,
i'm implementing a distributed database(partitioned view) system using 3
linked MSSQL 2000 server(SP3),
when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN, I
get the error "Distributed transaction aborted by MSDTC", or sometime
"Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction."
the http://support.microsoft.com/?kbid=834849 says this only happened if one
of the linked server is MSSQL Server 7.0, but all my servers are 2000 Server
with SP3, so can somebody help to resolve this?If you are running Windows XP or Windows Server 2003 you can use DTC tracing
to find out why the transaction aborts, otherwise you can use SQL Profiler
and include the DTC transaction event class.
Do you have a trigger on one of the tables that is involved in the
transaction?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"eugeneng" <eugeneng@.discussions.microsoft.com> wrote in message
news:C4FC5E8E-B128-459D-9914-78B8CE2AD795@.microsoft.com...
> hi,
> i'm implementing a distributed database(partitioned view) system using 3
> linked MSSQL 2000 server(SP3),
> when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN,
> I
> get the error "Distributed transaction aborted by MSDTC", or sometime
> "Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction."
> the http://support.microsoft.com/?kbid=834849 says this only happened if
> one
> of the linked server is MSSQL Server 7.0, but all my servers are 2000
> Server
> with SP3, so can somebody help to resolve this?
>
>

Distributed transaction aborted by MSDTC

hi,
i'm implementing a distributed database(partitioned view) system using 3
linked MSSQL 2000 server(SP3),
when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN, I
get the error "Distributed transaction aborted by MSDTC", or sometime
"Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction."
the http://support.microsoft.com/?kbid=834849 says this only happened if one
of the linked server is MSSQL Server 7.0, but all my servers are 2000 Server
with SP3, so can somebody help to resolve this?If you are running Windows XP or Windows Server 2003 you can use DTC tracing
to find out why the transaction aborts, otherwise you can use SQL Profiler
and include the DTC transaction event class.
Do you have a trigger on one of the tables that is involved in the
transaction?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"eugeneng" <eugeneng@.discussions.microsoft.com> wrote in message
news:C4FC5E8E-B128-459D-9914-78B8CE2AD795@.microsoft.com...
> hi,
> i'm implementing a distributed database(partitioned view) system using 3
> linked MSSQL 2000 server(SP3),
> when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN,
> I
> get the error "Distributed transaction aborted by MSDTC", or sometime
> "Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction."
> the http://support.microsoft.com/?kbid=834849 says this only happened if
> one
> of the linked server is MSSQL Server 7.0, but all my servers are 2000
> Server
> with SP3, so can somebody help to resolve this?
>
>