2012年3月29日星期四
BcpBatchSize problem in replication
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
Ron
Any ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>
BcpBatchSize problem in replication
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
RonAny ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>
BcpBatchSize problem in replication
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
RonAny ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>
BCPBatchSize and Replication hanging
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
Ron
fiddling with the bcpbatchsize is not always a good idea. What this does is
determines how many rows are processed before they are committed. Setting
this to a high value means your tlog on your subscriber will grow and the
bcp process (performed by odbcbcp) will chug along longer before sending
messages back to the SQL Server agent. This will cause the "waiting for
response from the backend".
using a small value for bcpbatchsize will take longer, but is more chatty
and you won't get this message.
Think of it like the way your girlfriend blows your check. Give her a large
amount of money and you won't hear from her for weeks. On the other hand
give her a small amount of money, and she will be back in a few hours. Give
her to little money and she will never come back. So you have to balance the
amount of money you give her with how frequently you want to see her. Same
with bcpbatchsize - but I digress.
If your subscriber is a multiproc machine think about using UseInprocLoader
switch.
But, I'm a little confused - are you regenerating your snapshot? Are the
tables whose column names you have changed large?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ron" <rgellenbecker@.wonderboxtech.com> wrote in message
news:Xns95E29DD9C85B5WBT39312565@.207.46.248.16...
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The servers
> are running windows 2003. Everything is enterprise edition and the sql
> servers are using about 14 gig memory.
> We restore the database to the second server and then enable publication
> to that server turning of the generation of SPs but enabling the use of
> column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just fine.
> (before we started using column names).
> Up until this past weekend we did not need the column name option in the
> replication distribution and everything ran fine. Some table structures
> changed but not that big of changes but we do not have about 60 ident
> columns that we are dealing with.
> Starting this Monday we needed to use the column name option because we
> are replicating to some large tables that have identity columns on them.
> We have enabled the not for replication property on the columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it a
> couple of times and each time the distribution job ran fine for a while
> and then would just hang. Once it would hang there was no getting past
> that point.
> I have traced the replication process and it just stops sending data. I
> have also enabled the output of the distribution job to a file and it
> shows more data being sent than I see in the sql trace. The trace is
> not missing data because it always stops at the same spot (I tried it
> several times) and the output file always stops also but shows alot more
> data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the way
> down to 1 or 2 but the trace still shows many more commands per batch
> than 1 or 2. I have enabled the tsql batch start and end to trace the
> batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables with
> large rows I am thinking that the 10,000 commands per batch is too much
> now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
|||Thanks for the response.
I was changing the bcpbatchsize because of the problem I am having were
the replication just stopped. I let it sit for over 24 hrs and saw no
activity, no error message, it just said it "waiting for response from
the backend co". It never timed out. I was using the default profile
at that time. The destination server was not showing much activity and
the trace showed no command flowing between the servers.
The other problem I then saw using the trace was that even when I set
the bcpbatchsize very small and I was able to keep replication running
(after doing complete resetup) I would see more commands sent per batch
than I had specified. I set it to two and during the trace saw more
than two commands between eatch batch start and end in the trace. My
understanding of the bcpbatchsize is as you defined it. I just did not
see it actually doing that in the trace. That is why I am wondering
just what it actually does?
Even with the bcpbatchsize at 2 the replication eventually froze again
on the same message, "waiting for a response from the backend co". The
problem was that it was still sending the big batches. I wanted to make
them smaller as a test and was having no luck.
We don't use the snapshot because it takes way too long for our 70 gig
databases. We can backup and then restore the database in about an
hour.
Some of the tables are quite large (lots of columns and some the columns
get pretty big) and some of the column names get a little wordy.
We have gone back to removing the idents (which takes about an hour
right now it took about 1/2 hr 6 months ago on slower hardware) and are
no longer setting the "use column names" in the replication and it has
been running fine for over a week. I don't want to keep removing the
idents because the time it takes is getting longer quickly and the
window to setup replication is shrinking. I can get about 6 hours now
before users start to complain. Our next upgrade is coming up soon and
I will be redoing the replication setup again because several tables
will have structure changes.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:#1pAG5k$EHA.3424@.TK2MSFTNGP11.phx.gbl:
> fiddling with the bcpbatchsize is not always a good idea. What this
> does is determines how many rows are processed before they are
> committed. Setting this to a high value means your tlog on your
> subscriber will grow and the bcp process (performed by odbcbcp) will
> chug along longer before sending messages back to the SQL Server
> agent. This will cause the "waiting for response from the backend".
> using a small value for bcpbatchsize will take longer, but is more
> chatty and you won't get this message.
> Think of it like the way your girlfriend blows your check. Give her a
> large amount of money and you won't hear from her for weeks. On the
> other hand give her a small amount of money, and she will be back in a
> few hours. Give her to little money and she will never come back. So
> you have to balance the amount of money you give her with how
> frequently you want to see her. Same with bcpbatchsize - but I
> digress.
> If your subscriber is a multiproc machine think about using
> UseInprocLoader switch.
> But, I'm a little confused - are you regenerating your snapshot? Are
> the tables whose column names you have changed large?
>
|||There is a condition where the buffers are exhausted/depleted this might
account for what you are seeing. Normally you get this when the agent
starts.
There is another condition, which again you might be running into. When you
push a tonne of data over the wire it takes some time for the data to be
committed. This time is a function of how much data and bcpbatchsize and
your log size. I have on occasion waited overnight for a snapshot to be
applied.
I would advise you to look into another strategy for deploying these large
snapshots. For instance you might want to break your publication into
pieces related to DRI. Put the large tables into separate publications, i.e.
one publication for each large table and tables related to it by DRI.
Then do a nosync subscription with these tables and use another method for
deploying the snapshot. Like bcp, or copying the snapshot to a cd,
tapedrive, network share and then applying it to the subscriber from this
location.
I prefer to use bcp as I have a window into what is being applied and I can
control it in a highly granular fashion. It will require more work, but the
problem is with large snapshot you have to start off at square one with each
hiccup.
SQL 2005 will restart the snapshot application where it fails so it should
fix this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ron" <rgellenbecker@.wonderboxtech.com> wrote in message
news:Xns95E37DDC6CCD5WBT39312565@.207.46.248.16...
> Thanks for the response.
> I was changing the bcpbatchsize because of the problem I am having were
> the replication just stopped. I let it sit for over 24 hrs and saw no
> activity, no error message, it just said it "waiting for response from
> the backend co". It never timed out. I was using the default profile
> at that time. The destination server was not showing much activity and
> the trace showed no command flowing between the servers.
> The other problem I then saw using the trace was that even when I set
> the bcpbatchsize very small and I was able to keep replication running
> (after doing complete resetup) I would see more commands sent per batch
> than I had specified. I set it to two and during the trace saw more
> than two commands between eatch batch start and end in the trace. My
> understanding of the bcpbatchsize is as you defined it. I just did not
> see it actually doing that in the trace. That is why I am wondering
> just what it actually does?
> Even with the bcpbatchsize at 2 the replication eventually froze again
> on the same message, "waiting for a response from the backend co". The
> problem was that it was still sending the big batches. I wanted to make
> them smaller as a test and was having no luck.
> We don't use the snapshot because it takes way too long for our 70 gig
> databases. We can backup and then restore the database in about an
> hour.
> Some of the tables are quite large (lots of columns and some the columns
> get pretty big) and some of the column names get a little wordy.
> We have gone back to removing the idents (which takes about an hour
> right now it took about 1/2 hr 6 months ago on slower hardware) and are
> no longer setting the "use column names" in the replication and it has
> been running fine for over a week. I don't want to keep removing the
> idents because the time it takes is getting longer quickly and the
> window to setup replication is shrinking. I can get about 6 hours now
> before users start to complain. Our next upgrade is coming up soon and
> I will be redoing the replication setup again because several tables
> will have structure changes.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
> news:#1pAG5k$EHA.3424@.TK2MSFTNGP11.phx.gbl:
>
|||Thanks for the info.
My problem is not with deploying the snapshots, we have figured out how
to do the replication setup without using snapshots.
The problem is that we want to start using column names in the
replication.
I have done more testing and tracing and have narrowed down my problem.
We are using 1 way transactional replication. When the system updates
records, replication is issuing deletes and then inserts. The deletes
are the part that are taking a long time (over an hour). The inserts
takes seconds. It is the same number of rows deleted and inserted.
If I delete the rows outside of the update statement on the publisher
and then just insert the data on the publisher the process takes seconds
for the deletes and inserts to replicate. It is when I do update
statments to the publisher that the distribution takes a very long time.
The log reader gets the data in seconds in either case. The odd part is
the during the trace I can see that the distribution is just doing
deletes and then inserts. I have even taken and recreated the deletes
that replication appears to be using and they run very quickly against
the publisher and through the distribution process.
The table has the same structure on both the publisher and subscriber.
There are no idents in this table and all foreign keys have been removed
on the subscriber. The delete statement is using the clustered primary
to select the records and the deletes are being done one row at a time.
I have done the deletes one row at time outside of the update statement
and it took about 2 min to execute and about 2 min to replicate.
Why do the delete statments created by replication for the updates at
the publisher take so much longer to execute when using column names in
replication? Updating the same records without column names takes
seconds.
Thanks,
Ron
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:usZrKu#$EHA.1564@.TK2MSFTNGP09.phx.gbl:
> There is a condition where the buffers are exhausted/depleted this
> might account for what you are seeing. Normally you get this when the
> agent starts.
> There is another condition, which again you might be running into.
> When you push a tonne of data over the wire it takes some time for the
> data to be committed. This time is a function of how much data and
> bcpbatchsize and your log size. I have on occasion waited overnight
> for a snapshot to be applied.
> I would advise you to look into another strategy for deploying these
> large snapshots. For instance you might want to break your
> publication into pieces related to DRI. Put the large tables into
> separate publications, i.e. one publication for each large table and
> tables related to it by DRI.
> Then do a nosync subscription with these tables and use another method
> for deploying the snapshot. Like bcp, or copying the snapshot to a cd,
> tapedrive, network share and then applying it to the subscriber from
> this location.
> I prefer to use bcp as I have a window into what is being applied and
> I can control it in a highly granular fashion. It will require more
> work, but the problem is with large snapshot you have to start off at
> square one with each hiccup.
> SQL 2005 will restart the snapshot application where it fails so it
> should fix this.
>
sql
2012年2月12日星期日
Batch database backup and restore
file?
TonyTony (tonyng2@.spacecommand.net) writes:
> Does anyone know how I could setup a backup or restore executed from a
> .bat file?
Put the command you want to run in a file called BACKUP.SQL, and then
say:
osql -E -n -i BACKUP.LOG
for more details, please look up OSQL in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
2012年2月11日星期六
basics with stored procedures.....please
I've setup my stored procedure, but now what?
I want to display the information (only one record it will return) on the page / NOT using a datagrid or list, just simple....I guess using an asp:label???
Could someone tell me what I need to do!!!
Also, are there any proper resources out there that just show you right out of the box howto insert / update / delete with sp's?, show / view, just proper simple source code?
THANKS!
my code:
// Creating the database connection
SqlConnection objConn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_STRING_conn_main"]);
// Creating the stored procedure 4 the webpage
// Instantiate a Sql Command
SqlCommand webpage_view = new SqlCommand("stpr_webpage_view", objConn);
// Declare that the T-SQL statement that will be executed is a stored procedure
// Use a CommandType.Text to execute a T-SQL Query
webpage_view.CommandType = CommandType.StoredProcedure;
SqlParameter webpage_id_parm = webpage_view.Parameters.Add("@.webpage_id", SqlDbType.Int);
webpage_id_parm.Value = "1";
// Create a SqlDataAdapter to manage the connection and retrieve the data
SqlDataAdapter da_1 = new SqlDataAdapter();
// Instantiate a Dataset that will hold the data retrieved from the database using the select command
DataSet ds_webpage = new DataSet();
da_1.SelectCommand = webpage_view;
// Retrieve the data into the dataset using the SqlDataAdapter.Fill command
da_1.Fill( ds_webpage, "webpage_title" );
DataView dv2 = ds_webpage.Tables[0].DefaultView;
if ( !this.IsPostBack ) {
this.DataBind ( );
}
// C#
// To place a single value from a recordset into a label
// drag an ASP:Label onto the Web form and give it an ID of myLabel
myLabel.Text = "No data";
// Be compulsive and make sure there is a datatable in the dataset and that it has at least one row
if( ds_webpage.Tables.Count > 0 && ds_webpage.Tables[0].Rows.Count > ) ){
myLabel.Text = ds_webpage.Tables[0].Rows[0].ToString();
}
The simplest way to see how stored procedures can be used to select and update data is to let VS.Net create it for you.
1. Go to Design View for a web form.
2. Drag an SqlDataAdapter onto the form. A DataAdapter Configuration Wizard will open
3. Select your database connection and click Next.
4. On the Choose a Query Type screen select the second option "Create a new Stored Procedure". Click Next
5. On the Generate a Stored Procedure screen enter a select statement for the fields you want to use. For example, just something like Select * from SomeTable. Click Next.
6. On the Create the Stored Procedures screen enter appropriate stored procedure names. Be sure to click the "Yes, create them in the database for me" radio button.
Look at the code behind generated for this data adapter to see how it all works. Use it like the one you created above for the select. For the update, after you changed data in the data set just call:
da_1.Update( ds_webpage, "webpage_title" );
Basic Upgrade questions
I need a few no nonsense answers to help me with my current SQL setup.
I currently have SQL7.0 Server running on NT4.0 SP6 box and also a SQL7.0
server running on a Windows 2000 SP4 box.
They are not clustered but I want to upgrade the SQL to SQL 2000 on both
boxes and I must also upgrade the NT4 to Win 2000.
What I want to know is, what is the best way to do this so that I can avail
of SQL clustering.
Both SQL instances should be equal so that either server can take requests
automatically if the other fails.
All help would be greatly appreciated.
Some suggestions:
1) Upgrade to Win2K BEFORE doing the upgrade to SQL2K
2) Ensure that you will be running Win2K Advanced Server or Datacenter
server
3) Ensure that you will be running SQL2K Enterprise Edition
4) It sounds like you want a single-instance SQL cluster, formerly known
as Active/Passive
6) Make sure to update statistics WITH FULLSCAN on all of your tables
7) If you have the bucks, go with new hardware and just migrate the
databases to the new cluster
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Padraig" <Padraig@.discussions.microsoft.com> wrote in message
news:C17ADBE9-CC8F-4128-9F20-582DA05C073F@.microsoft.com...
Hi,
I need a few no nonsense answers to help me with my current SQL setup.
I currently have SQL7.0 Server running on NT4.0 SP6 box and also a SQL7.0
server running on a Windows 2000 SP4 box.
They are not clustered but I want to upgrade the SQL to SQL 2000 on both
boxes and I must also upgrade the NT4 to Win 2000.
What I want to know is, what is the best way to do this so that I can avail
of SQL clustering.
Both SQL instances should be equal so that either server can take requests
automatically if the other fails.
All help would be greatly appreciated.
|||One more recommendation. Once you are up to where you want to be in
software versions, do a rolling clean install for each node. Wipe each node
and reinstall the OS from scratch. The instructions in BOL for Maintaining
a Failover Cluster list what to if a node completely fails. Just do this
once for each node reusing the 'failed' hardware after a clean install.
This way you guarantee there are no issues lef over from an upgrade, but you
still get the small step benefits of upgrading in place. It is definitely
more work, but I think it leaves a cleaner and more stable system.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ONTo1vJrEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Some suggestions:
> 1) Upgrade to Win2K BEFORE doing the upgrade to SQL2K
> 2) Ensure that you will be running Win2K Advanced Server or Datacenter
> server
> 3) Ensure that you will be running SQL2K Enterprise Edition
> 4) It sounds like you want a single-instance SQL cluster, formerly
known
> as Active/Passive
> 6) Make sure to update statistics WITH FULLSCAN on all of your tables
> 7) If you have the bucks, go with new hardware and just migrate the
> databases to the new cluster
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Padraig" <Padraig@.discussions.microsoft.com> wrote in message
> news:C17ADBE9-CC8F-4128-9F20-582DA05C073F@.microsoft.com...
> Hi,
> I need a few no nonsense answers to help me with my current SQL setup.
> I currently have SQL7.0 Server running on NT4.0 SP6 box and also a SQL7.0
> server running on a Windows 2000 SP4 box.
> They are not clustered but I want to upgrade the SQL to SQL 2000 on both
> boxes and I must also upgrade the NT4 to Win 2000.
> What I want to know is, what is the best way to do this so that I can
avail
> of SQL clustering.
> Both SQL instances should be equal so that either server can take requests
> automatically if the other fails.
> All help would be greatly appreciated.
>
Basic Upgrade questions
I need a few no nonsense answers to help me with my current SQL setup.
I currently have SQL7.0 Server running on NT4.0 SP6 box and also a SQL7.0
server running on a Windows 2000 SP4 box.
They are not clustered but I want to upgrade the SQL to SQL 2000 on both
boxes and I must also upgrade the NT4 to Win 2000.
What I want to know is, what is the best way to do this so that I can avail
of SQL clustering.
Both SQL instances should be equal so that either server can take requests
automatically if the other fails.
All help would be greatly appreciated.Some suggestions:
1) Upgrade to Win2K BEFORE doing the upgrade to SQL2K
2) Ensure that you will be running Win2K Advanced Server or Datacenter
server
3) Ensure that you will be running SQL2K Enterprise Edition
4) It sounds like you want a single-instance SQL cluster, formerly known
as Active/Passive
6) Make sure to update statistics WITH FULLSCAN on all of your tables
7) If you have the bucks, go with new hardware and just migrate the
databases to the new cluster
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Padraig" <Padraig@.discussions.microsoft.com> wrote in message
news:C17ADBE9-CC8F-4128-9F20-582DA05C073F@.microsoft.com...
Hi,
I need a few no nonsense answers to help me with my current SQL setup.
I currently have SQL7.0 Server running on NT4.0 SP6 box and also a SQL7.0
server running on a Windows 2000 SP4 box.
They are not clustered but I want to upgrade the SQL to SQL 2000 on both
boxes and I must also upgrade the NT4 to Win 2000.
What I want to know is, what is the best way to do this so that I can avail
of SQL clustering.
Both SQL instances should be equal so that either server can take requests
automatically if the other fails.
All help would be greatly appreciated.|||One more recommendation. Once you are up to where you want to be in
software versions, do a rolling clean install for each node. Wipe each node
and reinstall the OS from scratch. The instructions in BOL for Maintaining
a Failover Cluster list what to if a node completely fails. Just do this
once for each node reusing the 'failed' hardware after a clean install.
This way you guarantee there are no issues lef over from an upgrade, but you
still get the small step benefits of upgrading in place. It is definitely
more work, but I think it leaves a cleaner and more stable system.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ONTo1vJrEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Some suggestions:
> 1) Upgrade to Win2K BEFORE doing the upgrade to SQL2K
> 2) Ensure that you will be running Win2K Advanced Server or Datacenter
> server
> 3) Ensure that you will be running SQL2K Enterprise Edition
> 4) It sounds like you want a single-instance SQL cluster, formerly
known
> as Active/Passive
> 6) Make sure to update statistics WITH FULLSCAN on all of your tables
> 7) If you have the bucks, go with new hardware and just migrate the
> databases to the new cluster
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Padraig" <Padraig@.discussions.microsoft.com> wrote in message
> news:C17ADBE9-CC8F-4128-9F20-582DA05C073F@.microsoft.com...
> Hi,
> I need a few no nonsense answers to help me with my current SQL setup.
> I currently have SQL7.0 Server running on NT4.0 SP6 box and also a SQL7.0
> server running on a Windows 2000 SP4 box.
> They are not clustered but I want to upgrade the SQL to SQL 2000 on both
> boxes and I must also upgrade the NT4 to Win 2000.
> What I want to know is, what is the best way to do this so that I can
avail
> of SQL clustering.
> Both SQL instances should be equal so that either server can take requests
> automatically if the other fails.
> All help would be greatly appreciated.
>
Basic SQL server setup questions...
I'm setting up a database for the company I work for. Should I setup a different database for each division in our company (e.g. accounting, project management, estimating, etc.), or should I use just one database? It seems like it would be easier to use one database so I only have to use one connection, but is this ideal? I want all the info from the different divisions to be used together, but at this point I'm confused as to how to connect different databases together, if I were to go this way.
Thanks for any input or ideas!I create seperate databases based on the app function - not the company division.
many times you'll get people from different functional areas all using the same apps...
anyways, you can get to all the data in a single db with a single connection if its all on the same server. you just need to qualify the table names in your queries with a prefix of the database name.
i.e.
AccountingDB.dbo.table.column
since this is a pain to type, in one of our dbs that shared a user table with an existing db, we created a view that pulled all users from the other db. now our new database has a local view that we can treat like a user table...one connection can pull data from 2 (or more) databases :-)
2012年2月9日星期四
basic Server setup and user accounts
basic server and user accounts in order to then install Project Server?
Hi
Project Server is not yet officially certified to run with SQL 2005.
Postings in the project server news groups discuss how you can work around
this to get it working, but you should not use this on a production system
http://www.microsoft.com/office/comm...=&sloc=en -us
http://blogs.lv0.net/mpatest/archive...11/18/182.aspx
John
"Steve Scott" wrote:
> I have just installed SQL server 2005 standard edition - how do I create a
> basic server and user accounts in order to then install Project Server?
basic Server setup and user accounts
basic server and user accounts in order to then install Project Server?Hi
Project Server is not yet officially certified to run with SQL 2005.
Postings in the project server news groups discuss how you can work around
this to get it working, but you should not use this on a production system
http://www.microsoft.com/office/com...exp=&sloc=en-us
http://blogs.lv0.net/mpatest/archiv.../11/18/182.aspx
John
"Steve Scott" wrote:
> I have just installed SQL server 2005 standard edition - how do I create a
> basic server and user accounts in order to then install Project Server?
basic Server setup and user accounts
basic server and user accounts in order to then install Project Server?Hi
Project Server is not yet officially certified to run with SQL 2005.
Postings in the project server news groups discuss how you can work around
this to get it working, but you should not use this on a production system
http://www.microsoft.com/office/community/en-us/default.mspx?query=SQL+2005&dg=microsoft.public.project.server&cat=en-us-microsoftproject&lang=en&cr=US&pt=a1d023a3-f612-4da2-acb8-fda8f850d645&catlist=&dglist=&ptlist=&exp=&sloc=en-us
http://blogs.lv0.net/mpatest/archive/2005/11/18/182.aspx
John
"Steve Scott" wrote:
> I have just installed SQL server 2005 standard edition - how do I create a
> basic server and user accounts in order to then install Project Server?
Basic security setup
Our company has just started using RS and I would like to
know how to set up basic security.
Our setup:
We have RS installed on one of our SQL servers. Our
Analysis department creates reports on their PC's (with
Reporing Services & Visual Studio.net installed on their
local PC). They will then deploy the reports to our RS
server and the Marketing department will access reports
through the RS web interface.
What I am struggling with:
I cannot figure out how to let users see reports without
giving them adminitrator rights on the RS server. I have
tried giving them access to the reportserver database -
this gives them access to the reportserver webpage, but
they cannot see any reports (I gave them access to
specific reports in RS manager).
I'm sure it's something stupid that I'm not doing, but
cannot figure out what it is. Could someone please help
me.
Thanks,
TheresaIf you are a 'Content Manager' on the report server (by default all local
admins are content managers) you can set permission on RS objects. Go to
the object in question, on the property tab there is a security sub tab
which will allow you to give users access rights. You most likely want to
use the Browser role but you will have to investigate to see what fits your
needs.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Theresa dP" <theresa@.discussions.microsoft.com> wrote in message
news:43b401c49035$39843a90$a301280a@.phx.gbl...
> Hi there
> Our company has just started using RS and I would like to
> know how to set up basic security.
> Our setup:
> We have RS installed on one of our SQL servers. Our
> Analysis department creates reports on their PC's (with
> Reporing Services & Visual Studio.net installed on their
> local PC). They will then deploy the reports to our RS
> server and the Marketing department will access reports
> through the RS web interface.
> What I am struggling with:
> I cannot figure out how to let users see reports without
> giving them adminitrator rights on the RS server. I have
> tried giving them access to the reportserver database -
> this gives them access to the reportserver webpage, but
> they cannot see any reports (I gave them access to
> specific reports in RS manager).
> I'm sure it's something stupid that I'm not doing, but
> cannot figure out what it is. Could someone please help
> me.
> Thanks,
> Theresa