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
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...
>
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...
>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?|||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...
> > 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?
>
>|||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...
> > 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?
>
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...
>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?|||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...
> > 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?
>
>|||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...
> > 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?
>
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 i
mprove 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 i
mprove 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...
>
Adding a ODBC link to many computers
Is there any way of adding an OBDC connection to multiple computers without
going to each system. I need to push out a new SQL Server connection, does
anyone know an easy way.
Thanks!
Use a DSN-less connection.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"HenryG" <HenryG@.discussions.microsoft.com> wrote in message
news:49B3EF04-D325-462C-9FFC-A79600D0CAD9@.microsoft.com...
> Is there any way of adding an OBDC connection to multiple computers
> without
> going to each system. I need to push out a new SQL Server connection,
> does
> anyone know an easy way.
> Thanks!
sql
going to each system. I need to push out a new SQL Server connection, does
anyone know an easy way.
Thanks!
Use a DSN-less connection.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"HenryG" <HenryG@.discussions.microsoft.com> wrote in message
news:49B3EF04-D325-462C-9FFC-A79600D0CAD9@.microsoft.com...
> Is there any way of adding an OBDC connection to multiple computers
> without
> going to each system. I need to push out a new SQL Server connection,
> does
> anyone know an easy way.
> Thanks!
sql
Tuesday, March 27, 2012
Adding a not null column to replicated table
Hi,
I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:
ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)
which executed OK. When I went to synchronize the db on the mobile device I got the following error:
Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]
Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?
Regards,
Greg
I doubt this is a replication problem, but I don't have a SQL Compact db handy, can you try to create a new table on the compact db and run the TSQL command to see if SQL Compact even allows this?|||Hi Greg,
Thanks for your reply. I created a new database on the device (not replicated), created a table "Activity", added some records and then executed the same script above in Query Analyzer and it worked fine. It only seems to be a problem when the command is run by the replication agent. Any ideas?
Regards,
Greg
|||can you tell me what version of ce you're using?|||Hi,
I'm running SQL Server Compact (3.1) on Windows Mobile 5.0 (HP iPaq), and the server is SQL Server 2005 SP2.
Regards,
Greg
|||Some extra information: I tried to get the subscriber to synchronize by reinitializing all subscriptions (with a new snapshot and "upload unsynchronized changes" set to yes). I had thought that this would regenerate the database at the subscriber after uploading subscriber changes, but it still comes up with the original error message. It's trying to add the not null column but seems to ignore the default constraint.
Any help on this would be greatly appreciated. At this stage I'm not really able to make any db schema changes on the server without deleting subscriber dbs and starting from scratch.
Regards,
Greg
|||We're trying to track down some answers for you, so please be patient.|||This is a confirmed bug which we're trying to nail the root cause, thanks for raising this issue.
To get around your problem, you should do the following:
1. Reinitialize your subscriptions
2. Generate a new snapshot
3. Rerun your sync agents to apply the new snapshot
If you do this in order, you shouldn't hit the error because the new snapshot should generate a table with the new column. let me know if this still doesn't work.
|||Thanks Greg,
As per my previous post, the main issue is that this problem is not resolved by reinitializing subscriptions with a new snapshot. I still get the error. The only solution I've found so far is to delete the subscriber database and synchronize again.
Regards,
Greg
|||Hi,
This problem was seriously affecting the development and testing of this application, so I opened a support incident with Microsoft. The tech advised that I should use the stored procedure sp_repladdcolumn to add the column. It worked! Despite the documentation saying that this method is deprecated and should only be used with SQL Server 2000, it seems that this is only way to add a not null column with a default value to a SQL Server 2005 db table and have it successfully replicate to SQL Mobile/Compact (if anyone has any alternatives to this, please advise). This also seems to be a solution to another problem I've had with "could not enumerate changes at the subscriber" error when synchronizing after changing the db schema. Using the old stored procedures instead of "alter table" commands seems to fix this.
I hope MS will add a KB article about this so others won't have spend as much time as I have trying to get an answer.
Regards,
Greg
|||It's a bug in CE, not replication. Earlier I asked if you could run the alter table statement on your CE device to see if that failed, you said it passed but I think you ran it on your SQL Server 2005 server and not the CE. Running the alter table statement explicitly on the CE device will fail, if you remove the default name, then it works - CE should not choke like this. I'm glad you found a solution, a bug was already file, CE folks will be fixing this hopefully soon.Adding a new VarBinaryMax column to an existing table using SMO.
Consider the following code.
Table applicationSettingsTable = database.Tables["application_settings"];
Column logoColumn = new Column(applicationSettingsTable, "logo", DataType.VarBinaryMax);
applicationSettingsTable.Columns.Add(logoColumn);
applicationSettingsTable.Alter();
I don't understand why - but when I execute this code I end up with a column that has the data type varbinary(1) in the database rather than varbinary(MAX). I can't see anything in the msdn documentation that tells me I need to do something special for varbinary(max) columns, but maybe I do?
Any help much appreciated.
This appears to work tho...
Column logoColumn = new Column(applicationSettingsTable, "logo", DataType.VarBinary(-1));
Adding a new user login
In Ms SQL 2000, I want to be able to share my database with another user but
I only want them to be able to look at the database and not be able to
change anything.
I only want them to be able to look at the database and not be able to
change anything.
Does anyone know how I go about doing this?Just use sp_adduser from the desired db & use sp_addrolemember if necessary
Adding a new table to a complex join statement
All help appreciated.
I currently use the following select statement as a custom query.
SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
[setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
=[glinfo].[gl_spon] WHERE ....... (etc, etc, etc...)
Inventory info is stored in a fourth table called invinfo, key
[invinfo].inv_id], which equals [glinfo].[gl_id].
I'm having syntax trouble getting the inventory table joined. Can anyone
show me the syntax to add the invinfo table to this query?
Thanks!
Steveright outer joins and i do not get along -- they're not hard to understand, just backwards
here is your query written left to right -- select list, the, columns, you, want
from glinfo
left outer
join setinfo
on glinfo.gl_set = setinfo.SET_ID
left outer
join sponinfo
on glinfo.gl_spon = sponinfo.spon_idnow to bring in the other table, just addleft outer
join invinfo
on glinfo.gl_id = invinfo.inv_idordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --select list, the, columns, you, want
from ( ( glinfo
left outer
join setinfo
on [glinfo].[gl_set] = [setinfo].[SET_ID] )
left outer
join sponinfo
on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
left outer
join invinfo
on [glinfo].[gl_id] = [invinfo].[inv_id]
rudysql
I currently use the following select statement as a custom query.
SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
[setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
=[glinfo].[gl_spon] WHERE ....... (etc, etc, etc...)
Inventory info is stored in a fourth table called invinfo, key
[invinfo].inv_id], which equals [glinfo].[gl_id].
I'm having syntax trouble getting the inventory table joined. Can anyone
show me the syntax to add the invinfo table to this query?
Thanks!
Steveright outer joins and i do not get along -- they're not hard to understand, just backwards
here is your query written left to right -- select list, the, columns, you, want
from glinfo
left outer
join setinfo
on glinfo.gl_set = setinfo.SET_ID
left outer
join sponinfo
on glinfo.gl_spon = sponinfo.spon_idnow to bring in the other table, just addleft outer
join invinfo
on glinfo.gl_id = invinfo.inv_idordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --select list, the, columns, you, want
from ( ( glinfo
left outer
join setinfo
on [glinfo].[gl_set] = [setinfo].[SET_ID] )
left outer
join sponinfo
on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
left outer
join invinfo
on [glinfo].[gl_id] = [invinfo].[inv_id]
rudysql
Adding a New Table and EXCLUDING from merge replication
New to merge replication - sql 2000
I simply wish to add a table to the publisher database and this table
will never be replicated. Is there anything I need to do to exclude it?
Thanks,
Craig
Craig,
no problems here - creating the table using Enterprise Manager or using
Create Table will not automatically add it to the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I simply wish to add a table to the publisher database and this table
will never be replicated. Is there anything I need to do to exclude it?
Thanks,
Craig
Craig,
no problems here - creating the table using Enterprise Manager or using
Create Table will not automatically add it to the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
adding a new subscriber
Hi
I have merge replication. One client is already subscribed. I need to add
another client to the publication
The database is pretty big, so I don't want to send the whole db over the
internet.
I want to back it up and restore it, then establish the subscription.
Are the steps
a) backup database
b) recreate snapshot
c) restored db at subcriber B
d) establish subscription at subcriber B
I would assume I don't want any changes to the database made from the other
subscriber between steps a) and d)
Is this right. And I hope recreating the snapshot will not 'damage'
subscriber A
Thanks
Bruce
Thanks
Bruce
Bruce,
if you want a pull subscription, the easiest way is to create an Attachable
Subscription Database (details in BOL). If not, then you can backup and
restore and subscribe without initialization - @.sync_type = none.
HTH,
Paul Ibison
|||Bear in mind that if the database is too large the attachable subscription
will not work. The compressed databse is limited to 2 gig in size so a
large database will have problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I have merge replication. One client is already subscribed. I need to add
another client to the publication
The database is pretty big, so I don't want to send the whole db over the
internet.
I want to back it up and restore it, then establish the subscription.
Are the steps
a) backup database
b) recreate snapshot
c) restored db at subcriber B
d) establish subscription at subcriber B
I would assume I don't want any changes to the database made from the other
subscriber between steps a) and d)
Is this right. And I hope recreating the snapshot will not 'damage'
subscriber A
Thanks
Bruce
Thanks
Bruce
Bruce,
if you want a pull subscription, the easiest way is to create an Attachable
Subscription Database (details in BOL). If not, then you can backup and
restore and subscribe without initialization - @.sync_type = none.
HTH,
Paul Ibison
|||Bear in mind that if the database is too large the attachable subscription
will not work. The compressed databse is limited to 2 gig in size so a
large database will have problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.
adding a new sql server node to an existing sql cluster
I currently have a single node SQL 2000 cluster running on 2003 Server. I now
want to add the second node, but want to make sure I fully understand the
process. I run the SQL setup from the new node and select the advanced option
for failover clustering. It all seems very straight forward, but I want to be
sure. Does this cause the clustered SQL instance to be restarted, or is there
no outage?
Any help confirming this would be appreciated.
The cluster won't restart when you add a new node. Be sure to add the SQL
service pack to the new node while the *OLD* node owns the SQL Server
resource. This will simply add the binaries to the new node without
interrupting SQL. If you need to add a hotfix, however, you'll have to do
that on the mode that owns SQL Server. Therefore, you'll have to failover
to the new node and add the hotfix from there.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stephan" <Stephan@.discussions.microsoft.com> wrote in message
news:48266C9D-90DC-418B-B3FA-9B91FA8A9E04@.microsoft.com...
I currently have a single node SQL 2000 cluster running on 2003 Server. I
now
want to add the second node, but want to make sure I fully understand the
process. I run the SQL setup from the new node and select the advanced
option
for failover clustering. It all seems very straight forward, but I want to
be
sure. Does this cause the clustered SQL instance to be restarted, or is
there
no outage?
Any help confirming this would be appreciated.
|||Thanks, Tom, that's what I thought.
"Tom Moreau" wrote:
> The cluster won't restart when you add a new node. Be sure to add the SQL
> service pack to the new node while the *OLD* node owns the SQL Server
> resource. This will simply add the binaries to the new node without
> interrupting SQL. If you need to add a hotfix, however, you'll have to do
> that on the mode that owns SQL Server. Therefore, you'll have to failover
> to the new node and add the hotfix from there.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Stephan" <Stephan@.discussions.microsoft.com> wrote in message
> news:48266C9D-90DC-418B-B3FA-9B91FA8A9E04@.microsoft.com...
> I currently have a single node SQL 2000 cluster running on 2003 Server. I
> now
> want to add the second node, but want to make sure I fully understand the
> process. I run the SQL setup from the new node and select the advanced
> option
> for failover clustering. It all seems very straight forward, but I want to
> be
> sure. Does this cause the clustered SQL instance to be restarted, or is
> there
> no outage?
> Any help confirming this would be appreciated.
>
want to add the second node, but want to make sure I fully understand the
process. I run the SQL setup from the new node and select the advanced option
for failover clustering. It all seems very straight forward, but I want to be
sure. Does this cause the clustered SQL instance to be restarted, or is there
no outage?
Any help confirming this would be appreciated.
The cluster won't restart when you add a new node. Be sure to add the SQL
service pack to the new node while the *OLD* node owns the SQL Server
resource. This will simply add the binaries to the new node without
interrupting SQL. If you need to add a hotfix, however, you'll have to do
that on the mode that owns SQL Server. Therefore, you'll have to failover
to the new node and add the hotfix from there.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stephan" <Stephan@.discussions.microsoft.com> wrote in message
news:48266C9D-90DC-418B-B3FA-9B91FA8A9E04@.microsoft.com...
I currently have a single node SQL 2000 cluster running on 2003 Server. I
now
want to add the second node, but want to make sure I fully understand the
process. I run the SQL setup from the new node and select the advanced
option
for failover clustering. It all seems very straight forward, but I want to
be
sure. Does this cause the clustered SQL instance to be restarted, or is
there
no outage?
Any help confirming this would be appreciated.
|||Thanks, Tom, that's what I thought.
"Tom Moreau" wrote:
> The cluster won't restart when you add a new node. Be sure to add the SQL
> service pack to the new node while the *OLD* node owns the SQL Server
> resource. This will simply add the binaries to the new node without
> interrupting SQL. If you need to add a hotfix, however, you'll have to do
> that on the mode that owns SQL Server. Therefore, you'll have to failover
> to the new node and add the hotfix from there.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Stephan" <Stephan@.discussions.microsoft.com> wrote in message
> news:48266C9D-90DC-418B-B3FA-9B91FA8A9E04@.microsoft.com...
> I currently have a single node SQL 2000 cluster running on 2003 Server. I
> now
> want to add the second node, but want to make sure I fully understand the
> process. I run the SQL setup from the new node and select the advanced
> option
> for failover clustering. It all seems very straight forward, but I want to
> be
> sure. Does this cause the clustered SQL instance to be restarted, or is
> there
> no outage?
> Any help confirming this would be appreciated.
>
Adding a new row to SQL table
Hi all,
I have a database setup with a few rows and i would like to allow a user to create a new row of data. After some googling, I've deduced that I need to somehow use an onclick command to perform an insert command. here is my code as of right now:
protectedvoid ItemAdd_Click(object sender,EventArgs e)
{
int newnum = 4;int POnum = newnum ++;
string Item = textbox3.Text.ToString();
string Quantity = textbox4.ToString();
string Part = textbox5.ToString();string Description = textbox6.Text.ToString();
string Price = textbox7.Text.ToString();string UOM = textbox8.Text.ToString();SqlDataSource2.InsertParameters.Clear();
SqlDataSource2.InsertParameters.Add("@.Item", Item);
SqlDataSource2.InsertParameters.Add("@.Quantity", Quantity);SqlDataSource2.InsertParameters.Add("@.Part", Part);
SqlDataSource2.InsertParameters.Add("@.Description", Description);SqlDataSource2.InsertParameters.Add("@.Price", Price);
SqlDataSource2.InsertParameters.Add("@.UOM", UOM);SqlDataSource1.InsertCommand ="insert into ItemMaster "+
"values (12, @.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM)";SqlDataSource2.Insert();
}
here's the aspx:
<%@.PageLanguage="C#"MasterPageFile="~/MasterPage.master"AutoEventWireup="true"CodeFile="createpo.aspx.cs"Inherits="Subpgs_Purchasing_createpo"Title="Create A PO" %>
<asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">
<tablestyle="width: 600px; position: relative; top: 30px; height: 253px"cellpadding="10px">
<tr>
<tdstyle="width: 180px; text-align: left;"rowspan="2">
Select Vendor<br/>- or -<br/><asp:LinkButtonID="Linkbutton1"runat="server"OnClick="Linkbutton1_Click">Create Vendor</asp:LinkButton></td>
<tdstyle="width: 100px"rowspan="2">
<asp:ListBoxID="ListBox2"runat="server"DataSourceID="SqlDataSource1"DataTextField="Name"
DataValueField="ID"OnSelectedIndexChanged="ListBox2_SelectedIndexChanged"></asp:ListBox><asp:SqlDataSourceID="SqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="SELECT * FROM [Vendor_info]"></asp:SqlDataSource>
</td>
<tdstyle="width: 180px; text-align: left;">
Job/Req.S.O. No.</td>
<tdstyle="width: 100px">
<asp:TextBoxID="Job"runat="server"></asp:TextBox></td>
</tr>
<tr>
<tdstyle="width: 180px; text-align: left;">
Terms</td>
<tdstyle="width: 100px">
<asp:TextBoxID="Terms"runat="server"></asp:TextBox></td>
</tr>
<tr>
<tdstyle="width: 180px; text-align: left;">
F.O.B.</td>
<tdstyle="width: 100px">
<asp:TextBoxID="FOB"runat="server"></asp:TextBox></td>
<tdstyle="width: 180px; text-align: left;">
Ship To:<br/>
Address:<br/>
City / State / Zip:</td>
<tdstyle="width: 100px">
<asp:TextBoxID="Ship1"runat="server"></asp:TextBox>
<asp:TextBoxID="Ship2"runat="server"></asp:TextBox>
<asp:TextBoxID="Ship3"runat="server"></asp:TextBox></td>
</tr>
</table>
<br/>
<br/>
<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Purchase_Orders.mdf;Integrated Security=True;User Instance=True;Context Connection=False"
ProviderName="System.Data.SqlClient"SelectCommand="SELECT [Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber] FROM [ItemMaster]"DeleteCommand="DELETE FROM [ItemMaster] WHERE [PONumber] = @.PONumber"InsertCommand="INSERT INTO [ItemMaster] ([Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber]) VALUES (@.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM, @.Ammount, @.PONumber)"UpdateCommand="UPDATE [ItemMaster] SET [Item] = @.Item, [Quantity] = @.Quantity, [Part] = @.Part, [Description] = @.Description, [Price] = @.Price, [UOM] = @.UOM, [Ammount] = @.Ammount WHERE [PONumber] = @.PONumber">
<DeleteParameters>
<asp:ParameterName="PONumber"Type="Decimal"/>
</DeleteParameters>
<UpdateParameters>
<asp:ParameterName="Item"Type="String"/>
<asp:ParameterName="Quantity"Type="Decimal"/>
<asp:ParameterName="Part"Type="String"/>
<asp:ParameterName="Description"Type="String"/>
<asp:ParameterName="Price"Type="Decimal"/>
<asp:ParameterName="UOM"Type="String"/>
<asp:ParameterName="Ammount"Type="Decimal"/>
<asp:ParameterName="PONumber"Type="Decimal"/>
</UpdateParameters>
<InsertParameters>
<asp:ParameterName="Item"Type="String"/>
<asp:ParameterName="Quantity"Type="Decimal"/>
<asp:ParameterName="Part"Type="String"/>
<asp:ParameterName="Description"Type="String"/>
<asp:ParameterName="Price"Type="Decimal"/>
<asp:ParameterName="UOM"Type="String"/>
<asp:ParameterName="Ammount"Type="Decimal"/>
<asp:ParameterName="PONumber"Type="Decimal"/>
</InsertParameters>
</asp:SqlDataSource>
<br/>
<table>
<tr>
<td>Item #</td>
<td><asp:TextBoxID="textbox3"runat=server></asp:TextBox></td>
<td>Quantity</td>
<td><asp:TextBoxID="textbox4"runat=server></asp:TextBox></td>
<td>Part Number</td>
<td><asp:TextBoxID="textbox5"runat=server></asp:TextBox></td></tr>
<tr>
<td>Description</td>
<td><asp:TextBoxID="textbox6"runat=server></asp:TextBox></td>
<td>Unit Price</td>
<td><asp:TextBoxID="textbox7"runat=server></asp:TextBox></td>
<td>Unit of Measure</td>
<td><asp:TextBoxID="textbox8"runat=server></asp:TextBox></td>
</tr>
<tr>
<tdcolspan="6"align="center"><asp:ButtonID="ItemAdd"runat=servertext="Add Item"OnClick="ItemAdd_Click"/></td>
</tr>
</table>
<br/>
<br/>
<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="PONumber"
DataSourceID="SqlDataSource2">
<Columns>
<asp:CommandFieldShowEditButton="True"/>
<asp:BoundFieldDataField="Item"HeaderText="Item"SortExpression="Item"/>
<asp:BoundFieldDataField="Quantity"HeaderText="Quantity"SortExpression="Quantity"/>
<asp:BoundFieldDataField="Part"HeaderText="Part"SortExpression="Part"/>
<asp:BoundFieldDataField="Description"HeaderText="Description"SortExpression="Description"/>
<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>
<asp:BoundFieldDataField="UOM"HeaderText="UOM"SortExpression="UOM"/>
<asp:BoundFieldDataField="Ammount"HeaderText="Ammount"SortExpression="Ammount"/>
<asp:BoundFieldDataField="PONumber"HeaderText="PONumber"ReadOnly="True"SortExpression="PONumber"
Visible="False"/>
</Columns>
</asp:GridView>
<br/>
<br/>
<br/>
<asp:ButtonID="Button1"runat="server"Text="Submit Data"OnClick="Button1_Click"/> <br/>
<br/>
<asp:LabelID="Label1"runat="server"Text="Please Verify Information below!"Visible="False"></asp:Label><br/>
<br/>
<tablealign="left">
<tr>
<tdstyle="width: 200px; text-align: left;">
<asp:LabelID="Label4"runat="server"Text="Job/REQ.S.O. No."Visible="false"></asp:Label></td>
<tdstyle="width: 200px; text-align: left;">
<asp:LabelID="Label5"runat="server"></asp:Label></td>
</tr>
<tr>
<tdstyle="width: 200px; text-align: left;">
<asp:LabelID="Label7"runat="server"Text="Terms"Visible="false"></asp:Label></td>
<tdstyle="width: 200px; text-align: left;">
<asp:LabelID="Label8"runat="server"></asp:Label></td>
</tr>
<tr>
<tdstyle="width: 200px; text-align: left;">
<asp:LabelID="Label10"runat="server"Text="F.O.B."Visible="false"></asp:Label></td>
<tdstyle="width: 200px; text-align: left;">
<asp:LabelID="Label11"runat="server"></asp:Label></td>
</tr>
</table>
<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataSourceID="SqlDataSource1"
Height="50px"Visible="False"Width="260px"BackColor="#CCCCCC"BorderColor="#999999"BorderStyle="Solid"BorderWidth="3px"CellPadding="4"CellSpacing="2"DataKeyNames="ID"ForeColor="Black">
<FooterStyleBackColor="#CCCCCC"/>
<EditRowStyleBackColor="#000099"Font-Bold="True"ForeColor="White"/>
<RowStyleBackColor="White"/>
<PagerStyleBackColor="#CCCCCC"ForeColor="Black"HorizontalAlign="Left"/>
<Fields>
<asp:BoundFieldDataField="ID"HeaderText="ID"ReadOnly="True"SortExpression="ID"/>
<asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>
<asp:BoundFieldDataField="Street"HeaderText="Street"SortExpression="Street"/>
<asp:BoundFieldDataField="City"HeaderText="City"SortExpression="City"/>
<asp:BoundFieldDataField="State"HeaderText="State"SortExpression="State"/>
<asp:BoundFieldDataField="Zip"HeaderText="Zip"SortExpression="Zip"/>
<asp:BoundFieldDataField="Telephone Number"HeaderText="Telephone Number"SortExpression="Telephone Number"/>
<asp:BoundFieldDataField="Contact Information"HeaderText="Contact Information"
SortExpression="Contact Information"/>
<asp:BoundFieldDataField="E-mail address"HeaderText="E-mail address"SortExpression="E-mail address"/>
<asp:BoundFieldDataField="Fax Number"HeaderText="Fax Number"SortExpression="Fax Number"/>
<asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/>
</Fields>
<HeaderStyleBackColor="Black"Font-Bold="True"ForeColor="White"/>
</asp:DetailsView>
<br/>
<br/>
<br/>
<asp:ButtonID="Button2"runat="server"Text="E-Mail PO"Visible="False"OnClick="Button2_Click"/>
</asp:Content>
whenever I run this, I receive the following error and am not sure how to correct it:
System.Data.SqlClient.SqlException: Must declare the scalar variable "@.Item".
If someone could please explain to me the process here of what is going ion it would be greatly appreciated. My understanding is the @.item is just a variable if you will so i'm not sure why its stating i need to declare it.
Thank you all!
Welcome to the community. I would suggest you use the InsertCode option to insert code in your posts from next time. Lets start from the starting and then we will remove errors as we go.
Gizm0:
string Item = textbox3.Text.ToString();
string Quantity = textbox4.ToString();string Part = textbox5.ToString();
I am assuming these are your textboxes. You should use Text property of textboxes like you used for others. Also, you don't have to use ToString() method. Text property returns the textbox value as a string.
|||Hi Bullpit.
Sorry about that, I didn't see the code Icon there. Anyhow, I made the changes and continue to receive the same error. At first glance, does it at least look like i'm on the right path? I've always been more hardware/network inclined so a lot of the google searches and forums I used to piece this together are still Greek to me.
Thanks for the response!
|||I don't use SqlDataSource as such. Since you are writing some code, you can instead use this code to insert values to your database. Extend this example to add more parameters.
protected void ItemAdd_Click(object sender, EventArgs e) {// connection string to your database SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");// open connection mySqlConnection.Open();// create command object to execute query SqlCommand mySqlCommand = mySqlConnection.CreateCommand();// set command text to the INSERT sql query mySqlCommand.CommandText = @."INSERT INTO Employee (Item, Quantity, Part...) VALUES( @.Item, @.Quantity, @.Part...)";// Add parameters mySqlCommand.Parameters.Add("@.Item", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Quantity", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Part", SqlDbType.VarChar);// set parameter values mySqlCommand.Parameters["@.Item"].Value = textbox3.Text; mySqlCommand.Parameters["@.Quantity"].Value = textbox4.Text; mySqlCommand.Parameters["@.Part"].Value = textbox5.Text;// execute query mySqlCommand.ExecuteNonQuery();// close connection mySqlConnection.Close();}You will need these namespaces.
using System.Data;
using System.Data.SqlClient;
|||
Hey BullPit,
I really appreciate the help! I was able to get this working using your code above. Now it looks like I just need to validate the input boxes to make sure the correct data is being entered in the fields.
Again, thanks!
sql
Subscribe to:
Posts (Atom)