How to schedule Usage Based Optimization on a regular basis?

I want to perform Usage Based Optimization on given partitions automatically (not using the wizard). Is it possible to schedule this task and how?

I don't know of anyway "out of the box" that this can be done. You could write your own program using AMO to facilitate this, but there are a couple of issues that come to mind.

1) You should not need to continuously re-run the usage based optimizations unless the useage patterns are changing significantly. Optimizing a cube for a given usage pattern only needs to be done once. Once the aggregations have been designed they will be used every time the cube is reprocessed (or specifically when the index processing is done)

2) There is an issue with UBO in that it overwrites previously designed aggregations (see this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=806335&SiteID=1) so your performance could possibly regress, rather than incrementally improve, if you ran it unattended.

|||Thanks a lotsql

How to run a stored procedure by schedule

Is there a way to run a stored procedure based on schedule like every 8p.m everyday?schedule a Job, in this job executes your Stored Procedure:

in the option tree of Enterprise management, you selects: Management -> SQL Server Agent -> Jobs, create a new job, in the second tab select :

Type = Transact-SQL Script
DataBase = the database of the Stored Procedure
Command = You Stored Procedure

In the next tab "schedule" select the option "Recurring" and press button "Change"
and it programs your schedule.

Note: The SQL Server Agent has to be working.

How to return a partial string based on a particular character?

Hi,
I am looking through books on-line but an not finding what I am looking for.
In my stored proc, I am being passed a varchar field, 20 long. It looks
something like, '103098-1'
I need to split the characters on the left side of the '-' into one field,
and the characters on the right side of the '-' into another field.
How do I do this?
Thanks,
Steve
This is how I did it, does this make sense, or is there an easier way?
Declare @.strOrder varchar(20)
set @.strOrder = '38372-1'
set @.charIndex = CHARINDEX('-', @.strOrder)
set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
@.charIndex))
Thanks again.
"SteveInBeloit" wrote:

> Hi,
> I am looking through books on-line but an not finding what I am looking for.
> In my stored proc, I am being passed a varchar field, 20 long. It looks
> something like, '103098-1'
> I need to split the characters on the left side of the '-' into one field,
> and the characters on the right side of the '-' into another field.
> How do I do this?
> Thanks,
> Steve
|||yes, it could be done in a single line though.
Declare @.strOrder varchar(20)
declare @.left varchar(10)
declare @.right varchar(10)
set @.strOrder = '38372-1'
select @.left = left(@.strOrder, CHARINDEX('-', @.strOrder)-1),
@.right=substring(@.strOrder,
charindex('-',@.strOrder)+1,len(@.strOrder)-charindex('-',@.strOrder)+1)
print @.left
print @.right
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
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:A9247AFD-686A-46BE-AE7B-225E813FBCA1@.microsoft.com...[vbcol=seagreen]
> This is how I did it, does this make sense, or is there an easier way?
> Declare @.strOrder varchar(20)
> set @.strOrder = '38372-1'
> set @.charIndex = CHARINDEX('-', @.strOrder)
> set @.Orderin = CONVERT(int, LEFT(@.strOrder, @.charIndex - 1))
> Set @.linein = CONVERT(int, SUBSTRING(@.strOrder, @.charIndex + 1, 20 -
> @.charIndex))
> Thanks again.
> "SteveInBeloit" wrote:
for.[vbcol=seagreen]
looks[vbcol=seagreen]
field,[vbcol=seagreen]

How to restrict access to database to only IUSR_<machinename>

Hello Mark,
Based on the problem description, it seems that you'd like to let all the
application which is running internally can access SQL server successfully.
However, outside users cannot access SQL server from internet.
If this is what you want, you can remove TCP/IP and Named Pipes in the "SQL
Server Network Utility." You can follow the steps below:
a. On the Microsoft SQL Server 2000 server, start the SQL Server Network
Utility.
b. Click the General tab, and then select the instance you want from the
Instances drop-down menu.
c. Highlight TCP/IP, and then click Disable.
d. Highlight Named Pipes, and then click Disable.
By doing so, users still can access SQL server internally instead of
internet.
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.I think I boo-boo'd
I removed TCP/IP and Named Pipes from the SQL Server Registration from
within Enterprise Manager and rebooted the machine.
Now the SQL Server agent won't start, and therefore I can't get into the SQL
Server Registration again since the agent isn't running.
The SQLServerAgent is stopped, and when I attempt to start it, it just
starts and immediately stops again.
The event viewer shows the following:
SQLServerAgent could not be started (reason: Unable to connect to server
'MYSERVER\SERVER'; SQLServerAgent cannot start).
Is there anything I can do to recover?
Thanks!
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:OWhx%23YW%23EHA.3360@.cpmsftngxa10.phx.gbl...
> Hello Mark,
> Based on the problem description, it seems that you'd like to let all the
> application which is running internally can access SQL server
> successfully.
> However, outside users cannot access SQL server from internet.
> If this is what you want, you can remove TCP/IP and Named Pipes in the
> "SQL
> Server Network Utility." You can follow the steps below:
> a. On the Microsoft SQL Server 2000 server, start the SQL Server Network
> Utility.
> b. Click the General tab, and then select the instance you want from the
> Instances drop-down menu.
> c. Highlight TCP/IP, and then click Disable.
> d. Highlight Named Pipes, and then click Disable.
>
> By doing so, users still can access SQL server internally instead of
> internet.
> I hope above information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comHi
I am not totally sure what you require, as it sounds like a simple update
statement would do what you require e.g.
UPDATE B
SET [Three] = A.[Three]
FROM TABLEB B
JOIN TABLEA A ON A.id = B.id and B.id in ( 11, 12, 13 )
If you do choose to BCP the data out then your would probably need to load
the data into a staging table.
You may want to show what the final states of table a and b are.
John
"vinoth@.gsdindia.com" wrote:

> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comYou don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =
B.ID)
where
ID in (11, 12, 13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegroups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility
]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
---
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
---
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.comYou don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> ---
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> ---
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =B.ID)
where
ID in (11, 12, 13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegroups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegroups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>

How to restore Values to Table based on some Query

Hi,
I have a table called A and another Table Called B.
A table has the following Column and Values
ID One Two Three
10 test01 test02 test03
11 test11 test12 test13
12 test21 test22 test23
13 test31 test32 test33
B table has the following Column and Values
ID One Two Three
10 abcd01 abcd02 abcd03
11 abcd11 abcd12 abcd13
12 abcd21 abcd22
13 abcd31 abcd32 abcd33
I want to take a backup of Column Three where ID in(11,12,13). I know
using BCP utility i can take a backup of Table A of Column Three.
Following is the Query
"select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
c:\try.txt -U sa -P ***
and then i want to restore it in to the Table B's Column Three by
specifying some Query. Meaning i want to restore the values to the
Table B's Column Three where ID is the ID which is obtained in
Backup[Table A]. How can i do this? If anybody knows the solution
Please Let me know to solve it.
Thanks,
Vinoth
Vinoth@.gsdindia.com
You don't need to extract it to a file. If you want to update B from A,
just do:
update B
set
Three = (select A.Three from A where A.ID = B.ID)
where
ID in (11, 12, 13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131797987.096680.189410@.g14g2000cwa.googlegr oups.com...
> Hi,
>
> I have a table called A and another Table Called B.
>
> A table has the following Column and Values
>
> ID One Two Three
> 10 test01 test02 test03
> 11 test11 test12 test13
> 12 test21 test22 test23
> 13 test31 test32 test33
>
> B table has the following Column and Values
>
> ID One Two Three
> 10 abcd01 abcd02 abcd03
> 11 abcd11 abcd12 abcd13
> 12 abcd21 abcd22
> 13 abcd31 abcd32 abcd33
>
> I want to take a backup of Column Three where ID in(11,12,13). I know
> using BCP utility i can take a backup of Table A of Column Three.
> Following is the Query
>
> "select Three,ID from [Test].dbo.A where ID in(11,12,13)" queryout
> c:\try.txt -U sa -P ***
>
> and then i want to restore it in to the Table B's Column Three by
> specifying some Query. Meaning i want to restore the values to the
> Table B's Column Three where ID is the ID which is obtained in
> Backup[Table A]. How can i do this? If anybody knows the solution
> Please Let me know to solve it.
>
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>
|||Thanks for ur reply,
The table A will be in One database of Some Server and Table B will
be in Some other Database of Some other Server. How can i do it now?
Thanks,
Vinoth
|||Use 4-part naming (after adding a linked server):
update B
set
Three = (select A.Three from OtherServer.OtherDB.dbo.A as A where A.ID =
B.ID)
where
ID in (11, 12, 13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131799665.556244.160530@.g44g2000cwa.googlegr oups.com...
> Thanks for ur reply,
>
> The table A will be in One database of Some Server and Table B will
> be in Some other Database of Some other Server. How can i do it now?
> Thanks,
> Vinoth
>
|||Hi,
I cant use the above query. Because i dont have the A and B table on
the same time. A table will be deleted. I want to restore the deleted A
table values[ This values wil be kept in a text file using BCP utility]
to the B table. Now could you please tell me how can i do this?
Thanks,
Vinoth
Vinoth@.gsdindia.com
|||I still don't understand. You could simply create a new table, say CopyOfA
and put the old data there.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<vinoth@.gsdindia.com> wrote in message
news:1131802194.908738.97710@.g14g2000cwa.googlegro ups.com...
> Hi,
>
> I cant use the above query. Because i dont have the A and B table on
> the same time. A table will be deleted. I want to restore the deleted A
> table values[ This values wil be kept in a text file using BCP utility]
> to the B table. Now could you please tell me how can i do this?
> Thanks,
> Vinoth
> Vinoth@.gsdindia.com
>