The other day I updated a table to add a few columns using Enterprise
Manager. When I closed the table, it said it would update several other
database items as a result (very good, I thought).
However, subsequent to this change, even the most basic view is broken, e.g.
,
CREATE VIEW [v_table] AS select * from [table]
Recreating (or altering) the view clear up the problem. But, I was surprised
!
What is the canonical way to avoid views getting out of sync with tables? Is
there another way to modify table structure that will automatically recompil
e
other database objects?
David> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
The best practice is to specify a column list rather than '*'. You can
execute sp_refreshview to refresh view meta data after changes to underlying
tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
> e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
> surprised!
> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
> David
>|||Create your views WITH SCHEMABINDING to avoid this problem. That will
"bind" the schema -- meaning that none of the underlying tables will be able
to change unless you drop the view.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
surprised!
> What is the canonical way to avoid views getting out of sync with tables?
Is
> there another way to modify table structure that will automatically
recompile
> other database objects?
> David
>|||And you can automate this refresh using something like:
-- Cycle through all view in the current database and refresh their metadata
-- to take into account any changes to the underlying objects.
DECLARE @.ViewName sysname
DECLARE views_to_refresh CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
OPEN views_to_refresh
FETCH NEXT FROM views_to_refresh INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
print 'Refreshing ' + @.ViewName
exec sp_refreshview @.ViewName
FETCH NEXT FROM views_to_refresh INTO @.ViewName
END
CLOSE views_to_refresh
DEALLOCATE views_to_refresh
Thanks!
David
"Dan Guzman" wrote:
> The best practice is to specify a column list rather than '*'. You can
> execute sp_refreshview to refresh view meta data after changes to underlyi
ng
> tables.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in messag
e
> news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
>
>sql
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Thursday, March 22, 2012
Changing a field value to NULL
I know the trick of using ctrl-0 to change it individually, but is there a
syntax that I can use as in the UPDATE to change a BUNCH of fields at the
same time. When changing fields I usually use the
UPDATE <table>
SET <field> = <parameter>
WHERE <field> = <parameter>
How do I do this to set a field as null where the parameter is 'No
Response'?
TIA
JCNever mind. I figured out what I was doing wrong. Dumb mistake.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:4F1F5AC7-E93B-4E0D-BA69-13AE90A90F71@.microsoft.com...
>I know the trick of using ctrl-0 to change it individually, but is there a
>syntax that I can use as in the UPDATE to change a BUNCH of fields at the
>same time. When changing fields I usually use the
> UPDATE <table>
> SET <field> = <parameter>
> WHERE <field> = <parameter>
> How do I do this to set a field as null where the parameter is 'No
> Response'?
> TIA
> JC
syntax that I can use as in the UPDATE to change a BUNCH of fields at the
same time. When changing fields I usually use the
UPDATE <table>
SET <field> = <parameter>
WHERE <field> = <parameter>
How do I do this to set a field as null where the parameter is 'No
Response'?
TIA
JCNever mind. I figured out what I was doing wrong. Dumb mistake.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:4F1F5AC7-E93B-4E0D-BA69-13AE90A90F71@.microsoft.com...
>I know the trick of using ctrl-0 to change it individually, but is there a
>syntax that I can use as in the UPDATE to change a BUNCH of fields at the
>same time. When changing fields I usually use the
> UPDATE <table>
> SET <field> = <parameter>
> WHERE <field> = <parameter>
> How do I do this to set a field as null where the parameter is 'No
> Response'?
> TIA
> JC
Sunday, March 11, 2012
change value in a identity column
How can I change the value in a identity column? I cannot use update to change its value.
Thank youIdentity columns can not be updated but you could get around this by doing the following:
--Allows you to enter your own value into the Identity column in
--your table
SET IDENTITY_INSERT [table name] ON
INSERT [table name] ([enter full column list here])
SELECT [New ID], [all other columns]
FROM [table name]
WHERE ID = [Old ID]
SET IDENTITY_INSERT [table name] OFF
DELETE
FROM [table name]
WHERE ID = [Old ID]
You might also want to put all this inside a transaction to ensure everything happens as you expect it too.|||Im just qurious..
Why do you want to change it?|||because i want to remove some old data in a identity enabled table, and i want to change the remind data's identity value back to starting from 1 to ...|||don't do it
see Gaps in autonumber sequences (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid576584_tax285649,00.html)
(registration may be required, but it's free)
just leave the gaps alone|||what he said.
for whatever reason you think you need them sequential you need to rethink.
If you'd like, post your reasons to this list and folks can help you find better ways to do what you are after....
Originally posted by r937
don't do it
....
just leave the gaps alone|||I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.|||Originally posted by cobraeyez
I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.
How many exams are taken a day? Consider BOL: an integer can have a value upto 2,147,483,647, a bigint upto 9,223,372,036,854,775,807. Is it really necessary to reset the identity field?|||Originally posted by cobraeyez
I dont want to let the Exam ID grow to too big value.
define "too big"
as Kaiowas suggested, it will be a long time before you run out of numbers
for example, if you add 1,000 new exams every day, guess how long you can keep adding without having to worry about it?
2,147,483,647 / 1000 = 2,147,487 days = 5879.5 years
again, please define "too big" and why it's too big
;)|||Just thought of another reason NOT to do this:
- The GAP indicates there used to be more exams;
- What would you do should you have have to readback from an archive?|||Why do you want to remove more then 3 months?
Why don't you want a large exam id? Is this something you show to the users?
I'd argue that users are used to large numbers they need to write down. I'd also argue that if Fred took a test 9 months ago, and he wanted information about it, you should be able to find it.
Big is a relative term. If you get more then 10,000,000 rows you need to look at your indexes again. Do you give 10,000,000 exams in a 3 month period?
Phone numbers are this long. People can remember them if they need to.
Originally posted by cobraeyez
I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.|||well, it seems I really don't need to. The original reason was I was using integer type in VB to retrieve the exam ID. Now I changed it to Long. It should be ok.
Thank for all your help
Thank youIdentity columns can not be updated but you could get around this by doing the following:
--Allows you to enter your own value into the Identity column in
--your table
SET IDENTITY_INSERT [table name] ON
INSERT [table name] ([enter full column list here])
SELECT [New ID], [all other columns]
FROM [table name]
WHERE ID = [Old ID]
SET IDENTITY_INSERT [table name] OFF
DELETE
FROM [table name]
WHERE ID = [Old ID]
You might also want to put all this inside a transaction to ensure everything happens as you expect it too.|||Im just qurious..
Why do you want to change it?|||because i want to remove some old data in a identity enabled table, and i want to change the remind data's identity value back to starting from 1 to ...|||don't do it
see Gaps in autonumber sequences (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid576584_tax285649,00.html)
(registration may be required, but it's free)
just leave the gaps alone|||what he said.
for whatever reason you think you need them sequential you need to rethink.
If you'd like, post your reasons to this list and folks can help you find better ways to do what you are after....
Originally posted by r937
don't do it
....
just leave the gaps alone|||I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.|||Originally posted by cobraeyez
I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.
How many exams are taken a day? Consider BOL: an integer can have a value upto 2,147,483,647, a bigint upto 9,223,372,036,854,775,807. Is it really necessary to reset the identity field?|||Originally posted by cobraeyez
I dont want to let the Exam ID grow to too big value.
define "too big"
as Kaiowas suggested, it will be a long time before you run out of numbers
for example, if you add 1,000 new exams every day, guess how long you can keep adding without having to worry about it?
2,147,483,647 / 1000 = 2,147,487 days = 5879.5 years
again, please define "too big" and why it's too big
;)|||Just thought of another reason NOT to do this:
- The GAP indicates there used to be more exams;
- What would you do should you have have to readback from an archive?|||Why do you want to remove more then 3 months?
Why don't you want a large exam id? Is this something you show to the users?
I'd argue that users are used to large numbers they need to write down. I'd also argue that if Fred took a test 9 months ago, and he wanted information about it, you should be able to find it.
Big is a relative term. If you get more then 10,000,000 rows you need to look at your indexes again. Do you give 10,000,000 exams in a 3 month period?
Phone numbers are this long. People can remember them if they need to.
Originally posted by cobraeyez
I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
And that is my case. Thanks for all suggestions.|||well, it seems I really don't need to. The original reason was I was using integer type in VB to retrieve the exam ID. Now I changed it to Long. It should be ok.
Thank for all your help
Thursday, March 8, 2012
Change Type greyed out in 2k5 mgmt studio
in 2000 ent mgr, I can right click on a table/Query/perform a query and then
"Change Type" to update or whatever.
In 2k5 mgmt studio (against a 2000 database) I can arrange the panes to look
the same and perform the same query.
But Change Type is greyed out (on the screen). For what its work the icon
isnt there, but the words "Change Type".
Its enabled (checked) - well, I forget how I got there, but it was checked.
If I click on a table in the object explorer, its no longer grey-out, but
all the options are (which makes sense).
So - how can I Change Type graphically on a query?
You may need to logon but add your vote and comment here.
http://connect.microsoft.com/SQLServer
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124911
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"SandpointGuy" wrote:
> in 2000 ent mgr, I can right click on a table/Query/perform a query and
> then
> "Change Type" to update or whatever.
> In 2k5 mgmt studio (against a 2000 database) I can arrange the panes to
> look
> the same and perform the same query.
> But Change Type is greyed out (on the screen). For what its work the icon
> isnt there, but the words "Change Type".
> Its enabled (checked) - well, I forget how I got there, but it was
> checked.
> If I click on a table in the object explorer, its no longer grey-out, but
> all the options are (which makes sense).
> So - how can I Change Type graphically on a query?
|||I don't use the designer tools but when I played around with
it, it looks like you have to be in the Query Designer to
get that functionality.
1. From the toolbar, select new query
2. On the new query window, right click and select Design
Query in Editor
You can right click on the panes in this designer and select
Change Type.
-Sue
On Fri, 10 Aug 2007 10:50:01 -0700, SandpointGuy
<SandpointGuy@.discussions.microsoft.com> wrote:
>in 2000 ent mgr, I can right click on a table/Query/perform a query and then
>"Change Type" to update or whatever.
>In 2k5 mgmt studio (against a 2000 database) I can arrange the panes to look
>the same and perform the same query.
>But Change Type is greyed out (on the screen). For what its work the icon
>isnt there, but the words "Change Type".
>Its enabled (checked) - well, I forget how I got there, but it was checked.
>If I click on a table in the object explorer, its no longer grey-out, but
>all the options are (which makes sense).
>So - how can I Change Type graphically on a query?
"Change Type" to update or whatever.
In 2k5 mgmt studio (against a 2000 database) I can arrange the panes to look
the same and perform the same query.
But Change Type is greyed out (on the screen). For what its work the icon
isnt there, but the words "Change Type".
Its enabled (checked) - well, I forget how I got there, but it was checked.
If I click on a table in the object explorer, its no longer grey-out, but
all the options are (which makes sense).
So - how can I Change Type graphically on a query?
You may need to logon but add your vote and comment here.
http://connect.microsoft.com/SQLServer
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124911
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"SandpointGuy" wrote:
> in 2000 ent mgr, I can right click on a table/Query/perform a query and
> then
> "Change Type" to update or whatever.
> In 2k5 mgmt studio (against a 2000 database) I can arrange the panes to
> look
> the same and perform the same query.
> But Change Type is greyed out (on the screen). For what its work the icon
> isnt there, but the words "Change Type".
> Its enabled (checked) - well, I forget how I got there, but it was
> checked.
> If I click on a table in the object explorer, its no longer grey-out, but
> all the options are (which makes sense).
> So - how can I Change Type graphically on a query?
|||I don't use the designer tools but when I played around with
it, it looks like you have to be in the Query Designer to
get that functionality.
1. From the toolbar, select new query
2. On the new query window, right click and select Design
Query in Editor
You can right click on the panes in this designer and select
Change Type.
-Sue
On Fri, 10 Aug 2007 10:50:01 -0700, SandpointGuy
<SandpointGuy@.discussions.microsoft.com> wrote:
>in 2000 ent mgr, I can right click on a table/Query/perform a query and then
>"Change Type" to update or whatever.
>In 2k5 mgmt studio (against a 2000 database) I can arrange the panes to look
>the same and perform the same query.
>But Change Type is greyed out (on the screen). For what its work the icon
>isnt there, but the words "Change Type".
>Its enabled (checked) - well, I forget how I got there, but it was checked.
>If I click on a table in the object explorer, its no longer grey-out, but
>all the options are (which makes sense).
>So - how can I Change Type graphically on a query?
Change Tracking VS Update Index
When creating a new Table Schedule, there is an option for a Job Type of
"Update Index". I though this was Change Tracking, but it doesn't seem to
be. I still had to manually select Change Tracking from the full text menu.
What is Update Index?
Thanks
It is, but change tracking has two modes:
1) update index is a scheduled update, ie you can update the index on demand
or as a scheduled job
2) update index in background - ie have it happen continuously
"Don Schilling" <fake@.ReplyToGroup.com> wrote in message
news:OMa3r$2PEHA.648@.TK2MSFTNGP10.phx.gbl...
> When creating a new Table Schedule, there is an option for a Job Type of
> "Update Index". I though this was Change Tracking, but it doesn't seem to
> be. I still had to manually select Change Tracking from the full text
menu.
> What is Update Index?
> Thanks
"Update Index". I though this was Change Tracking, but it doesn't seem to
be. I still had to manually select Change Tracking from the full text menu.
What is Update Index?
Thanks
It is, but change tracking has two modes:
1) update index is a scheduled update, ie you can update the index on demand
or as a scheduled job
2) update index in background - ie have it happen continuously
"Don Schilling" <fake@.ReplyToGroup.com> wrote in message
news:OMa3r$2PEHA.648@.TK2MSFTNGP10.phx.gbl...
> When creating a new Table Schedule, there is an option for a Job Type of
> "Update Index". I though this was Change Tracking, but it doesn't seem to
> be. I still had to manually select Change Tracking from the full text
menu.
> What is Update Index?
> Thanks
Sunday, February 12, 2012
Change Owner Dts Package
Hi, i want to change owner to my dts package.
Can i do this problem to this procedure?
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
update sysdtspackages set owner = 'newname'
where owner ='oldname'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Because in this case i update all old name.
I also test sp_reassign_dtspackageowner but i don't know how this comand to do on the table?
Can i do this problem to this procedure?
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
update sysdtspackages set owner = 'newname'
where owner ='oldname'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Because in this case i update all old name.
I also test sp_reassign_dtspackageowner but i don't know how this comand to do on the table?
Thanks
Don't bother is my advice, it is not permanent and largely cosmetic.
Package Ownership Issues
(http://www.sqldts.com/default.aspx?212)
Friday, February 10, 2012
Change Notifications
Hello
I have a requirement to update the Exchange contacts folder according
to a table in MSSQL 200. Also changes in the db has to be updated in
the contacts.
The solution that I have thought about is to write a trigger and
contact a COM object that does the update. Is there a better way to do?
Thank you and regards
Renjith V.I helped someone with a similar requirement a while back. We ended up using
DAV and a Windows Service that checked the table to do the updates en masse
every 5 minutes or so. Depends on your requirements - if you need to have
immediate updates you could write an XP and avoid COM; or you could go the
COM route and use the 'sp_OA%' stored procs to invoke/manage it. I've never
invoked a COM routine from within a Trigger, however, so I couldn't tell you
about possible performance or other potential problems (although if you
don't properly destroy COM objects and/or you're not on SP 4, you can end up
with serious memory leaks.)
"Renjith" <v.renjith@.gmail.com> wrote in message
news:1150338308.044717.160160@.y41g2000cwy.googlegroups.com...
> Hello
> I have a requirement to update the Exchange contacts folder according
> to a table in MSSQL 200. Also changes in the db has to be updated in
> the contacts.
> The solution that I have thought about is to write a trigger and
> contact a COM object that does the update. Is there a better way to do?
> Thank you and regards
> Renjith V.
>|||Thank you Mike for the comments. But I am not sure what DAV and XP
mean. Could you please tell me a bit more about it?
Mike C# wrote:
> I helped someone with a similar requirement a while back. We ended up usi
ng
> DAV and a Windows Service that checked the table to do the updates en mass
e
> every 5 minutes or so. Depends on your requirements - if you need to have
> immediate updates you could write an XP and avoid COM; or you could go the
> COM route and use the 'sp_OA%' stored procs to invoke/manage it. I've nev
er
> invoked a COM routine from within a Trigger, however, so I couldn't tell y
ou
> about possible performance or other potential problems (although if you
> don't properly destroy COM objects and/or you're not on SP 4, you can end
up
> with serious memory leaks.)
> "Renjith" <v.renjith@.gmail.com> wrote in message
> news:1150338308.044717.160160@.y41g2000cwy.googlegroups.com...|||"Renjith" <v.renjith@.gmail.com> wrote in message
news:1150339498.386101.227740@.g10g2000cwb.googlegroups.com...
> Thank you Mike for the comments. But I am not sure what DAV and XP
> mean. Could you please tell me a bit more about it?
DAV is a web-based protocol you can use to retrieve and update information.
You can use it to update your Exchange folders. XP is an extended stored
procedure written in C++ and compiled to a DLL that SQL Server can invoke to
perform functions that are outside the normal scope of T-SQL.|||If you can update the contacts through Active Directory you can look linking
to the AD through VB/VBScript (DTS ActiveX Task), you could also link to SQL
Server through the same script, produce a recordset of the changes and apply
the changes to the AD.
If this is an option the best place to start would probably be:
http://www.microsoft.com/technet/sc...er/default.mspx
Ray
"Renjith" wrote:
> Hello
> I have a requirement to update the Exchange contacts folder according
> to a table in MSSQL 200. Also changes in the db has to be updated in
> the contacts.
> The solution that I have thought about is to write a trigger and
> contact a COM object that does the update. Is there a better way to do?
> Thank you and regards
> Renjith V.
>
I have a requirement to update the Exchange contacts folder according
to a table in MSSQL 200. Also changes in the db has to be updated in
the contacts.
The solution that I have thought about is to write a trigger and
contact a COM object that does the update. Is there a better way to do?
Thank you and regards
Renjith V.I helped someone with a similar requirement a while back. We ended up using
DAV and a Windows Service that checked the table to do the updates en masse
every 5 minutes or so. Depends on your requirements - if you need to have
immediate updates you could write an XP and avoid COM; or you could go the
COM route and use the 'sp_OA%' stored procs to invoke/manage it. I've never
invoked a COM routine from within a Trigger, however, so I couldn't tell you
about possible performance or other potential problems (although if you
don't properly destroy COM objects and/or you're not on SP 4, you can end up
with serious memory leaks.)
"Renjith" <v.renjith@.gmail.com> wrote in message
news:1150338308.044717.160160@.y41g2000cwy.googlegroups.com...
> Hello
> I have a requirement to update the Exchange contacts folder according
> to a table in MSSQL 200. Also changes in the db has to be updated in
> the contacts.
> The solution that I have thought about is to write a trigger and
> contact a COM object that does the update. Is there a better way to do?
> Thank you and regards
> Renjith V.
>|||Thank you Mike for the comments. But I am not sure what DAV and XP
mean. Could you please tell me a bit more about it?
Mike C# wrote:
> I helped someone with a similar requirement a while back. We ended up usi
ng
> DAV and a Windows Service that checked the table to do the updates en mass
e
> every 5 minutes or so. Depends on your requirements - if you need to have
> immediate updates you could write an XP and avoid COM; or you could go the
> COM route and use the 'sp_OA%' stored procs to invoke/manage it. I've nev
er
> invoked a COM routine from within a Trigger, however, so I couldn't tell y
ou
> about possible performance or other potential problems (although if you
> don't properly destroy COM objects and/or you're not on SP 4, you can end
up
> with serious memory leaks.)
> "Renjith" <v.renjith@.gmail.com> wrote in message
> news:1150338308.044717.160160@.y41g2000cwy.googlegroups.com...|||"Renjith" <v.renjith@.gmail.com> wrote in message
news:1150339498.386101.227740@.g10g2000cwb.googlegroups.com...
> Thank you Mike for the comments. But I am not sure what DAV and XP
> mean. Could you please tell me a bit more about it?
DAV is a web-based protocol you can use to retrieve and update information.
You can use it to update your Exchange folders. XP is an extended stored
procedure written in C++ and compiled to a DLL that SQL Server can invoke to
perform functions that are outside the normal scope of T-SQL.|||If you can update the contacts through Active Directory you can look linking
to the AD through VB/VBScript (DTS ActiveX Task), you could also link to SQL
Server through the same script, produce a recordset of the changes and apply
the changes to the AD.
If this is an option the best place to start would probably be:
http://www.microsoft.com/technet/sc...er/default.mspx
Ray
"Renjith" wrote:
> Hello
> I have a requirement to update the Exchange contacts folder according
> to a table in MSSQL 200. Also changes in the db has to be updated in
> the contacts.
> The solution that I have thought about is to write a trigger and
> contact a COM object that does the update. Is there a better way to do?
> Thank you and regards
> Renjith V.
>
change nc-index to c-index?
Found out a while back that my facts-tabel has an non-clustered index on its facts_id. In a bunch of procedures an update is executed against a facts_id unfortunately on it's facts-table. I was wondering if changing it into a clustered index is worth the effort / would make sense considering a +110 million facts and re-indexing the other indexes as well? Facts are loaded sequentially, so I would suspect them facts are in the ordered already?
thanx,Is update executed against facts_id itself or against the table "where facts_id=<whatever>"? If it's the first, then you have a lot of page splits in your index pages, but rebuilding non-clustered index with so many rows is much less destructive than if you had a clustered index there.|||the updates are like:
update facts
set name = value
where facts_id = @.facts_id
so I can expect only few page splits (is there a way to count 'em?).
There's no update on the facts_id itself and it's unlikely it'll happen in the future which, I guess, makes the facts_id even more attractive for a clustered index.
I'll have to rebuild the other indexes as well though but I guess that's okay.
I saw the facts has a bunch of statistics, my dev does not have this nor do the other warehouses that are derived from prod. Could this be a problem? If I'd throw 'em out (delete 'em), would sql rebuild them by itself?|||It will if Auto Create Statistics is on. It's a database setting.
thanx,Is update executed against facts_id itself or against the table "where facts_id=<whatever>"? If it's the first, then you have a lot of page splits in your index pages, but rebuilding non-clustered index with so many rows is much less destructive than if you had a clustered index there.|||the updates are like:
update facts
set name = value
where facts_id = @.facts_id
so I can expect only few page splits (is there a way to count 'em?).
There's no update on the facts_id itself and it's unlikely it'll happen in the future which, I guess, makes the facts_id even more attractive for a clustered index.
I'll have to rebuild the other indexes as well though but I guess that's okay.
I saw the facts has a bunch of statistics, my dev does not have this nor do the other warehouses that are derived from prod. Could this be a problem? If I'd throw 'em out (delete 'em), would sql rebuild them by itself?|||It will if Auto Create Statistics is on. It's a database setting.
Subscribe to:
Posts (Atom)