Hi basically Im creating a package and I need to back up the table and put the date in the name of the table whenever the package is run, however I get an error when I try and do it, I think it should be possible but im not sure:
SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename
So the tablename should looksomething like this
tablename20031210
The error message is: incorrect syntax near +
Can anybody help.
ThanksUse dynamic Sql within pl/sql:
l_statement varchar2(100);
begin
l_statement:=' create table name_'||to_char(sysdate,'DDMMRRRR');
l_statement:=l_statement||define colums here
execute immediate l_statement;
end;
eventually you must append an ; at the end of the varchar2 because it is an DDL-Statement
Showing posts with label adding. Show all posts
Showing posts with label adding. Show all posts
Thursday, March 29, 2012
Adding a sysdate to a table
Hi basically Im creating a package and I need to back up the table and put the date in the name of the table whenever the package is run, however I get an error when I try and do it, I think it should be possible but im not sure:
SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename
So the tablename should looksomething like this
tablename20031210
The error message is: incorrect syntax near +
Can anybody help.
ThanksMaybe...
Declare @.Query nVarchar(1000)
SET @.Query=N'Select * From ' + 'tablename' + CONVERT(char(10), GETDATE(), 112)
EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35|||Thanks a lot this has been driving me crazy. I had to modify the code slightly but it works fine.
DECLARE @.Query nVarchar(1000)
SET @.Query = N'Select * INTO ' + 'tablename' + CONVERT(char(10), GETDATE(), 112) + 'FROM tablename' EXECUTE sp_executesql @.Query, N'@.level tinyint',
@.level = 35
SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename
So the tablename should looksomething like this
tablename20031210
The error message is: incorrect syntax near +
Can anybody help.
ThanksMaybe...
Declare @.Query nVarchar(1000)
SET @.Query=N'Select * From ' + 'tablename' + CONVERT(char(10), GETDATE(), 112)
EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35|||Thanks a lot this has been driving me crazy. I had to modify the code slightly but it works fine.
DECLARE @.Query nVarchar(1000)
SET @.Query = N'Select * INTO ' + 'tablename' + CONVERT(char(10), GETDATE(), 112) + 'FROM tablename' EXECUTE sp_executesql @.Query, N'@.level tinyint',
@.level = 35
Adding a sucsriber
Assuming that there is an exiting replication in place between ServerA and
ClientA (subscriber) and now I want to add another subscriber ClientB
How do I go about that
Should I just copy the database from the serverA and add a subscription to
ClientB or I have to do something to the Publication on ServerA
Thank you,
Samuel
Just add another subscriber using the wizard or sp_addsubcription. The
snapshot will be sent to Client B automatically.
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
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OMZb2rZ4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Assuming that there is an exiting replication in place between ServerA and
> ClientA (subscriber) and now I want to add another subscriber ClientB
> How do I go about that
> Should I just copy the database from the serverA and add a subscription to
> ClientB or I have to do something to the Publication on ServerA
> Thank you,
> Samuel
>
ClientA (subscriber) and now I want to add another subscriber ClientB
How do I go about that
Should I just copy the database from the serverA and add a subscription to
ClientB or I have to do something to the Publication on ServerA
Thank you,
Samuel
Just add another subscriber using the wizard or sp_addsubcription. The
snapshot will be sent to Client B automatically.
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
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OMZb2rZ4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Assuming that there is an exiting replication in place between ServerA and
> ClientA (subscriber) and now I want to add another subscriber ClientB
> How do I go about that
> Should I just copy the database from the serverA and add a subscription to
> ClientB or I have to do something to the Publication on ServerA
> Thank you,
> Samuel
>
Adding a subtotal column to a matrix
Can anyone direct me on how to add a subtotal column to a matrix?
I have dates in each column header and I want to add another column that has
"Total" in its header and sums up the other columns.
ThanksRight-click on the column header and check the "Subtotal" option. In order
to access the subtotal formatting properties in the VS properties window,
you have to click on the little green triangle after you added the subtotal.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"M" <M@.discussions.microsoft.com> wrote in message
news:1B939293-22C6-4B2B-A1E9-F6C0548C314B@.microsoft.com...
> Can anyone direct me on how to add a subtotal column to a matrix?
> I have dates in each column header and I want to add another column that
has
> "Total" in its header and sums up the other columns.
> Thankssql
I have dates in each column header and I want to add another column that has
"Total" in its header and sums up the other columns.
ThanksRight-click on the column header and check the "Subtotal" option. In order
to access the subtotal formatting properties in the VS properties window,
you have to click on the little green triangle after you added the subtotal.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"M" <M@.discussions.microsoft.com> wrote in message
news:1B939293-22C6-4B2B-A1E9-F6C0548C314B@.microsoft.com...
> Can anyone direct me on how to add a subtotal column to a matrix?
> I have dates in each column header and I want to add another column that
has
> "Total" in its header and sums up the other columns.
> Thankssql
Adding a subscriber which already has the schema and data
Hello,
I use a dynamically filtered publication.
I want to add a subscriber that already has the schema and data. So I create
a new subscription and set the option 'No, the Subscriber already has the
schema and data'.
When I start the new merge agent for the first time, it replicates all
historical data changes since the creation of the publication.
I don't want this behaviour, because the subscriber has already updated data.
I want the merge agent to replicate all changes since the creation of the
subscription.
Does someone has ideas?
thanks in advance, Marco
The merge replication process requires this metadata in order to track
changes. If you run a sp_mergemetadataretentioncleanup this could clean up
your metadata on your publisher and all subscriber, so you might be able to
start with metadata replication tables with no row in them.
You need sp 1 and above for this proc.
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:491BE7CE-2FD2-4885-908B-355460FAF06C@.microsoft.com...
> Hello,
> I use a dynamically filtered publication.
> I want to add a subscriber that already has the schema and data. So I
create
> a new subscription and set the option 'No, the Subscriber already has the
> schema and data'.
> When I start the new merge agent for the first time, it replicates all
> historical data changes since the creation of the publication.
> I don't want this behaviour, because the subscriber has already updated
data.
> I want the merge agent to replicate all changes since the creation of the
> subscription.
> Does someone has ideas?
> thanks in advance, Marco
>
>
I use a dynamically filtered publication.
I want to add a subscriber that already has the schema and data. So I create
a new subscription and set the option 'No, the Subscriber already has the
schema and data'.
When I start the new merge agent for the first time, it replicates all
historical data changes since the creation of the publication.
I don't want this behaviour, because the subscriber has already updated data.
I want the merge agent to replicate all changes since the creation of the
subscription.
Does someone has ideas?
thanks in advance, Marco
The merge replication process requires this metadata in order to track
changes. If you run a sp_mergemetadataretentioncleanup this could clean up
your metadata on your publisher and all subscriber, so you might be able to
start with metadata replication tables with no row in them.
You need sp 1 and above for this proc.
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:491BE7CE-2FD2-4885-908B-355460FAF06C@.microsoft.com...
> Hello,
> I use a dynamically filtered publication.
> I want to add a subscriber that already has the schema and data. So I
create
> a new subscription and set the option 'No, the Subscriber already has the
> schema and data'.
> When I start the new merge agent for the first time, it replicates all
> historical data changes since the creation of the publication.
> I don't want this behaviour, because the subscriber has already updated
data.
> I want the merge agent to replicate all changes since the creation of the
> subscription.
> Does someone has ideas?
> thanks in advance, Marco
>
>
adding a stored proc to a publication
Hi
I created a publication (merge anonymous via Web) - the subscriber already had the data, so there was no need to to initialize data and schema.
The problem is when it comes to adding a new stored proc to the publication. I create the proc, add it as an article to the publication and recreate the snapshot ok, but then the synchronisation fails. I presume this is because the stored proc does not exist at the subscriber. I bet it would work if I had initialized the schema and data, but due to the size of the data this is not practical.
If I manually create the proc at the subscriber then it works fine - but this defeats the purpose somewhat.....
Any ideas ?
thanks
BruceSince the subscription was created as no-sync, you will need to manually create the proc on the subscriber. You could create different publications for tables and stored procs and workaround that way. Now you can initialize the subscriber for the publication with the stored proc (and other smaller articles if you wish) and large data publication can stay intact (as no-sync)|||Thanks for the suggestion - a good workaround for now....
Adding a step to CreateUserWizard
Hey all,
I'm working in Visual Web Developer 2005 Express. I'm a total newb so please bare with me. I'm trying to add a step to my CreateUserWizard that I put in using the automatic site administration utility. I've added a wizard step in between the "Sign up for your new account" step and the "complete" step. The purpose of the new step is to collect additional information about the user. I've added a FormView control and used an SQLDataSource to link it to a new table in the database established by the administration utility, and set up the templates to recieve the data.
The one kink in the process seems to be associating the UserID created in step one with the additional information submitted in step 2. I made a UserID column, of data type uniqueidetifier in my new table and set up a foreign key relationship with the UserID columns in the aspnet_Membership table and the aspnet_Users table. When I run it as is I get the following error after I try to insert the data entered in the second step (additional information) into my new database table:
Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query
I guess my question is:How do I take the UserID generated in step 1, and pass it into the new table and into the new row being created in step 2? If possible, I would like to do it without any code (haha probably not going to happen, but worth a shot) but any assistance anyone could offer would be greatly appreciated. Thanks everyone.
Matt Downey
You can grab the userID like this
Membership.GetUser(CreateUserWizard1.UserName).ProviderUserKey
where createuserwizard1 is the ID of the 1st step of your wizard.
Adding a static text to a report header?
Does anyone know how to add static text to the report in the same section where report parameters and View Report are located?
Thanks,
Yadgor2000
Drop a textbox in the header and type into it. Bear in mind that a rich/html textbox is not supported so you can't format individual words. Any formats and fonts will apply to the entire contents of the textbox.|||Will the static text render before the report is run?
Thanks
YADGOR2000
|||No. When you run the report you will see the green circular loading gif and only once the report output has been generated in the RSTempDB database the report HTML will be sent to the client for output. I think you're referring to the way HTML tables get rendered as soon as the browser detects a closing </table> tag. This is not the way things work in RS.Adding a second processor
Hello,
We use a client/server application from our vendor running on SQL 2000,
which runs on a Windows 2003 Server in an ADS domain. The server is
currently only running a single Xeon processor and it has the capability for
having 2 processors. Our vendor claims that their application is not "coded
"
to run on a dual-processor machine (a bunch of bull?), but their application
only runs on the client end, not on the server.
The way I see it, it is Windows and SQL that have to be able to handle the
processor, which we all know they can certainly do. Does anyone think that
by adding a second processor, we would be doing any damage to our data and o
r
the application?
The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
is a case management system for a law firm. This is our mission-critical
application.
Thanks, JeffOutside of the fact that your vendor doesn't support it, I really see no
reason why you would want to limit yourself to just one processor. In most
cases, SQL Server would run faster with more CPU's. This is because your
workload is distributed across all of the CPU's. Thus, if you have 100
concurrent queries, 50 would run on one and 50 would run on the other -
ignoring parallelism. However, in most cases parallelism would improve an
individual query's performance, since both CPU's would be used to service
the query. In some cases, parallelism makes specific queries run slower,
but you can turn parallelism off on a per-query basis (or even at the server
level, if you prefer).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
Hello,
We use a client/server application from our vendor running on SQL 2000,
which runs on a Windows 2003 Server in an ADS domain. The server is
currently only running a single Xeon processor and it has the capability for
having 2 processors. Our vendor claims that their application is not
"coded"
to run on a dual-processor machine (a bunch of bull?), but their application
only runs on the client end, not on the server.
The way I see it, it is Windows and SQL that have to be able to handle the
processor, which we all know they can certainly do. Does anyone think that
by adding a second processor, we would be doing any damage to our data and
or
the application?
The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
is a case management system for a law firm. This is our mission-critical
application.
Thanks, Jeff|||I do realize the benefits of a second processor in SQL and that's just it, w
e
don't want to limit ourselves to a single processor. We purchased a new
server recently and only purchased one processor because of what our vendor
told us. It didn't make any sense to me then and it doesn't now. The mobo
on that server is setup for dual-processors. Since the application runs on
the client, I didn't understand why they would tell us that.
More importantly, we no longer have a service contract with that vendor so
they are not going to support us either way unless we get another contract.
My thought is that the worst that can happen is that we have to restore the
database off of a backup and pull the second processor out.
--
Thanks, Jeff
"Tom Moreau" wrote:
> Outside of the fact that your vendor doesn't support it, I really see no
> reason why you would want to limit yourself to just one processor. In mos
t
> cases, SQL Server would run faster with more CPU's. This is because your
> workload is distributed across all of the CPU's. Thus, if you have 100
> concurrent queries, 50 would run on one and 50 would run on the other -
> ignoring parallelism. However, in most cases parallelism would improve an
> individual query's performance, since both CPU's would be used to service
> the query. In some cases, parallelism makes specific queries run slower,
> but you can turn parallelism off on a per-query basis (or even at the serv
er
> level, if you prefer).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> Hello,
> We use a client/server application from our vendor running on SQL 2000,
> which runs on a Windows 2003 Server in an ADS domain. The server is
> currently only running a single Xeon processor and it has the capability f
or
> having 2 processors. Our vendor claims that their application is not
> "coded"
> to run on a dual-processor machine (a bunch of bull?), but their applicati
on
> only runs on the client end, not on the server.
> The way I see it, it is Windows and SQL that have to be able to handle the
> processor, which we all know they can certainly do. Does anyone think tha
t
> by adding a second processor, we would be doing any damage to our data and
> or
> the application?
> The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
> is a case management system for a law firm. This is our mission-critical
> application.
> --
> Thanks, Jeff
>|||The only real issue I see is that of licensing. If you go with a per-CPU
license, you will have to spend some change to do the upgrade. Other than
that, you're fine.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7CA5419B-884D-4D51-8B1C-3DEE16B291F1@.microsoft.com...
I do realize the benefits of a second processor in SQL and that's just it,
we
don't want to limit ourselves to a single processor. We purchased a new
server recently and only purchased one processor because of what our vendor
told us. It didn't make any sense to me then and it doesn't now. The mobo
on that server is setup for dual-processors. Since the application runs on
the client, I didn't understand why they would tell us that.
More importantly, we no longer have a service contract with that vendor so
they are not going to support us either way unless we get another contract.
My thought is that the worst that can happen is that we have to restore the
database off of a backup and pull the second processor out.
--
Thanks, Jeff
"Tom Moreau" wrote:
> Outside of the fact that your vendor doesn't support it, I really see no
> reason why you would want to limit yourself to just one processor. In
> most
> cases, SQL Server would run faster with more CPU's. This is because your
> workload is distributed across all of the CPU's. Thus, if you have 100
> concurrent queries, 50 would run on one and 50 would run on the other -
> ignoring parallelism. However, in most cases parallelism would improve an
> individual query's performance, since both CPU's would be used to service
> the query. In some cases, parallelism makes specific queries run slower,
> but you can turn parallelism off on a per-query basis (or even at the
> server
> level, if you prefer).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> Hello,
> We use a client/server application from our vendor running on SQL 2000,
> which runs on a Windows 2003 Server in an ADS domain. The server is
> currently only running a single Xeon processor and it has the capability
> for
> having 2 processors. Our vendor claims that their application is not
> "coded"
> to run on a dual-processor machine (a bunch of bull?), but their
> application
> only runs on the client end, not on the server.
> The way I see it, it is Windows and SQL that have to be able to handle the
> processor, which we all know they can certainly do. Does anyone think
> that
> by adding a second processor, we would be doing any damage to our data and
> or
> the application?
> The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
> is a case management system for a law firm. This is our mission-critical
> application.
> --
> Thanks, Jeff
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uzy8rPbeGHA.5016@.TK2MSFTNGP04.phx.gbl...
> The only real issue I see is that of licensing. If you go with a per-CPU
> license, you will have to spend some change to do the upgrade. Other than
> that, you're fine.
I'll second this.
And second the opinion that the original vendor is full of it. :-)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:7CA5419B-884D-4D51-8B1C-3DEE16B291F1@.microsoft.com...
> I do realize the benefits of a second processor in SQL and that's just it,
> we
> don't want to limit ourselves to a single processor. We purchased a new
> server recently and only purchased one processor because of what our
vendor
> told us. It didn't make any sense to me then and it doesn't now. The
mobo
> on that server is setup for dual-processors. Since the application runs
on
> the client, I didn't understand why they would tell us that.
> More importantly, we no longer have a service contract with that vendor so
> they are not going to support us either way unless we get another
contract.
> My thought is that the worst that can happen is that we have to restore
the
> database off of a backup and pull the second processor out.
> --
> Thanks, Jeff
>
> "Tom Moreau" wrote:
>
your[vbcol=seagreen]
an[vbcol=seagreen]
service[vbcol=seagreen]
slower,[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
it[vbcol=seagreen]
mission-critical[vbcol=seagreen]
>sql
We use a client/server application from our vendor running on SQL 2000,
which runs on a Windows 2003 Server in an ADS domain. The server is
currently only running a single Xeon processor and it has the capability for
having 2 processors. Our vendor claims that their application is not "coded
"
to run on a dual-processor machine (a bunch of bull?), but their application
only runs on the client end, not on the server.
The way I see it, it is Windows and SQL that have to be able to handle the
processor, which we all know they can certainly do. Does anyone think that
by adding a second processor, we would be doing any damage to our data and o
r
the application?
The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
is a case management system for a law firm. This is our mission-critical
application.
Thanks, JeffOutside of the fact that your vendor doesn't support it, I really see no
reason why you would want to limit yourself to just one processor. In most
cases, SQL Server would run faster with more CPU's. This is because your
workload is distributed across all of the CPU's. Thus, if you have 100
concurrent queries, 50 would run on one and 50 would run on the other -
ignoring parallelism. However, in most cases parallelism would improve an
individual query's performance, since both CPU's would be used to service
the query. In some cases, parallelism makes specific queries run slower,
but you can turn parallelism off on a per-query basis (or even at the server
level, if you prefer).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
Hello,
We use a client/server application from our vendor running on SQL 2000,
which runs on a Windows 2003 Server in an ADS domain. The server is
currently only running a single Xeon processor and it has the capability for
having 2 processors. Our vendor claims that their application is not
"coded"
to run on a dual-processor machine (a bunch of bull?), but their application
only runs on the client end, not on the server.
The way I see it, it is Windows and SQL that have to be able to handle the
processor, which we all know they can certainly do. Does anyone think that
by adding a second processor, we would be doing any damage to our data and
or
the application?
The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
is a case management system for a law firm. This is our mission-critical
application.
Thanks, Jeff|||I do realize the benefits of a second processor in SQL and that's just it, w
e
don't want to limit ourselves to a single processor. We purchased a new
server recently and only purchased one processor because of what our vendor
told us. It didn't make any sense to me then and it doesn't now. The mobo
on that server is setup for dual-processors. Since the application runs on
the client, I didn't understand why they would tell us that.
More importantly, we no longer have a service contract with that vendor so
they are not going to support us either way unless we get another contract.
My thought is that the worst that can happen is that we have to restore the
database off of a backup and pull the second processor out.
--
Thanks, Jeff
"Tom Moreau" wrote:
> Outside of the fact that your vendor doesn't support it, I really see no
> reason why you would want to limit yourself to just one processor. In mos
t
> cases, SQL Server would run faster with more CPU's. This is because your
> workload is distributed across all of the CPU's. Thus, if you have 100
> concurrent queries, 50 would run on one and 50 would run on the other -
> ignoring parallelism. However, in most cases parallelism would improve an
> individual query's performance, since both CPU's would be used to service
> the query. In some cases, parallelism makes specific queries run slower,
> but you can turn parallelism off on a per-query basis (or even at the serv
er
> level, if you prefer).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> Hello,
> We use a client/server application from our vendor running on SQL 2000,
> which runs on a Windows 2003 Server in an ADS domain. The server is
> currently only running a single Xeon processor and it has the capability f
or
> having 2 processors. Our vendor claims that their application is not
> "coded"
> to run on a dual-processor machine (a bunch of bull?), but their applicati
on
> only runs on the client end, not on the server.
> The way I see it, it is Windows and SQL that have to be able to handle the
> processor, which we all know they can certainly do. Does anyone think tha
t
> by adding a second processor, we would be doing any damage to our data and
> or
> the application?
> The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
> is a case management system for a law firm. This is our mission-critical
> application.
> --
> Thanks, Jeff
>|||The only real issue I see is that of licensing. If you go with a per-CPU
license, you will have to spend some change to do the upgrade. Other than
that, you're fine.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7CA5419B-884D-4D51-8B1C-3DEE16B291F1@.microsoft.com...
I do realize the benefits of a second processor in SQL and that's just it,
we
don't want to limit ourselves to a single processor. We purchased a new
server recently and only purchased one processor because of what our vendor
told us. It didn't make any sense to me then and it doesn't now. The mobo
on that server is setup for dual-processors. Since the application runs on
the client, I didn't understand why they would tell us that.
More importantly, we no longer have a service contract with that vendor so
they are not going to support us either way unless we get another contract.
My thought is that the worst that can happen is that we have to restore the
database off of a backup and pull the second processor out.
--
Thanks, Jeff
"Tom Moreau" wrote:
> Outside of the fact that your vendor doesn't support it, I really see no
> reason why you would want to limit yourself to just one processor. In
> most
> cases, SQL Server would run faster with more CPU's. This is because your
> workload is distributed across all of the CPU's. Thus, if you have 100
> concurrent queries, 50 would run on one and 50 would run on the other -
> ignoring parallelism. However, in most cases parallelism would improve an
> individual query's performance, since both CPU's would be used to service
> the query. In some cases, parallelism makes specific queries run slower,
> but you can turn parallelism off on a per-query basis (or even at the
> server
> level, if you prefer).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> Hello,
> We use a client/server application from our vendor running on SQL 2000,
> which runs on a Windows 2003 Server in an ADS domain. The server is
> currently only running a single Xeon processor and it has the capability
> for
> having 2 processors. Our vendor claims that their application is not
> "coded"
> to run on a dual-processor machine (a bunch of bull?), but their
> application
> only runs on the client end, not on the server.
> The way I see it, it is Windows and SQL that have to be able to handle the
> processor, which we all know they can certainly do. Does anyone think
> that
> by adding a second processor, we would be doing any damage to our data and
> or
> the application?
> The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
> is a case management system for a law firm. This is our mission-critical
> application.
> --
> Thanks, Jeff
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uzy8rPbeGHA.5016@.TK2MSFTNGP04.phx.gbl...
> The only real issue I see is that of licensing. If you go with a per-CPU
> license, you will have to spend some change to do the upgrade. Other than
> that, you're fine.
I'll second this.
And second the opinion that the original vendor is full of it. :-)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:7CA5419B-884D-4D51-8B1C-3DEE16B291F1@.microsoft.com...
> I do realize the benefits of a second processor in SQL and that's just it,
> we
> don't want to limit ourselves to a single processor. We purchased a new
> server recently and only purchased one processor because of what our
vendor
> told us. It didn't make any sense to me then and it doesn't now. The
mobo
> on that server is setup for dual-processors. Since the application runs
on
> the client, I didn't understand why they would tell us that.
> More importantly, we no longer have a service contract with that vendor so
> they are not going to support us either way unless we get another
contract.
> My thought is that the worst that can happen is that we have to restore
the
> database off of a backup and pull the second processor out.
> --
> Thanks, Jeff
>
> "Tom Moreau" wrote:
>
your[vbcol=seagreen]
an[vbcol=seagreen]
service[vbcol=seagreen]
slower,[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
it[vbcol=seagreen]
mission-critical[vbcol=seagreen]
>sql
Adding a second processor
Hello,
We use a client/server application from our vendor running on SQL 2000,
which runs on a Windows 2003 Server in an ADS domain. The server is
currently only running a single Xeon processor and it has the capability for
having 2 processors. Our vendor claims that their application is not "coded"
to run on a dual-processor machine (a bunch of bull?), but their application
only runs on the client end, not on the server.
The way I see it, it is Windows and SQL that have to be able to handle the
processor, which we all know they can certainly do. Does anyone think that
by adding a second processor, we would be doing any damage to our data and or
the application?
The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
is a case management system for a law firm. This is our mission-critical
application.
--
Thanks, JeffOutside of the fact that your vendor doesn't support it, I really see no
reason why you would want to limit yourself to just one processor. In most
cases, SQL Server would run faster with more CPU's. This is because your
workload is distributed across all of the CPU's. Thus, if you have 100
concurrent queries, 50 would run on one and 50 would run on the other -
ignoring parallelism. However, in most cases parallelism would improve an
individual query's performance, since both CPU's would be used to service
the query. In some cases, parallelism makes specific queries run slower,
but you can turn parallelism off on a per-query basis (or even at the server
level, if you prefer).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
Hello,
We use a client/server application from our vendor running on SQL 2000,
which runs on a Windows 2003 Server in an ADS domain. The server is
currently only running a single Xeon processor and it has the capability for
having 2 processors. Our vendor claims that their application is not
"coded"
to run on a dual-processor machine (a bunch of bull?), but their application
only runs on the client end, not on the server.
The way I see it, it is Windows and SQL that have to be able to handle the
processor, which we all know they can certainly do. Does anyone think that
by adding a second processor, we would be doing any damage to our data and
or
the application?
The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
is a case management system for a law firm. This is our mission-critical
application.
--
Thanks, Jeff|||I do realize the benefits of a second processor in SQL and that's just it, we
don't want to limit ourselves to a single processor. We purchased a new
server recently and only purchased one processor because of what our vendor
told us. It didn't make any sense to me then and it doesn't now. The mobo
on that server is setup for dual-processors. Since the application runs on
the client, I didn't understand why they would tell us that.
More importantly, we no longer have a service contract with that vendor so
they are not going to support us either way unless we get another contract.
My thought is that the worst that can happen is that we have to restore the
database off of a backup and pull the second processor out.
--
Thanks, Jeff
"Tom Moreau" wrote:
> Outside of the fact that your vendor doesn't support it, I really see no
> reason why you would want to limit yourself to just one processor. In most
> cases, SQL Server would run faster with more CPU's. This is because your
> workload is distributed across all of the CPU's. Thus, if you have 100
> concurrent queries, 50 would run on one and 50 would run on the other -
> ignoring parallelism. However, in most cases parallelism would improve an
> individual query's performance, since both CPU's would be used to service
> the query. In some cases, parallelism makes specific queries run slower,
> but you can turn parallelism off on a per-query basis (or even at the server
> level, if you prefer).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> Hello,
> We use a client/server application from our vendor running on SQL 2000,
> which runs on a Windows 2003 Server in an ADS domain. The server is
> currently only running a single Xeon processor and it has the capability for
> having 2 processors. Our vendor claims that their application is not
> "coded"
> to run on a dual-processor machine (a bunch of bull?), but their application
> only runs on the client end, not on the server.
> The way I see it, it is Windows and SQL that have to be able to handle the
> processor, which we all know they can certainly do. Does anyone think that
> by adding a second processor, we would be doing any damage to our data and
> or
> the application?
> The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
> is a case management system for a law firm. This is our mission-critical
> application.
> --
> Thanks, Jeff
>|||The only real issue I see is that of licensing. If you go with a per-CPU
license, you will have to spend some change to do the upgrade. Other than
that, you're fine.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7CA5419B-884D-4D51-8B1C-3DEE16B291F1@.microsoft.com...
I do realize the benefits of a second processor in SQL and that's just it,
we
don't want to limit ourselves to a single processor. We purchased a new
server recently and only purchased one processor because of what our vendor
told us. It didn't make any sense to me then and it doesn't now. The mobo
on that server is setup for dual-processors. Since the application runs on
the client, I didn't understand why they would tell us that.
More importantly, we no longer have a service contract with that vendor so
they are not going to support us either way unless we get another contract.
My thought is that the worst that can happen is that we have to restore the
database off of a backup and pull the second processor out.
--
Thanks, Jeff
"Tom Moreau" wrote:
> Outside of the fact that your vendor doesn't support it, I really see no
> reason why you would want to limit yourself to just one processor. In
> most
> cases, SQL Server would run faster with more CPU's. This is because your
> workload is distributed across all of the CPU's. Thus, if you have 100
> concurrent queries, 50 would run on one and 50 would run on the other -
> ignoring parallelism. However, in most cases parallelism would improve an
> individual query's performance, since both CPU's would be used to service
> the query. In some cases, parallelism makes specific queries run slower,
> but you can turn parallelism off on a per-query basis (or even at the
> server
> level, if you prefer).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> Hello,
> We use a client/server application from our vendor running on SQL 2000,
> which runs on a Windows 2003 Server in an ADS domain. The server is
> currently only running a single Xeon processor and it has the capability
> for
> having 2 processors. Our vendor claims that their application is not
> "coded"
> to run on a dual-processor machine (a bunch of bull?), but their
> application
> only runs on the client end, not on the server.
> The way I see it, it is Windows and SQL that have to be able to handle the
> processor, which we all know they can certainly do. Does anyone think
> that
> by adding a second processor, we would be doing any damage to our data and
> or
> the application?
> The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
> is a case management system for a law firm. This is our mission-critical
> application.
> --
> Thanks, Jeff
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uzy8rPbeGHA.5016@.TK2MSFTNGP04.phx.gbl...
> The only real issue I see is that of licensing. If you go with a per-CPU
> license, you will have to spend some change to do the upgrade. Other than
> that, you're fine.
I'll second this.
And second the opinion that the original vendor is full of it. :-)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:7CA5419B-884D-4D51-8B1C-3DEE16B291F1@.microsoft.com...
> I do realize the benefits of a second processor in SQL and that's just it,
> we
> don't want to limit ourselves to a single processor. We purchased a new
> server recently and only purchased one processor because of what our
vendor
> told us. It didn't make any sense to me then and it doesn't now. The
mobo
> on that server is setup for dual-processors. Since the application runs
on
> the client, I didn't understand why they would tell us that.
> More importantly, we no longer have a service contract with that vendor so
> they are not going to support us either way unless we get another
contract.
> My thought is that the worst that can happen is that we have to restore
the
> database off of a backup and pull the second processor out.
> --
> Thanks, Jeff
>
> "Tom Moreau" wrote:
> > Outside of the fact that your vendor doesn't support it, I really see no
> > reason why you would want to limit yourself to just one processor. In
> > most
> > cases, SQL Server would run faster with more CPU's. This is because
your
> > workload is distributed across all of the CPU's. Thus, if you have 100
> > concurrent queries, 50 would run on one and 50 would run on the other -
> > ignoring parallelism. However, in most cases parallelism would improve
an
> > individual query's performance, since both CPU's would be used to
service
> > the query. In some cases, parallelism makes specific queries run
slower,
> > but you can turn parallelism off on a per-query basis (or even at the
> > server
> > level, if you prefer).
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > ..
> > "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> > news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> > Hello,
> >
> > We use a client/server application from our vendor running on SQL 2000,
> > which runs on a Windows 2003 Server in an ADS domain. The server is
> > currently only running a single Xeon processor and it has the capability
> > for
> > having 2 processors. Our vendor claims that their application is not
> > "coded"
> > to run on a dual-processor machine (a bunch of bull?), but their
> > application
> > only runs on the client end, not on the server.
> >
> > The way I see it, it is Windows and SQL that have to be able to handle
the
> > processor, which we all know they can certainly do. Does anyone think
> > that
> > by adding a second processor, we would be doing any damage to our data
and
> > or
> > the application?
> >
> > The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and
it
> > is a case management system for a law firm. This is our
mission-critical
> > application.
> >
> > --
> > Thanks, Jeff
> >
> >
>
We use a client/server application from our vendor running on SQL 2000,
which runs on a Windows 2003 Server in an ADS domain. The server is
currently only running a single Xeon processor and it has the capability for
having 2 processors. Our vendor claims that their application is not "coded"
to run on a dual-processor machine (a bunch of bull?), but their application
only runs on the client end, not on the server.
The way I see it, it is Windows and SQL that have to be able to handle the
processor, which we all know they can certainly do. Does anyone think that
by adding a second processor, we would be doing any damage to our data and or
the application?
The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
is a case management system for a law firm. This is our mission-critical
application.
--
Thanks, JeffOutside of the fact that your vendor doesn't support it, I really see no
reason why you would want to limit yourself to just one processor. In most
cases, SQL Server would run faster with more CPU's. This is because your
workload is distributed across all of the CPU's. Thus, if you have 100
concurrent queries, 50 would run on one and 50 would run on the other -
ignoring parallelism. However, in most cases parallelism would improve an
individual query's performance, since both CPU's would be used to service
the query. In some cases, parallelism makes specific queries run slower,
but you can turn parallelism off on a per-query basis (or even at the server
level, if you prefer).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
Hello,
We use a client/server application from our vendor running on SQL 2000,
which runs on a Windows 2003 Server in an ADS domain. The server is
currently only running a single Xeon processor and it has the capability for
having 2 processors. Our vendor claims that their application is not
"coded"
to run on a dual-processor machine (a bunch of bull?), but their application
only runs on the client end, not on the server.
The way I see it, it is Windows and SQL that have to be able to handle the
processor, which we all know they can certainly do. Does anyone think that
by adding a second processor, we would be doing any damage to our data and
or
the application?
The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
is a case management system for a law firm. This is our mission-critical
application.
--
Thanks, Jeff|||I do realize the benefits of a second processor in SQL and that's just it, we
don't want to limit ourselves to a single processor. We purchased a new
server recently and only purchased one processor because of what our vendor
told us. It didn't make any sense to me then and it doesn't now. The mobo
on that server is setup for dual-processors. Since the application runs on
the client, I didn't understand why they would tell us that.
More importantly, we no longer have a service contract with that vendor so
they are not going to support us either way unless we get another contract.
My thought is that the worst that can happen is that we have to restore the
database off of a backup and pull the second processor out.
--
Thanks, Jeff
"Tom Moreau" wrote:
> Outside of the fact that your vendor doesn't support it, I really see no
> reason why you would want to limit yourself to just one processor. In most
> cases, SQL Server would run faster with more CPU's. This is because your
> workload is distributed across all of the CPU's. Thus, if you have 100
> concurrent queries, 50 would run on one and 50 would run on the other -
> ignoring parallelism. However, in most cases parallelism would improve an
> individual query's performance, since both CPU's would be used to service
> the query. In some cases, parallelism makes specific queries run slower,
> but you can turn parallelism off on a per-query basis (or even at the server
> level, if you prefer).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> Hello,
> We use a client/server application from our vendor running on SQL 2000,
> which runs on a Windows 2003 Server in an ADS domain. The server is
> currently only running a single Xeon processor and it has the capability for
> having 2 processors. Our vendor claims that their application is not
> "coded"
> to run on a dual-processor machine (a bunch of bull?), but their application
> only runs on the client end, not on the server.
> The way I see it, it is Windows and SQL that have to be able to handle the
> processor, which we all know they can certainly do. Does anyone think that
> by adding a second processor, we would be doing any damage to our data and
> or
> the application?
> The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
> is a case management system for a law firm. This is our mission-critical
> application.
> --
> Thanks, Jeff
>|||The only real issue I see is that of licensing. If you go with a per-CPU
license, you will have to spend some change to do the upgrade. Other than
that, you're fine.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7CA5419B-884D-4D51-8B1C-3DEE16B291F1@.microsoft.com...
I do realize the benefits of a second processor in SQL and that's just it,
we
don't want to limit ourselves to a single processor. We purchased a new
server recently and only purchased one processor because of what our vendor
told us. It didn't make any sense to me then and it doesn't now. The mobo
on that server is setup for dual-processors. Since the application runs on
the client, I didn't understand why they would tell us that.
More importantly, we no longer have a service contract with that vendor so
they are not going to support us either way unless we get another contract.
My thought is that the worst that can happen is that we have to restore the
database off of a backup and pull the second processor out.
--
Thanks, Jeff
"Tom Moreau" wrote:
> Outside of the fact that your vendor doesn't support it, I really see no
> reason why you would want to limit yourself to just one processor. In
> most
> cases, SQL Server would run faster with more CPU's. This is because your
> workload is distributed across all of the CPU's. Thus, if you have 100
> concurrent queries, 50 would run on one and 50 would run on the other -
> ignoring parallelism. However, in most cases parallelism would improve an
> individual query's performance, since both CPU's would be used to service
> the query. In some cases, parallelism makes specific queries run slower,
> but you can turn parallelism off on a per-query basis (or even at the
> server
> level, if you prefer).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> Hello,
> We use a client/server application from our vendor running on SQL 2000,
> which runs on a Windows 2003 Server in an ADS domain. The server is
> currently only running a single Xeon processor and it has the capability
> for
> having 2 processors. Our vendor claims that their application is not
> "coded"
> to run on a dual-processor machine (a bunch of bull?), but their
> application
> only runs on the client end, not on the server.
> The way I see it, it is Windows and SQL that have to be able to handle the
> processor, which we all know they can certainly do. Does anyone think
> that
> by adding a second processor, we would be doing any damage to our data and
> or
> the application?
> The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and it
> is a case management system for a law firm. This is our mission-critical
> application.
> --
> Thanks, Jeff
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uzy8rPbeGHA.5016@.TK2MSFTNGP04.phx.gbl...
> The only real issue I see is that of licensing. If you go with a per-CPU
> license, you will have to spend some change to do the upgrade. Other than
> that, you're fine.
I'll second this.
And second the opinion that the original vendor is full of it. :-)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:7CA5419B-884D-4D51-8B1C-3DEE16B291F1@.microsoft.com...
> I do realize the benefits of a second processor in SQL and that's just it,
> we
> don't want to limit ourselves to a single processor. We purchased a new
> server recently and only purchased one processor because of what our
vendor
> told us. It didn't make any sense to me then and it doesn't now. The
mobo
> on that server is setup for dual-processors. Since the application runs
on
> the client, I didn't understand why they would tell us that.
> More importantly, we no longer have a service contract with that vendor so
> they are not going to support us either way unless we get another
contract.
> My thought is that the worst that can happen is that we have to restore
the
> database off of a backup and pull the second processor out.
> --
> Thanks, Jeff
>
> "Tom Moreau" wrote:
> > Outside of the fact that your vendor doesn't support it, I really see no
> > reason why you would want to limit yourself to just one processor. In
> > most
> > cases, SQL Server would run faster with more CPU's. This is because
your
> > workload is distributed across all of the CPU's. Thus, if you have 100
> > concurrent queries, 50 would run on one and 50 would run on the other -
> > ignoring parallelism. However, in most cases parallelism would improve
an
> > individual query's performance, since both CPU's would be used to
service
> > the query. In some cases, parallelism makes specific queries run
slower,
> > but you can turn parallelism off on a per-query basis (or even at the
> > server
> > level, if you prefer).
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > ..
> > "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> > news:56410EBD-043F-4AC4-870B-652141840545@.microsoft.com...
> > Hello,
> >
> > We use a client/server application from our vendor running on SQL 2000,
> > which runs on a Windows 2003 Server in an ADS domain. The server is
> > currently only running a single Xeon processor and it has the capability
> > for
> > having 2 processors. Our vendor claims that their application is not
> > "coded"
> > to run on a dual-processor machine (a bunch of bull?), but their
> > application
> > only runs on the client end, not on the server.
> >
> > The way I see it, it is Windows and SQL that have to be able to handle
the
> > processor, which we all know they can certainly do. Does anyone think
> > that
> > by adding a second processor, we would be doing any damage to our data
and
> > or
> > the application?
> >
> > The application is Time Matters Enterprise 5.0 (sr2), by LexisNexis and
it
> > is a case management system for a law firm. This is our
mission-critical
> > application.
> >
> > --
> > Thanks, Jeff
> >
> >
>
Adding a second CPU
Hi all.
I have a 1 cpu server running sql server 2000 sp3a and we would like to add
a second cpu to it and I wonder if it is just a matter of installaing the
second CPU and then start the system and SQL server automaticly start to use
the second cpu or do i have to make any configuration changes for SQL server
to start use the second cpu.
Thanks
Thomas
Once you add the 2nd CPU and re-start the machine SQL Server will
automatically recognise the extra processor.
The only exception would be if you had a an Affinity Mask on (to
restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
your case.
ALI
Thomas_ wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to use
> the second cpu or do i have to make any configuration changes for SQL server
> to start use the second cpu.
> Thanks
> Thomas
|||Hi
If the OS sees it on startup, SQL Server will also and will use it. No
changes for you.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas_" wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to use
> the second cpu or do i have to make any configuration changes for SQL server
> to start use the second cpu.
> Thanks
> Thomas
|||Thank you very much for your quick reply.
Regards
Thomas
"zashah@.gmail.com" wrote:
> Once you add the 2nd CPU and re-start the machine SQL Server will
> automatically recognise the extra processor.
> The only exception would be if you had a an Affinity Mask on (to
> restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
> your case.
> ALI
>
> Thomas_ wrote:
>
|||Like Mike said, SQL Server will can use the processors detected by the OS.
Depending on your Windows version, additional steps may be needed to install
a multiprocessor HAL:
http://support.microsoft.com/kb/234558
http://support.microsoft.com/default...b;en-us;888729
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas_" <Thomas_@.discussions.microsoft.com> wrote in message
news:FCED2511-598D-4D4D-9E6D-C4FBE15B2075@.microsoft.com...
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to
> add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to
> use
> the second cpu or do i have to make any configuration changes for SQL
> server
> to start use the second cpu.
> Thanks
> Thomas
I have a 1 cpu server running sql server 2000 sp3a and we would like to add
a second cpu to it and I wonder if it is just a matter of installaing the
second CPU and then start the system and SQL server automaticly start to use
the second cpu or do i have to make any configuration changes for SQL server
to start use the second cpu.
Thanks
Thomas
Once you add the 2nd CPU and re-start the machine SQL Server will
automatically recognise the extra processor.
The only exception would be if you had a an Affinity Mask on (to
restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
your case.
ALI
Thomas_ wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to use
> the second cpu or do i have to make any configuration changes for SQL server
> to start use the second cpu.
> Thanks
> Thomas
|||Hi
If the OS sees it on startup, SQL Server will also and will use it. No
changes for you.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas_" wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to use
> the second cpu or do i have to make any configuration changes for SQL server
> to start use the second cpu.
> Thanks
> Thomas
|||Thank you very much for your quick reply.
Regards
Thomas
"zashah@.gmail.com" wrote:
> Once you add the 2nd CPU and re-start the machine SQL Server will
> automatically recognise the extra processor.
> The only exception would be if you had a an Affinity Mask on (to
> restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
> your case.
> ALI
>
> Thomas_ wrote:
>
|||Like Mike said, SQL Server will can use the processors detected by the OS.
Depending on your Windows version, additional steps may be needed to install
a multiprocessor HAL:
http://support.microsoft.com/kb/234558
http://support.microsoft.com/default...b;en-us;888729
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas_" <Thomas_@.discussions.microsoft.com> wrote in message
news:FCED2511-598D-4D4D-9E6D-C4FBE15B2075@.microsoft.com...
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to
> add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to
> use
> the second cpu or do i have to make any configuration changes for SQL
> server
> to start use the second cpu.
> Thanks
> Thomas
Adding a second CPU
Hi all.
I have a 1 cpu server running sql server 2000 sp3a and we would like to add
a second cpu to it and I wonder if it is just a matter of installaing the
second CPU and then start the system and SQL server automaticly start to use
the second cpu or do i have to make any configuration changes for SQL server
to start use the second cpu.
Thanks
ThomasOnce you add the 2nd CPU and re-start the machine SQL Server will
automatically recognise the extra processor.
The only exception would be if you had a an Affinity Mask on (to
restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
your case.
ALI
Thomas_ wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to use
> the second cpu or do i have to make any configuration changes for SQL server
> to start use the second cpu.
> Thanks
> Thomas|||Hi
If the OS sees it on startup, SQL Server will also and will use it. No
changes for you.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas_" wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to use
> the second cpu or do i have to make any configuration changes for SQL server
> to start use the second cpu.
> Thanks
> Thomas|||Thank you very much for your quick reply.
Regards
Thomas
"zashah@.gmail.com" wrote:
> Once you add the 2nd CPU and re-start the machine SQL Server will
> automatically recognise the extra processor.
> The only exception would be if you had a an Affinity Mask on (to
> restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
> your case.
> ALI
>
> Thomas_ wrote:
> > Hi all.
> >
> > I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> > a second cpu to it and I wonder if it is just a matter of installaing the
> > second CPU and then start the system and SQL server automaticly start to use
> > the second cpu or do i have to make any configuration changes for SQL server
> > to start use the second cpu.
> >
> > Thanks
> > Thomas
>|||Like Mike said, SQL Server will can use the processors detected by the OS.
Depending on your Windows version, additional steps may be needed to install
a multiprocessor HAL:
http://support.microsoft.com/kb/234558
http://support.microsoft.com/default.aspx?scid=kb;en-us;888729
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas_" <Thomas_@.discussions.microsoft.com> wrote in message
news:FCED2511-598D-4D4D-9E6D-C4FBE15B2075@.microsoft.com...
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to
> add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to
> use
> the second cpu or do i have to make any configuration changes for SQL
> server
> to start use the second cpu.
> Thanks
> Thomas
I have a 1 cpu server running sql server 2000 sp3a and we would like to add
a second cpu to it and I wonder if it is just a matter of installaing the
second CPU and then start the system and SQL server automaticly start to use
the second cpu or do i have to make any configuration changes for SQL server
to start use the second cpu.
Thanks
ThomasOnce you add the 2nd CPU and re-start the machine SQL Server will
automatically recognise the extra processor.
The only exception would be if you had a an Affinity Mask on (to
restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
your case.
ALI
Thomas_ wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to use
> the second cpu or do i have to make any configuration changes for SQL server
> to start use the second cpu.
> Thanks
> Thomas|||Hi
If the OS sees it on startup, SQL Server will also and will use it. No
changes for you.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas_" wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to use
> the second cpu or do i have to make any configuration changes for SQL server
> to start use the second cpu.
> Thanks
> Thomas|||Thank you very much for your quick reply.
Regards
Thomas
"zashah@.gmail.com" wrote:
> Once you add the 2nd CPU and re-start the machine SQL Server will
> automatically recognise the extra processor.
> The only exception would be if you had a an Affinity Mask on (to
> restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
> your case.
> ALI
>
> Thomas_ wrote:
> > Hi all.
> >
> > I have a 1 cpu server running sql server 2000 sp3a and we would like to add
> > a second cpu to it and I wonder if it is just a matter of installaing the
> > second CPU and then start the system and SQL server automaticly start to use
> > the second cpu or do i have to make any configuration changes for SQL server
> > to start use the second cpu.
> >
> > Thanks
> > Thomas
>|||Like Mike said, SQL Server will can use the processors detected by the OS.
Depending on your Windows version, additional steps may be needed to install
a multiprocessor HAL:
http://support.microsoft.com/kb/234558
http://support.microsoft.com/default.aspx?scid=kb;en-us;888729
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas_" <Thomas_@.discussions.microsoft.com> wrote in message
news:FCED2511-598D-4D4D-9E6D-C4FBE15B2075@.microsoft.com...
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to
> add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to
> use
> the second cpu or do i have to make any configuration changes for SQL
> server
> to start use the second cpu.
> Thanks
> Thomas
Adding a second CPU
Hi all.
I have a 1 cpu server running sql server 2000 sp3a and we would like to add
a second cpu to it and I wonder if it is just a matter of installaing the
second CPU and then start the system and SQL server automaticly start to use
the second cpu or do i have to make any configuration changes for SQL server
to start use the second cpu.
Thanks
ThomasOnce you add the 2nd CPU and re-start the machine SQL Server will
automatically recognise the extra processor.
The only exception would be if you had a an Affinity Mask on (to
restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
your case.
ALI
Thomas_ wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to ad
d
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to u
se
> the second cpu or do i have to make any configuration changes for SQL serv
er
> to start use the second cpu.
> Thanks
> Thomas|||Hi
If the OS sees it on startup, SQL Server will also and will use it. No
changes for you.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas_" wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to ad
d
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to u
se
> the second cpu or do i have to make any configuration changes for SQL serv
er
> to start use the second cpu.
> Thanks
> Thomas|||Thank you very much for your quick reply.
Regards
Thomas
"zashah@.gmail.com" wrote:
> Once you add the 2nd CPU and re-start the machine SQL Server will
> automatically recognise the extra processor.
> The only exception would be if you had a an Affinity Mask on (to
> restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
> your case.
> ALI
>
> Thomas_ wrote:
>|||Like Mike said, SQL Server will can use the processors detected by the OS.
Depending on your Windows version, additional steps may be needed to install
a multiprocessor HAL:
http://support.microsoft.com/kb/234558
http://support.microsoft.com/defaul...kb;en-us;888729
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas_" <Thomas_@.discussions.microsoft.com> wrote in message
news:FCED2511-598D-4D4D-9E6D-C4FBE15B2075@.microsoft.com...
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to
> add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to
> use
> the second cpu or do i have to make any configuration changes for SQL
> server
> to start use the second cpu.
> Thanks
> Thomas
I have a 1 cpu server running sql server 2000 sp3a and we would like to add
a second cpu to it and I wonder if it is just a matter of installaing the
second CPU and then start the system and SQL server automaticly start to use
the second cpu or do i have to make any configuration changes for SQL server
to start use the second cpu.
Thanks
ThomasOnce you add the 2nd CPU and re-start the machine SQL Server will
automatically recognise the extra processor.
The only exception would be if you had a an Affinity Mask on (to
restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
your case.
ALI
Thomas_ wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to ad
d
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to u
se
> the second cpu or do i have to make any configuration changes for SQL serv
er
> to start use the second cpu.
> Thanks
> Thomas|||Hi
If the OS sees it on startup, SQL Server will also and will use it. No
changes for you.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas_" wrote:
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to ad
d
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to u
se
> the second cpu or do i have to make any configuration changes for SQL serv
er
> to start use the second cpu.
> Thanks
> Thomas|||Thank you very much for your quick reply.
Regards
Thomas
"zashah@.gmail.com" wrote:
> Once you add the 2nd CPU and re-start the machine SQL Server will
> automatically recognise the extra processor.
> The only exception would be if you had a an Affinity Mask on (to
> restrict to 1 CPU) or were using MAXDOP but these aren't applicable in
> your case.
> ALI
>
> Thomas_ wrote:
>|||Like Mike said, SQL Server will can use the processors detected by the OS.
Depending on your Windows version, additional steps may be needed to install
a multiprocessor HAL:
http://support.microsoft.com/kb/234558
http://support.microsoft.com/defaul...kb;en-us;888729
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas_" <Thomas_@.discussions.microsoft.com> wrote in message
news:FCED2511-598D-4D4D-9E6D-C4FBE15B2075@.microsoft.com...
> Hi all.
> I have a 1 cpu server running sql server 2000 sp3a and we would like to
> add
> a second cpu to it and I wonder if it is just a matter of installaing the
> second CPU and then start the system and SQL server automaticly start to
> use
> the second cpu or do i have to make any configuration changes for SQL
> server
> to start use the second cpu.
> Thanks
> Thomas
Adding a script in Reporting Services
Hi.
I need to display a date with his serial week number of the year (from 1-52
weeks).
I wrote a class method that will receive this date and will return the
corresponding week number, and made a dll file out of it.
I added this dll as a reference to my report using Report->Report
Properties->Add Reference.
Now to the tricky part, how do I embed this code in my report?
I found some documented material that I need to uncomment the
<customAssembies> in the rsconfig file. I looked into my config file but no
such <customAssembies> exists.
Does anyone have an example of how to use external dll and to successfully
embed them in the report?
I will highly appreciate any word of wisdom...
Thanks,
GuyGuy,
I didn't figure out your dll question but I believe this will solve
your date problem.
=DatePart(DateInterval.WeekOfYear,Today)
or
=DatePart("ww",Today)
Hope this helps.
Here's a link to the Visual Basic Run-time Library Members
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
Steve|||Steve, thanks a lot.
You have been very helpful.
I also realized how to create GetWeekOfYear( ) in VB.Net and to embed this
function in my report as Code.GetWeekOfYear( ).
I would to rephrase my request for assistance:
1. I created a class in C# and the class method as GetWeekOfYear( ) and
create a dll file of this class.
2. I added this reference to an existing report using the Report->Add
reference command from the Menu toolbar.
3. According to MSDN documentation I need to uncomment the
<CustomAssemblies> in the RSReportingServer.config
However, I didn't find any <CustomAssemblies> in the
RSReportingServer.config, therefore I can't refer to my dll file which
contains the GetWeekOfYear( ) method.
I will appreciate any help in adding and using dll in the Reporting Services
(which is not written in VB.Net which is the natural language in this
environment).
Thanks,
Guy
"sjeffrey@.gmail.com" wrote:
> Guy,
> I didn't figure out your dll question but I believe this will solve
> your date problem.
> =DatePart(DateInterval.WeekOfYear,Today)
> or
> =DatePart("ww",Today)
> Hope this helps.
> Here's a link to the Visual Basic Run-time Library Members
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
> Steve
>|||Why not simply have a reference date table that contains a record for every
date and the week numbers in the record? Then you solve the problem with a
join at the data source. This reference date table has plenty of benefits
as mentioned in other recent posts.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"GuyR" <GuyR@.discussions.microsoft.com> wrote in message
news:4B1CFD27-5D6C-44BA-A07B-D2F186EC4876@.microsoft.com...
> Hi.
> I need to display a date with his serial week number of the year (from
> 1-52
> weeks).
> I wrote a class method that will receive this date and will return the
> corresponding week number, and made a dll file out of it.
> I added this dll as a reference to my report using Report->Report
> Properties->Add Reference.
> Now to the tricky part, how do I embed this code in my report?
> I found some documented material that I need to uncomment the
> <customAssembies> in the rsconfig file. I looked into my config file but
> no
> such <customAssembies> exists.
> Does anyone have an example of how to use external dll and to successfully
> embed them in the report?
> I will highly appreciate any word of wisdom...
> Thanks,
> Guy
>sql
I need to display a date with his serial week number of the year (from 1-52
weeks).
I wrote a class method that will receive this date and will return the
corresponding week number, and made a dll file out of it.
I added this dll as a reference to my report using Report->Report
Properties->Add Reference.
Now to the tricky part, how do I embed this code in my report?
I found some documented material that I need to uncomment the
<customAssembies> in the rsconfig file. I looked into my config file but no
such <customAssembies> exists.
Does anyone have an example of how to use external dll and to successfully
embed them in the report?
I will highly appreciate any word of wisdom...
Thanks,
GuyGuy,
I didn't figure out your dll question but I believe this will solve
your date problem.
=DatePart(DateInterval.WeekOfYear,Today)
or
=DatePart("ww",Today)
Hope this helps.
Here's a link to the Visual Basic Run-time Library Members
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
Steve|||Steve, thanks a lot.
You have been very helpful.
I also realized how to create GetWeekOfYear( ) in VB.Net and to embed this
function in my report as Code.GetWeekOfYear( ).
I would to rephrase my request for assistance:
1. I created a class in C# and the class method as GetWeekOfYear( ) and
create a dll file of this class.
2. I added this reference to an existing report using the Report->Add
reference command from the Menu toolbar.
3. According to MSDN documentation I need to uncomment the
<CustomAssemblies> in the RSReportingServer.config
However, I didn't find any <CustomAssemblies> in the
RSReportingServer.config, therefore I can't refer to my dll file which
contains the GetWeekOfYear( ) method.
I will appreciate any help in adding and using dll in the Reporting Services
(which is not written in VB.Net which is the natural language in this
environment).
Thanks,
Guy
"sjeffrey@.gmail.com" wrote:
> Guy,
> I didn't figure out your dll question but I believe this will solve
> your date problem.
> =DatePart(DateInterval.WeekOfYear,Today)
> or
> =DatePart("ww",Today)
> Hope this helps.
> Here's a link to the Visual Basic Run-time Library Members
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
> Steve
>|||Why not simply have a reference date table that contains a record for every
date and the week numbers in the record? Then you solve the problem with a
join at the data source. This reference date table has plenty of benefits
as mentioned in other recent posts.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"GuyR" <GuyR@.discussions.microsoft.com> wrote in message
news:4B1CFD27-5D6C-44BA-A07B-D2F186EC4876@.microsoft.com...
> Hi.
> I need to display a date with his serial week number of the year (from
> 1-52
> weeks).
> I wrote a class method that will receive this date and will return the
> corresponding week number, and made a dll file out of it.
> I added this dll as a reference to my report using Report->Report
> Properties->Add Reference.
> Now to the tricky part, how do I embed this code in my report?
> I found some documented material that I need to uncomment the
> <customAssembies> in the rsconfig file. I looked into my config file but
> no
> such <customAssembies> exists.
> Does anyone have an example of how to use external dll and to successfully
> embed them in the report?
> I will highly appreciate any word of wisdom...
> Thanks,
> Guy
>sql
Adding a restricted role to SQL Server 2005
I have a database and I want to grant an external user access to only one
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GO
Hi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>
|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...[vbcol=seagreen]
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GO
Hi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>
|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...[vbcol=seagreen]
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
Adding a restricted role to SQL Server 2005
I have a database and I want to grant an external user access to only one
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GOHi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
>> I have a database and I want to grant an external user access to only one
>> view in my database. Using SQL Studio I generated these scripts and ran
>> them.
>> Once I did that I logged into the database as [loginExternalApp] and was
>> happy that I could not see any of the raw tables. But I found that I
>> could
>> SELECT from all of the views in my database, not just the one I granted
>> the
>> SELECT to.
>> What did I miss? I thought by default that the new role would have no
>> permissions for anything that I did not grant. It doesn't seem right
>> that I
>> would need to specifically deny access to all of the views.
>> Any thoughts would be welcome.
>> Richard Speiss
>>
>> Here is the script that I ran
>> USE [master]
>> GO
>> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
>> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
>> GO
>> USE [TestDB]
>> GO
>> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
>> GO
>> CREATE ROLE [roleExternalApp]
>> GO
>> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
>> GO
>> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
>> GO
>>
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GOHi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
>> I have a database and I want to grant an external user access to only one
>> view in my database. Using SQL Studio I generated these scripts and ran
>> them.
>> Once I did that I logged into the database as [loginExternalApp] and was
>> happy that I could not see any of the raw tables. But I found that I
>> could
>> SELECT from all of the views in my database, not just the one I granted
>> the
>> SELECT to.
>> What did I miss? I thought by default that the new role would have no
>> permissions for anything that I did not grant. It doesn't seem right
>> that I
>> would need to specifically deny access to all of the views.
>> Any thoughts would be welcome.
>> Richard Speiss
>>
>> Here is the script that I ran
>> USE [master]
>> GO
>> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
>> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
>> GO
>> USE [TestDB]
>> GO
>> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
>> GO
>> CREATE ROLE [roleExternalApp]
>> GO
>> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
>> GO
>> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
>> GO
>>
Adding a resource (Generic Script) to the SQL Cluster
I would like to add a VB Script to the SQL Cluster so that
in the event of a failover the script will execute on the
new active node. The script goes out and checks for the
existance of some key files once a day.
I am running Windows 2003 SQL 2000 in an Active / Passive
configuration.
I have the VBS running every morning through task Mgr, but
I would like to get it configured to run as a clustered
resource and failover to the active node.
Thanks,
In 2003, there is a "Generic Script" resource type that support VBS and Perl
scripts. Check out the following for more details:
http://msdn.microsoft.com/library/de...ric_script.asp
Regards,
John
"Rodney" <anonymous@.discussions.microsoft.com> wrote in message
news:441301c47333$a3be4aa0$a601280a@.phx.gbl...
> I would like to add a VB Script to the SQL Cluster so that
> in the event of a failover the script will execute on the
> new active node. The script goes out and checks for the
> existance of some key files once a day.
> I am running Windows 2003 SQL 2000 in an Active / Passive
> configuration.
> I have the VBS running every morning through task Mgr, but
> I would like to get it configured to run as a clustered
> resource and failover to the active node.
> Thanks,
in the event of a failover the script will execute on the
new active node. The script goes out and checks for the
existance of some key files once a day.
I am running Windows 2003 SQL 2000 in an Active / Passive
configuration.
I have the VBS running every morning through task Mgr, but
I would like to get it configured to run as a clustered
resource and failover to the active node.
Thanks,
In 2003, there is a "Generic Script" resource type that support VBS and Perl
scripts. Check out the following for more details:
http://msdn.microsoft.com/library/de...ric_script.asp
Regards,
John
"Rodney" <anonymous@.discussions.microsoft.com> wrote in message
news:441301c47333$a3be4aa0$a601280a@.phx.gbl...
> I would like to add a VB Script to the SQL Cluster so that
> in the event of a failover the script will execute on the
> new active node. The script goes out and checks for the
> existance of some key files once a day.
> I am running Windows 2003 SQL 2000 in an Active / Passive
> configuration.
> I have the VBS running every morning through task Mgr, but
> I would like to get it configured to run as a clustered
> resource and failover to the active node.
> Thanks,
Adding a read only logins to a standby server
Hi,
I am running a standby server which backups my main Sql server. I also
use this server as readonly server and everything works fine with my
login but I no matter what I try I am not able to add new users to be
able to read from the standby server. Besides trying to add users
directly to the standby server (?which of course failed) I also tried
to add the same login on both servers allowing the new login to read
from the main database and then performed the "backup-restore"
routine.
Thanks,
RanIf you want this destination to become an available source
database, you must select the Allow database to assume
primary role check box. If this box is not selected, this
destination database will not be able to assume the source
database role in the future. If you have selected the
Allow database to assume primary role check box, you must
also specify the Transaction Log Backup Directory on the
destination database to which the logs will be backed up.
Koohyar
This posting is provided "AS IS" with no warranties, and
confers no rights.
>--Original Message--
>Hi,
>I am running a standby server which backups my main Sql
server. I also
>use this server as readonly server and everything works
fine with my
>login but I no matter what I try I am not able to add new
users to be
>able to read from the standby server. Besides trying to
add users
>directly to the standby server (.which of course failed)
I also tried
>to add the same login on both servers allowing the new
login to read
>from the main database and then performed the "backup-
restore"
>routine.
>Thanks,
>Ran
>.
>|||Here is a totally unsupported hack to the system tables. Use at your own
risk.
This runs on the standby server. It needs a linked server path to the
primary server. Create the logins on the Primary server and give them
permissions on the databases on the primary server. Run the following
script, changing the source server and the login name. Make sure the login
does not exist on the stand-by server before running this script.
insert master.dbo.sysxlogins
([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
[dbid], [language])
select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
[dbid], [language]
from [PrimaryServerName].master.dbo.sysxlogins
where [name] = 'MyLoginName'
and srvid is NULL
Again, this is totally unsupported and is completely at your own risk. If
you don't understand what it is doing, don't run it.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"ran" <rveierov@.hotmail.com> wrote in message
news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> Hi,
> I am running a standby server which backups my main Sql server. I also
> use this server as readonly server and everything works fine with my
> login but I no matter what I try I am not able to add new users to be
> able to read from the standby server. Besides trying to add users
> directly to the standby server (.which of course failed) I also tried
> to add the same login on both servers allowing the new login to read
> from the main database and then performed the "backup-restore"
> routine.
> Thanks,
> Ran|||Hi Koohyar,
I have no problem in making the destination become an available source
database and i don't see how this may help me solve my problem with
adding new logins to my secondary database.
thanks,
Ran
"koohyar[MSFT]" <koohyd@.online.microsoft.com> wrote in message news:<0a5701c38cea$0d3244e0$a001280a@.phx.gbl>...
> If you want this destination to become an available source
> database, you must select the Allow database to assume
> primary role check box. If this box is not selected, this
> destination database will not be able to assume the source
> database role in the future. If you have selected the
> Allow database to assume primary role check box, you must
> also specify the Transaction Log Backup Directory on the
> destination database to which the logs will be backed up.
> Koohyar
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >--Original Message--
> >Hi,
> >
> >I am running a standby server which backups my main Sql
> server. I also
> >use this server as readonly server and everything works
> fine with my
> >login but I no matter what I try I am not able to add new
> users to be
> >able to read from the standby server. Besides trying to
> add users
> >directly to the standby server (.which of course failed)
> I also tried
> >to add the same login on both servers allowing the new
> login to read
> >from the main database and then performed the "backup-
> restore"
> >routine.
> >
> >Thanks,
> >Ran
> >.
> >|||Hi Geoff,
I have tried this on my development environment, but I am keep getting
the following error message:
"Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this."
Is there anyway to get pass this restriction?
Thanks,
Ran
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OsCa4wOjDHA.2704@.TK2MSFTNGP10.phx.gbl>...
> Here is a totally unsupported hack to the system tables. Use at your own
> risk.
> This runs on the standby server. It needs a linked server path to the
> primary server. Create the logins on the Primary server and give them
> permissions on the databases on the primary server. Run the following
> script, changing the source server and the login name. Make sure the login
> does not exist on the stand-by server before running this script.
> insert master.dbo.sysxlogins
> ([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> [dbid], [language])
> select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> [dbid], [language]
> from [PrimaryServerName].master.dbo.sysxlogins
> where [name] = 'MyLoginName'
> and srvid is NULL
> Again, this is totally unsupported and is completely at your own risk. If
> you don't understand what it is doing, don't run it.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "ran" <rveierov@.hotmail.com> wrote in message
> news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> > Hi,
> >
> > I am running a standby server which backups my main Sql server. I also
> > use this server as readonly server and everything works fine with my
> > login but I no matter what I try I am not able to add new users to be
> > able to read from the standby server. Besides trying to add users
> > directly to the standby server (.which of course failed) I also tried
> > to add the same login on both servers allowing the new login to read
> > from the main database and then performed the "backup-restore"
> > routine.
> >
> > Thanks,
> > Ran|||Hi Geoff,
Thanks for the tip. it worked perfectly. (my other problem was solved
with sp_configure 'allow updates',1)
Thank,
Ran
rveierov@.hotmail.com (ran) wrote in message news:<fb241b13.0310072345.76828f86@.posting.google.com>...
> Hi Geoff,
> I have tried this on my development environment, but I am keep getting
> the following error message:
> "Server: Msg 259, Level 16, State 2, Line 1
> Ad hoc updates to system catalogs are not enabled. The system
> administrator must reconfigure SQL Server to allow this."
> Is there anyway to get pass this restriction?
> Thanks,
> Ran
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OsCa4wOjDHA.2704@.TK2MSFTNGP10.phx.gbl>...
> > Here is a totally unsupported hack to the system tables. Use at your own
> > risk.
> >
> > This runs on the standby server. It needs a linked server path to the
> > primary server. Create the logins on the Primary server and give them
> > permissions on the databases on the primary server. Run the following
> > script, changing the source server and the login name. Make sure the login
> > does not exist on the stand-by server before running this script.
> >
> > insert master.dbo.sysxlogins
> > ([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> > [dbid], [language])
> > select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> > [dbid], [language]
> > from [PrimaryServerName].master.dbo.sysxlogins
> > where [name] = 'MyLoginName'
> > and srvid is NULL
> >
> > Again, this is totally unsupported and is completely at your own risk. If
> > you don't understand what it is doing, don't run it.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> > "ran" <rveierov@.hotmail.com> wrote in message
> > news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> > > Hi,
> > >
> > > I am running a standby server which backups my main Sql server. I also
> > > use this server as readonly server and everything works fine with my
> > > login but I no matter what I try I am not able to add new users to be
> > > able to read from the standby server. Besides trying to add users
> > > directly to the standby server (.which of course failed) I also tried
> > > to add the same login on both servers allowing the new login to read
> > > from the main database and then performed the "backup-restore"
> > > routine.
> > >
> > > Thanks,
> > > Ransql
I am running a standby server which backups my main Sql server. I also
use this server as readonly server and everything works fine with my
login but I no matter what I try I am not able to add new users to be
able to read from the standby server. Besides trying to add users
directly to the standby server (?which of course failed) I also tried
to add the same login on both servers allowing the new login to read
from the main database and then performed the "backup-restore"
routine.
Thanks,
RanIf you want this destination to become an available source
database, you must select the Allow database to assume
primary role check box. If this box is not selected, this
destination database will not be able to assume the source
database role in the future. If you have selected the
Allow database to assume primary role check box, you must
also specify the Transaction Log Backup Directory on the
destination database to which the logs will be backed up.
Koohyar
This posting is provided "AS IS" with no warranties, and
confers no rights.
>--Original Message--
>Hi,
>I am running a standby server which backups my main Sql
server. I also
>use this server as readonly server and everything works
fine with my
>login but I no matter what I try I am not able to add new
users to be
>able to read from the standby server. Besides trying to
add users
>directly to the standby server (.which of course failed)
I also tried
>to add the same login on both servers allowing the new
login to read
>from the main database and then performed the "backup-
restore"
>routine.
>Thanks,
>Ran
>.
>|||Here is a totally unsupported hack to the system tables. Use at your own
risk.
This runs on the standby server. It needs a linked server path to the
primary server. Create the logins on the Primary server and give them
permissions on the databases on the primary server. Run the following
script, changing the source server and the login name. Make sure the login
does not exist on the stand-by server before running this script.
insert master.dbo.sysxlogins
([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
[dbid], [language])
select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
[dbid], [language]
from [PrimaryServerName].master.dbo.sysxlogins
where [name] = 'MyLoginName'
and srvid is NULL
Again, this is totally unsupported and is completely at your own risk. If
you don't understand what it is doing, don't run it.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"ran" <rveierov@.hotmail.com> wrote in message
news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> Hi,
> I am running a standby server which backups my main Sql server. I also
> use this server as readonly server and everything works fine with my
> login but I no matter what I try I am not able to add new users to be
> able to read from the standby server. Besides trying to add users
> directly to the standby server (.which of course failed) I also tried
> to add the same login on both servers allowing the new login to read
> from the main database and then performed the "backup-restore"
> routine.
> Thanks,
> Ran|||Hi Koohyar,
I have no problem in making the destination become an available source
database and i don't see how this may help me solve my problem with
adding new logins to my secondary database.
thanks,
Ran
"koohyar[MSFT]" <koohyd@.online.microsoft.com> wrote in message news:<0a5701c38cea$0d3244e0$a001280a@.phx.gbl>...
> If you want this destination to become an available source
> database, you must select the Allow database to assume
> primary role check box. If this box is not selected, this
> destination database will not be able to assume the source
> database role in the future. If you have selected the
> Allow database to assume primary role check box, you must
> also specify the Transaction Log Backup Directory on the
> destination database to which the logs will be backed up.
> Koohyar
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >--Original Message--
> >Hi,
> >
> >I am running a standby server which backups my main Sql
> server. I also
> >use this server as readonly server and everything works
> fine with my
> >login but I no matter what I try I am not able to add new
> users to be
> >able to read from the standby server. Besides trying to
> add users
> >directly to the standby server (.which of course failed)
> I also tried
> >to add the same login on both servers allowing the new
> login to read
> >from the main database and then performed the "backup-
> restore"
> >routine.
> >
> >Thanks,
> >Ran
> >.
> >|||Hi Geoff,
I have tried this on my development environment, but I am keep getting
the following error message:
"Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this."
Is there anyway to get pass this restriction?
Thanks,
Ran
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OsCa4wOjDHA.2704@.TK2MSFTNGP10.phx.gbl>...
> Here is a totally unsupported hack to the system tables. Use at your own
> risk.
> This runs on the standby server. It needs a linked server path to the
> primary server. Create the logins on the Primary server and give them
> permissions on the databases on the primary server. Run the following
> script, changing the source server and the login name. Make sure the login
> does not exist on the stand-by server before running this script.
> insert master.dbo.sysxlogins
> ([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> [dbid], [language])
> select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> [dbid], [language]
> from [PrimaryServerName].master.dbo.sysxlogins
> where [name] = 'MyLoginName'
> and srvid is NULL
> Again, this is totally unsupported and is completely at your own risk. If
> you don't understand what it is doing, don't run it.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "ran" <rveierov@.hotmail.com> wrote in message
> news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> > Hi,
> >
> > I am running a standby server which backups my main Sql server. I also
> > use this server as readonly server and everything works fine with my
> > login but I no matter what I try I am not able to add new users to be
> > able to read from the standby server. Besides trying to add users
> > directly to the standby server (.which of course failed) I also tried
> > to add the same login on both servers allowing the new login to read
> > from the main database and then performed the "backup-restore"
> > routine.
> >
> > Thanks,
> > Ran|||Hi Geoff,
Thanks for the tip. it worked perfectly. (my other problem was solved
with sp_configure 'allow updates',1)
Thank,
Ran
rveierov@.hotmail.com (ran) wrote in message news:<fb241b13.0310072345.76828f86@.posting.google.com>...
> Hi Geoff,
> I have tried this on my development environment, but I am keep getting
> the following error message:
> "Server: Msg 259, Level 16, State 2, Line 1
> Ad hoc updates to system catalogs are not enabled. The system
> administrator must reconfigure SQL Server to allow this."
> Is there anyway to get pass this restriction?
> Thanks,
> Ran
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OsCa4wOjDHA.2704@.TK2MSFTNGP10.phx.gbl>...
> > Here is a totally unsupported hack to the system tables. Use at your own
> > risk.
> >
> > This runs on the standby server. It needs a linked server path to the
> > primary server. Create the logins on the Primary server and give them
> > permissions on the databases on the primary server. Run the following
> > script, changing the source server and the login name. Make sure the login
> > does not exist on the stand-by server before running this script.
> >
> > insert master.dbo.sysxlogins
> > ([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> > [dbid], [language])
> > select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> > [dbid], [language]
> > from [PrimaryServerName].master.dbo.sysxlogins
> > where [name] = 'MyLoginName'
> > and srvid is NULL
> >
> > Again, this is totally unsupported and is completely at your own risk. If
> > you don't understand what it is doing, don't run it.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> > "ran" <rveierov@.hotmail.com> wrote in message
> > news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> > > Hi,
> > >
> > > I am running a standby server which backups my main Sql server. I also
> > > use this server as readonly server and everything works fine with my
> > > login but I no matter what I try I am not able to add new users to be
> > > able to read from the standby server. Besides trying to add users
> > > directly to the standby server (.which of course failed) I also tried
> > > to add the same login on both servers allowing the new login to read
> > > from the main database and then performed the "backup-restore"
> > > routine.
> > >
> > > Thanks,
> > > Ransql
adding a query as parameter using 'contains' logic
Hello,
Setting parameter ranges for a query is very useful.
How can I specify : begins with ..... and make that a parameter (in this
case first 5 digits of a project name)?
Liek '%[parameter3]%' does not work.
What should it be?
Help much appreciated,
An 'over-his-head' Texas Tonie
Texas Tonie,
If you want a range of values, look at the BETWEEN operator. For character
strings, however, the whole issue of collation and sort order needs to be
considered when defining what is between.
For your example problem, assuming that the 'number' is actually a character
string (not some sort of numeric datatype) and that the number may have
leading spaces, then you could use:
DECLARE @.Value = ' 12345'
SELECT @.Value WHERE LTRIM(@.Value) LIKE '12345%'
FWIW,
RLF
"Texas Tonie" <TexasTonie@.discussions.microsoft.com> wrote in message
news:72C622EC-3C49-41C7-8259-2274E9396CD3@.microsoft.com...
> Hello,
> Setting parameter ranges for a query is very useful.
> How can I specify : begins with ..... and make that a parameter (in this
> case first 5 digits of a project name)?
> Liek '%[parameter3]%' does not work.
> What should it be?
> Help much appreciated,
> An 'over-his-head' Texas Tonie
Setting parameter ranges for a query is very useful.
How can I specify : begins with ..... and make that a parameter (in this
case first 5 digits of a project name)?
Liek '%[parameter3]%' does not work.
What should it be?
Help much appreciated,
An 'over-his-head' Texas Tonie
Texas Tonie,
If you want a range of values, look at the BETWEEN operator. For character
strings, however, the whole issue of collation and sort order needs to be
considered when defining what is between.
For your example problem, assuming that the 'number' is actually a character
string (not some sort of numeric datatype) and that the number may have
leading spaces, then you could use:
DECLARE @.Value = ' 12345'
SELECT @.Value WHERE LTRIM(@.Value) LIKE '12345%'
FWIW,
RLF
"Texas Tonie" <TexasTonie@.discussions.microsoft.com> wrote in message
news:72C622EC-3C49-41C7-8259-2274E9396CD3@.microsoft.com...
> Hello,
> Setting parameter ranges for a query is very useful.
> How can I specify : begins with ..... and make that a parameter (in this
> case first 5 digits of a project name)?
> Liek '%[parameter3]%' does not work.
> What should it be?
> Help much appreciated,
> An 'over-his-head' Texas Tonie
Adding a primary key to a table with two records
This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?
Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...[vbcol=seagreen]
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:
> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
>
|||Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||another thing too (contributing an answer to my own question here) I couldn't
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:
> Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?
Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...[vbcol=seagreen]
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:
> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
>
|||Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||another thing too (contributing an answer to my own question here) I couldn't
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:
> Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
Subscribe to:
Posts (Atom)