First time poster...
Alright this is what I got...I am running MSSQL server 2000.
I have already restored a copy of the production db to our test sql server.
What I need to do is run some update scripts (from 3rd party vendor, going to newer version of their product). The database is about 60Gb and I filled up the transaction log (causing later updates to fail) while running one of their scripts that executed 194 routines of:
alter table Table_Name add New_Column int
GO
EXEC sp_bindefault mg_zero_dflt, [Table_Name.New_Column]
GO
update Table_Name set New_Column=1
GO
Will "sp_dboption 'dbname' 'trunc. log on chkpt' 'TRUE' " automatically truncate the transaction log when approaching the transaction log file limit while running these update scripts. Then when the updates are complete go ahead and "sp_dboption 'dbname' 'trunc. log on chkpt' 'FALSE' "
I'm not worried about logging the transactions during this process, I just don't want to have the transaction log fill up while running these scripts causing it to error out.
Or is there a way to make those changes using bcp?In the database properties window, go to the options tab and set your database recovery model to "simple". This should keep the log from filling up.
When you are finished, set the recovery model back to "full" and run an immediate backup.|||Thanks for the help. I remember seeing something about that and got sidetracked with the whole 'trunc. log on chkpt' idea. I'm in the process of finishing the scripts on the test server and I'll update with results. Thanks again!|||I finished running the scripts late yesterday without any hitches. That was exactly what I was looking for!
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Friday, March 16, 2012
"This Machine Only" and "Stop Processing More Rules" Questions
I'm running Outlook 2003 on a Win 2kServer machine.
When setting up a rule, what is the purpose of the "this machine only"
option? I did not notice this option in previous versions of Outlook. Why
would someone setup a rule without this option? What is the effect if this
option is not selected? Is it possible to share rules between different
machines?
For the "Stop processing more rules" - I'm assuming that this option is only
executed when the rule matches. For example, if rule #1 says "if sender is
abc@.xyz.com, move this message to xyz folder, and stop processing more
rules", and the message is not from that sender, shouldn't Outlook then move
on to rule #2? Also, if you get a hit on a rule with incoming message
number 1, shouldn't the rules start again from the top for message #2, or
does the "stop processing more rules" keep this from happening? I'm seeing
a situation where, when I get some new messages, it appears that the rules
don't immediately run. I can't tell if one of them is firing then the rules
stop processing the rest of the messages. If I go and click the
Send/Receive button, the rules will all run.
Thanks,
John Schneider
Sorry! I inadvertently posted this to the wrong newsgroup.
"John Schneider" <JohnDONTSPAMME.Schneider@.eWebITNOSPAM.Kom> wrote in
message news:eTTXupW0EHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm running Outlook 2003 on a Win 2kServer machine.
> When setting up a rule, what is the purpose of the "this machine only"
> option? I did not notice this option in previous versions of Outlook.
Why
> would someone setup a rule without this option? What is the effect if
this
> option is not selected? Is it possible to share rules between different
> machines?
> For the "Stop processing more rules" - I'm assuming that this option is
only
> executed when the rule matches. For example, if rule #1 says "if sender
is
> abc@.xyz.com, move this message to xyz folder, and stop processing more
> rules", and the message is not from that sender, shouldn't Outlook then
move
> on to rule #2? Also, if you get a hit on a rule with incoming message
> number 1, shouldn't the rules start again from the top for message #2, or
> does the "stop processing more rules" keep this from happening? I'm
seeing
> a situation where, when I get some new messages, it appears that the rules
> don't immediately run. I can't tell if one of them is firing then the
rules
> stop processing the rest of the messages. If I go and click the
> Send/Receive button, the rules will all run.
> Thanks,
> John Schneider
>
When setting up a rule, what is the purpose of the "this machine only"
option? I did not notice this option in previous versions of Outlook. Why
would someone setup a rule without this option? What is the effect if this
option is not selected? Is it possible to share rules between different
machines?
For the "Stop processing more rules" - I'm assuming that this option is only
executed when the rule matches. For example, if rule #1 says "if sender is
abc@.xyz.com, move this message to xyz folder, and stop processing more
rules", and the message is not from that sender, shouldn't Outlook then move
on to rule #2? Also, if you get a hit on a rule with incoming message
number 1, shouldn't the rules start again from the top for message #2, or
does the "stop processing more rules" keep this from happening? I'm seeing
a situation where, when I get some new messages, it appears that the rules
don't immediately run. I can't tell if one of them is firing then the rules
stop processing the rest of the messages. If I go and click the
Send/Receive button, the rules will all run.
Thanks,
John Schneider
Sorry! I inadvertently posted this to the wrong newsgroup.
"John Schneider" <JohnDONTSPAMME.Schneider@.eWebITNOSPAM.Kom> wrote in
message news:eTTXupW0EHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm running Outlook 2003 on a Win 2kServer machine.
> When setting up a rule, what is the purpose of the "this machine only"
> option? I did not notice this option in previous versions of Outlook.
Why
> would someone setup a rule without this option? What is the effect if
this
> option is not selected? Is it possible to share rules between different
> machines?
> For the "Stop processing more rules" - I'm assuming that this option is
only
> executed when the rule matches. For example, if rule #1 says "if sender
is
> abc@.xyz.com, move this message to xyz folder, and stop processing more
> rules", and the message is not from that sender, shouldn't Outlook then
move
> on to rule #2? Also, if you get a hit on a rule with incoming message
> number 1, shouldn't the rules start again from the top for message #2, or
> does the "stop processing more rules" keep this from happening? I'm
seeing
> a situation where, when I get some new messages, it appears that the rules
> don't immediately run. I can't tell if one of them is firing then the
rules
> stop processing the rest of the messages. If I go and click the
> Send/Receive button, the rules will all run.
> Thanks,
> John Schneider
>
"The SQL Server cannot obtain a LOCK resource at this time" error
Hi there,
I am running on my machine the following SQL statements on my (local) SQL
Server:
SET NOCOUNT ON
SET ROWCOUNT 1500
delete_more:
delete table1 where field1 = '1' and field2 = '2'
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
The table has arround 5 million records and the DELETE statement would
delete arround 100000 records. I can see by running sp_lock that SQL is
escalating the locks to a table lock.
I am running in paralel the following:
SET ROWCOUNT 100
delete table1 where field1 = '3' and field2 = '2'
SET ROWCOUNT 0
This query is running fine, however the DELETE statement fails with the
error 1204:
"The SQL Server cannot obtain a LOCK resource at this time". Can somebody
shed some light for me here? Am I not able to run two DELETE statements is
paralel or am I doing something wrong?
I am running with the run_value for locks set to 0.
Thank you in advance.You mentioned there is already a table lock on that particular table, as it
is deleting millions of records. You probably need to wait for this
transaction to complete before you execute another delete statement on the
same table.
I suspect changing isolation level would solve your purpose.
"Andrei" wrote:
> Hi there,
> I am running on my machine the following SQL statements on my (local) SQL
> Server:
> SET NOCOUNT ON
> SET ROWCOUNT 1500
> delete_more:
> delete table1 where field1 = '1' and field2 = '2'
> IF @.@.ROWCOUNT > 0 GOTO delete_more
> SET ROWCOUNT 0
> The table has arround 5 million records and the DELETE statement would
> delete arround 100000 records. I can see by running sp_lock that SQL is
> escalating the locks to a table lock.
> I am running in paralel the following:
> SET ROWCOUNT 100
> delete table1 where field1 = '3' and field2 = '2'
> SET ROWCOUNT 0
> This query is running fine, however the DELETE statement fails with the
> error 1204:
> "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> shed some light for me here? Am I not able to run two DELETE statements is
> paralel or am I doing something wrong?
> I am running with the run_value for locks set to 0.
> Thank you in advance.|||What I want is to run several jobs concurrently, I do not want to wait for
one to finish in order to start the second one.
However I do not understand why I get the error, since the first statement
is suppose to delete 1500 records (so maximum 1500 key locks applied, if
there is no lock escalation) and the second statement is deleting 100
records. Am I missing something here?
"GYK" wrote:
> You mentioned there is already a table lock on that particular table, as it
> is deleting millions of records. You probably need to wait for this
> transaction to complete before you execute another delete statement on the
> same table.
> I suspect changing isolation level would solve your purpose.
> "Andrei" wrote:
> > Hi there,
> >
> > I am running on my machine the following SQL statements on my (local) SQL
> > Server:
> >
> > SET NOCOUNT ON
> >
> > SET ROWCOUNT 1500
> > delete_more:
> > delete table1 where field1 = '1' and field2 = '2'
> > IF @.@.ROWCOUNT > 0 GOTO delete_more
> > SET ROWCOUNT 0
> >
> > The table has arround 5 million records and the DELETE statement would
> > delete arround 100000 records. I can see by running sp_lock that SQL is
> > escalating the locks to a table lock.
> >
> > I am running in paralel the following:
> > SET ROWCOUNT 100
> > delete table1 where field1 = '3' and field2 = '2'
> > SET ROWCOUNT 0
> >
> > This query is running fine, however the DELETE statement fails with the
> > error 1204:
> > "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> > shed some light for me here? Am I not able to run two DELETE statements is
> > paralel or am I doing something wrong?
> >
> > I am running with the run_value for locks set to 0.
> >
> > Thank you in advance.
I am running on my machine the following SQL statements on my (local) SQL
Server:
SET NOCOUNT ON
SET ROWCOUNT 1500
delete_more:
delete table1 where field1 = '1' and field2 = '2'
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
The table has arround 5 million records and the DELETE statement would
delete arround 100000 records. I can see by running sp_lock that SQL is
escalating the locks to a table lock.
I am running in paralel the following:
SET ROWCOUNT 100
delete table1 where field1 = '3' and field2 = '2'
SET ROWCOUNT 0
This query is running fine, however the DELETE statement fails with the
error 1204:
"The SQL Server cannot obtain a LOCK resource at this time". Can somebody
shed some light for me here? Am I not able to run two DELETE statements is
paralel or am I doing something wrong?
I am running with the run_value for locks set to 0.
Thank you in advance.You mentioned there is already a table lock on that particular table, as it
is deleting millions of records. You probably need to wait for this
transaction to complete before you execute another delete statement on the
same table.
I suspect changing isolation level would solve your purpose.
"Andrei" wrote:
> Hi there,
> I am running on my machine the following SQL statements on my (local) SQL
> Server:
> SET NOCOUNT ON
> SET ROWCOUNT 1500
> delete_more:
> delete table1 where field1 = '1' and field2 = '2'
> IF @.@.ROWCOUNT > 0 GOTO delete_more
> SET ROWCOUNT 0
> The table has arround 5 million records and the DELETE statement would
> delete arround 100000 records. I can see by running sp_lock that SQL is
> escalating the locks to a table lock.
> I am running in paralel the following:
> SET ROWCOUNT 100
> delete table1 where field1 = '3' and field2 = '2'
> SET ROWCOUNT 0
> This query is running fine, however the DELETE statement fails with the
> error 1204:
> "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> shed some light for me here? Am I not able to run two DELETE statements is
> paralel or am I doing something wrong?
> I am running with the run_value for locks set to 0.
> Thank you in advance.|||What I want is to run several jobs concurrently, I do not want to wait for
one to finish in order to start the second one.
However I do not understand why I get the error, since the first statement
is suppose to delete 1500 records (so maximum 1500 key locks applied, if
there is no lock escalation) and the second statement is deleting 100
records. Am I missing something here?
"GYK" wrote:
> You mentioned there is already a table lock on that particular table, as it
> is deleting millions of records. You probably need to wait for this
> transaction to complete before you execute another delete statement on the
> same table.
> I suspect changing isolation level would solve your purpose.
> "Andrei" wrote:
> > Hi there,
> >
> > I am running on my machine the following SQL statements on my (local) SQL
> > Server:
> >
> > SET NOCOUNT ON
> >
> > SET ROWCOUNT 1500
> > delete_more:
> > delete table1 where field1 = '1' and field2 = '2'
> > IF @.@.ROWCOUNT > 0 GOTO delete_more
> > SET ROWCOUNT 0
> >
> > The table has arround 5 million records and the DELETE statement would
> > delete arround 100000 records. I can see by running sp_lock that SQL is
> > escalating the locks to a table lock.
> >
> > I am running in paralel the following:
> > SET ROWCOUNT 100
> > delete table1 where field1 = '3' and field2 = '2'
> > SET ROWCOUNT 0
> >
> > This query is running fine, however the DELETE statement fails with the
> > error 1204:
> > "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> > shed some light for me here? Am I not able to run two DELETE statements is
> > paralel or am I doing something wrong?
> >
> > I am running with the run_value for locks set to 0.
> >
> > Thank you in advance.
"The request failed with HTTP status 401: Unauthorized."
Hi,
I'm having a few problems with getting SQL RS running and wondered
whether anyone could give me some help.
I have just installed RS and have never got into the Report Manager
yet. I think it is basically an IIS permissions problem as I am
getting a page that says "The request failed with HTTP status 401:
Unauthorized." whenever I try to get to
http://reporting.[domain].com/Reports, even if I add myself to the
domain Administrators group. I have all of the IIS settings as they
are specified in documentation.
My setup: Reporting Services SP2, IIS6, Windows 2003 Server, SQL Server
2000, installed on a non-default website following instructions on
SQLJunkies.com by Trent Miesner(although I'm pretty sure this isn't the
problem).
Thanks in advance,
SamJust check if this helps
http://support.microsoft.com/kb/896861/en-us
Sam wrote:
>Hi,
>I'm having a few problems with getting SQL RS running and wondered
>whether anyone could give me some help.
>I have just installed RS and have never got into the Report Manager
>yet. I think it is basically an IIS permissions problem as I am
>getting a page that says "The request failed with HTTP status 401:
>Unauthorized." whenever I try to get to
>http://reporting.[domain].com/Reports, even if I add myself to the
>domain Administrators group. I have all of the IIS settings as they
>are specified in documentation.
>My setup: Reporting Services SP2, IIS6, Windows 2003 Server, SQL Server
>2000, installed on a non-default website following instructions on
>SQLJunkies.com by Trent Miesner(although I'm pretty sure this isn't the
>problem).
>Thanks in advance,
>Sam
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1|||As a few others have mentioned, the article(method 1) did fix my problem. I
had everything working but started getting this error when I required SSL in
the RS configuration/report server virtual directory configuration.
http://support.microsoft.com/kb/896861 I rebooted after doing it.
I'm using SQL2005 SP1 on Win2k3 SP1.
--Forrest
"BALAJI K via SQLMonster.com" wrote:
> Just check if this helps
> http://support.microsoft.com/kb/896861/en-us
>
>
> Sam wrote:
> >Hi,
> >
> >I'm having a few problems with getting SQL RS running and wondered
> >whether anyone could give me some help.
> >
> >I have just installed RS and have never got into the Report Manager
> >yet. I think it is basically an IIS permissions problem as I am
> >getting a page that says "The request failed with HTTP status 401:
> >Unauthorized." whenever I try to get to
> >http://reporting.[domain].com/Reports, even if I add myself to the
> >domain Administrators group. I have all of the IIS settings as they
> >are specified in documentation.
> >
> >My setup: Reporting Services SP2, IIS6, Windows 2003 Server, SQL Server
> >2000, installed on a non-default website following instructions on
> >SQLJunkies.com by Trent Miesner(although I'm pretty sure this isn't the
> >problem).
> >
> >Thanks in advance,
> >
> >Sam
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1
>
I'm having a few problems with getting SQL RS running and wondered
whether anyone could give me some help.
I have just installed RS and have never got into the Report Manager
yet. I think it is basically an IIS permissions problem as I am
getting a page that says "The request failed with HTTP status 401:
Unauthorized." whenever I try to get to
http://reporting.[domain].com/Reports, even if I add myself to the
domain Administrators group. I have all of the IIS settings as they
are specified in documentation.
My setup: Reporting Services SP2, IIS6, Windows 2003 Server, SQL Server
2000, installed on a non-default website following instructions on
SQLJunkies.com by Trent Miesner(although I'm pretty sure this isn't the
problem).
Thanks in advance,
SamJust check if this helps
http://support.microsoft.com/kb/896861/en-us
Sam wrote:
>Hi,
>I'm having a few problems with getting SQL RS running and wondered
>whether anyone could give me some help.
>I have just installed RS and have never got into the Report Manager
>yet. I think it is basically an IIS permissions problem as I am
>getting a page that says "The request failed with HTTP status 401:
>Unauthorized." whenever I try to get to
>http://reporting.[domain].com/Reports, even if I add myself to the
>domain Administrators group. I have all of the IIS settings as they
>are specified in documentation.
>My setup: Reporting Services SP2, IIS6, Windows 2003 Server, SQL Server
>2000, installed on a non-default website following instructions on
>SQLJunkies.com by Trent Miesner(although I'm pretty sure this isn't the
>problem).
>Thanks in advance,
>Sam
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1|||As a few others have mentioned, the article(method 1) did fix my problem. I
had everything working but started getting this error when I required SSL in
the RS configuration/report server virtual directory configuration.
http://support.microsoft.com/kb/896861 I rebooted after doing it.
I'm using SQL2005 SP1 on Win2k3 SP1.
--Forrest
"BALAJI K via SQLMonster.com" wrote:
> Just check if this helps
> http://support.microsoft.com/kb/896861/en-us
>
>
> Sam wrote:
> >Hi,
> >
> >I'm having a few problems with getting SQL RS running and wondered
> >whether anyone could give me some help.
> >
> >I have just installed RS and have never got into the Report Manager
> >yet. I think it is basically an IIS permissions problem as I am
> >getting a page that says "The request failed with HTTP status 401:
> >Unauthorized." whenever I try to get to
> >http://reporting.[domain].com/Reports, even if I add myself to the
> >domain Administrators group. I have all of the IIS settings as they
> >are specified in documentation.
> >
> >My setup: Reporting Services SP2, IIS6, Windows 2003 Server, SQL Server
> >2000, installed on a non-default website following instructions on
> >SQLJunkies.com by Trent Miesner(although I'm pretty sure this isn't the
> >problem).
> >
> >Thanks in advance,
> >
> >Sam
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1
>
"Text column data incomplete" error during replication
I'm doing a transactional replication for the first time between 2
servers.
After 14 minutes of running (according to the logs), the replicaton
crashes. This is what I find in the log file generated by the
distribution agent. It starts bulk copying data from many tables until
it finally crashes on the first copy.
Any ideas why?
What does it mean 'Text column data incomplete'.
Table Parcel has 2 text columns that can be NULL.
Also, at the time the replication was taking place, it is possible
other transactions might have been being added/deleted from
Parcel......could this produce this type of error?
Here goes the erro log:
Bulk copying data into table 'Parcel'
:
Bulk copying data into table 'Item Specific Cost Factor'
:
Bulk copying data into table 'Item Appraisal Details'
:
:
Agent message code 20037. The process could not bulk copy into table
'"Parcel"'.
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
414, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0004158900000066000100000001, Command ID = 414
Message: Replication-Replication Distribution Subsystem: agent
SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
failed.
The process could not bulk copy into table '"Parcel"'.[6/16/2004
10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
N'TestDB', ?)}
ErrorId = 9, SourceTypeId = 1
ErrorCode = ''
ErrorText = 'select * from "Parcel Item" where 1 = 2
'
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: select * from "Parcel Item" where 1 = 2
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: 68.216.171.51
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
[6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
table ''"Parcel"''.'
Disconnecting from Subscriber '......'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
is the database collations on the subscriber and publisher the same?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Rocio" <rocio.katsanis@.softwareservices.net> wrote in message
news:7b914006.0406161119.2936cea7@.posting.google.c om...
> I'm doing a transactional replication for the first time between 2
> servers.
> After 14 minutes of running (according to the logs), the replicaton
> crashes. This is what I find in the log file generated by the
> distribution agent. It starts bulk copying data from many tables until
> it finally crashes on the first copy.
> Any ideas why?
> What does it mean 'Text column data incomplete'.
> Table Parcel has 2 text columns that can be NULL.
> Also, at the time the replication was taking place, it is possible
> other transactions might have been being added/deleted from
> Parcel......could this produce this type of error?
> Here goes the erro log:
> Bulk copying data into table 'Parcel'
> :
> Bulk copying data into table 'Item Specific Cost Factor'
> :
> Bulk copying data into table 'Item Appraisal Details'
> :
> :
> Agent message code 20037. The process could not bulk copy into table
> '"Parcel"'.
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
> 414, 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
> Transaction Seqno = 0004158900000066000100000001, Command ID = 414
> Message: Replication-Replication Distribution Subsystem: agent
> SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
> failed.
> The process could not bulk copy into table '"Parcel"'.[6/16/2004
> 10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
> 14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
> N'TestDB', ?)}
> ErrorId = 9, SourceTypeId = 1
> ErrorCode = ''
> ErrorText = 'select * from "Parcel Item" where 1 = 2
> '
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: select * from "Parcel Item" where 1 = 2
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: 68.216.171.51
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> [6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
> table ''"Parcel"''.'
> Disconnecting from Subscriber '......'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
|||Hi Hillary,
I treid the same replication 3 more times, and the erro I got on those 3
times was different than the one I published here. Now the error is:
Category:SQLSERVER
Source: <IP of subscriber...>
Number: 2812
Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
[6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
datastream processing of parameterized command.'
Disconnecting from Subscriber '<IP of subscriber...>'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
If I looked carefully at this log file, and it seems to me that such
stored procedure is somehow created:
[6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
"[sp_MSupd_Entity Address]"
@.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
int,@.c10 varchar(500),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "Entity Address" set
"Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"Address ID" end
,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "Entity Address" set
"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
however, if I look at TestDB in my subscriber, I can find
[sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
with brackets, the other not.
why 2 sp's anyway (with & without brackets)?
how can I control the creation of this procedure? I looked at the
properties tab of this publictaion at the Publisher, under Article
Entity Address, and make sure all the options are selected (e.g. Replace
INSERT commands with this stored procedure, etc).
The only option I could not access is the one at the very bottom that
causes replication to use the columns name. It was just enabled.
Any ideas?
btw: your book about Transactional & Snapshot replication looks good, I
haven't found a good book on replication yet! Is it out already?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||on your publisher do this
sp_scriptpublicationcustomprocs 'publicationName'
then in your results pan, copy the results and then paste them into another ISQLW window which you open into your subsciption database. then execute this script there.
Restart your distribution agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rocio Katsanis" wrote:
> Hi Hillary,
> I treid the same replication 3 more times, and the erro I got on those 3
> times was different than the one I published here. Now the error is:
> Category:SQLSERVER
> Source: <IP of subscriber...>
> Number: 2812
> Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
> [6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
> datastream processing of parameterized command.'
> Disconnecting from Subscriber '<IP of subscriber...>'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
> If I looked carefully at this log file, and it seems to me that such
> stored procedure is somehow created:
> [6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
> "[sp_MSupd_Entity Address]"
> @.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
> int,@.c10 varchar(500),@.pkc1 int
> ,@.bitmap binary(2)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "Entity Address" set
> "Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "Address ID" end
> ,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "Entity Address" set
> "Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> however, if I look at TestDB in my subscriber, I can find
> [sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
> with brackets, the other not.
> why 2 sp's anyway (with & without brackets)?
> how can I control the creation of this procedure? I looked at the
> properties tab of this publictaion at the Publisher, under Article
> Entity Address, and make sure all the options are selected (e.g. Replace
> INSERT commands with this stored procedure, etc).
> The only option I could not access is the one at the very bottom that
> causes replication to use the columns name. It was just enabled.
> Any ideas?
> btw: your book about Transactional & Snapshot replication looks good, I
> haven't found a good book on replication yet! Is it out already?
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
servers.
After 14 minutes of running (according to the logs), the replicaton
crashes. This is what I find in the log file generated by the
distribution agent. It starts bulk copying data from many tables until
it finally crashes on the first copy.
Any ideas why?
What does it mean 'Text column data incomplete'.
Table Parcel has 2 text columns that can be NULL.
Also, at the time the replication was taking place, it is possible
other transactions might have been being added/deleted from
Parcel......could this produce this type of error?
Here goes the erro log:
Bulk copying data into table 'Parcel'
:
Bulk copying data into table 'Item Specific Cost Factor'
:
Bulk copying data into table 'Item Appraisal Details'
:
:
Agent message code 20037. The process could not bulk copy into table
'"Parcel"'.
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
414, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
Transaction Seqno = 0004158900000066000100000001, Command ID = 414
Message: Replication-Replication Distribution Subsystem: agent
SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
failed.
The process could not bulk copy into table '"Parcel"'.[6/16/2004
10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
N'TestDB', ?)}
ErrorId = 9, SourceTypeId = 1
ErrorCode = ''
ErrorText = 'select * from "Parcel Item" where 1 = 2
'
[6/16/2004 10:37:08 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: select * from "Parcel Item" where 1 = 2
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: 68.216.171.51
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
ErrorId = 9, SourceTypeId = 4
ErrorCode = 'S1000'
ErrorText = 'Text column data incomplete'
[6/16/2004 10:37:09 AM]SERVER.distribution: {call
sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1000
Message: Text column data incomplete
[6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
table ''"Parcel"''.'
Disconnecting from Subscriber '......'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
is the database collations on the subscriber and publisher the same?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Rocio" <rocio.katsanis@.softwareservices.net> wrote in message
news:7b914006.0406161119.2936cea7@.posting.google.c om...
> I'm doing a transactional replication for the first time between 2
> servers.
> After 14 minutes of running (according to the logs), the replicaton
> crashes. This is what I find in the log file generated by the
> distribution agent. It starts bulk copying data from many tables until
> it finally crashes on the first copy.
> Any ideas why?
> What does it mean 'Text column data incomplete'.
> Table Parcel has 2 text columns that can be NULL.
> Also, at the time the replication was taking place, it is possible
> other transactions might have been being added/deleted from
> Parcel......could this produce this type of error?
> Here goes the erro log:
> Bulk copying data into table 'Parcel'
> :
> Bulk copying data into table 'Item Specific Cost Factor'
> :
> Bulk copying data into table 'Item Appraisal Details'
> :
> :
> Agent message code 20037. The process could not bulk copy into table
> '"Parcel"'.
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?,
> 414, 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 9,
> Transaction Seqno = 0004158900000066000100000001, Command ID = 414
> Message: Replication-Replication Distribution Subsystem: agent
> SERVER-Acadia_Final_Assessor-Acadia_Final_Assessor-68.216.171.51-4
> failed.
> The process could not bulk copy into table '"Parcel"'.[6/16/2004
> 10:37:08 AM]SERVER.distribution: {call sp_MSadd_repl_alert(3, 4, 9,
> 14151, ?, 414, N'SERVER', N'Acadia_Final_Assessor', N'68.216.171.51',
> N'TestDB', ?)}
> ErrorId = 9, SourceTypeId = 1
> ErrorCode = ''
> ErrorText = 'select * from "Parcel Item" where 1 = 2
> '
> [6/16/2004 10:37:08 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 1, ?, N'', ?)}
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: select * from "Parcel Item" where 1 = 2
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: 68.216.171.51
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> ErrorId = 9, SourceTypeId = 4
> ErrorCode = 'S1000'
> ErrorText = 'Text column data incomplete'
> [6/16/2004 10:37:09 AM]SERVER.distribution: {call
> sp_MSadd_repl_error(9, 0, 4, ?, N'S1000', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1000
> Message: Text column data incomplete
> [6/16/2004 10:37:09 AM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'The process could not bulk copy into
> table ''"Parcel"''.'
> Disconnecting from Subscriber '......'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
|||Hi Hillary,
I treid the same replication 3 more times, and the erro I got on those 3
times was different than the one I published here. Now the error is:
Category:SQLSERVER
Source: <IP of subscriber...>
Number: 2812
Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
[6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
datastream processing of parameterized command.'
Disconnecting from Subscriber '<IP of subscriber...>'
Disconnecting from Distributor 'SERVER'
Disconnecting from Distributor History 'SERVER'
If I looked carefully at this log file, and it seems to me that such
stored procedure is somehow created:
[6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
"[sp_MSupd_Entity Address]"
@.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
int,@.c10 varchar(500),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "Entity Address" set
"Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"Address ID" end
,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "Entity Address" set
"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"Entity ID" end
,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
ID" end
,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"State ID" end
,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "Postal Code ID" end
,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"Zip Last 4" end
,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"Country ID" end
,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
else "Primary Address" end
,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
else "Address Type ID" end
,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"Address Lines" end
where "Address ID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
however, if I look at TestDB in my subscriber, I can find
[sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
with brackets, the other not.
why 2 sp's anyway (with & without brackets)?
how can I control the creation of this procedure? I looked at the
properties tab of this publictaion at the Publisher, under Article
Entity Address, and make sure all the options are selected (e.g. Replace
INSERT commands with this stored procedure, etc).
The only option I could not access is the one at the very bottom that
causes replication to use the columns name. It was just enabled.
Any ideas?
btw: your book about Transactional & Snapshot replication looks good, I
haven't found a good book on replication yet! Is it out already?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||on your publisher do this
sp_scriptpublicationcustomprocs 'publicationName'
then in your results pan, copy the results and then paste them into another ISQLW window which you open into your subsciption database. then execute this script there.
Restart your distribution agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rocio Katsanis" wrote:
> Hi Hillary,
> I treid the same replication 3 more times, and the erro I got on those 3
> times was different than the one I published here. Now the error is:
> Category:SQLSERVER
> Source: <IP of subscriber...>
> Number: 2812
> Message: Could not find stored procedure 'sp_MSupd_Entity Address'.
> [6/16/2004 8:24:39 PM]68.216.171.51.TestDB: exec
> dbo.sp_MSupdatelastsyncinfo N'SERVER',N'Acadia_Final_Assessor',
> N'Acadia_Final_Assessor', 0, 6, N'Error at parameter 199 during
> datastream processing of parameterized command.'
> Disconnecting from Subscriber '<IP of subscriber...>'
> Disconnecting from Distributor 'SERVER'
> Disconnecting from Distributor History 'SERVER'
> If I looked carefully at this log file, and it seems to me that such
> stored procedure is somehow created:
> [6/16/2004 8:01:12 PM]<IP of subscriber...>.TestDB: create procedure
> "[sp_MSupd_Entity Address]"
> @.c1 int,@.c2 int,@.c3 int,@.c4 int,@.c5 int,@.c6 char(4),@.c7 int,@.c8 bit,@.c9
> int,@.c10 varchar(500),@.pkc1 int
> ,@.bitmap binary(2)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "Entity Address" set
> "Address ID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "Address ID" end
> ,"Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "Entity Address" set
> "Entity ID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "Entity ID" end
> ,"City ID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else "City
> ID" end
> ,"State ID" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "State ID" end
> ,"Postal Code ID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "Postal Code ID" end
> ,"Zip Last 4" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "Zip Last 4" end
> ,"Country ID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "Country ID" end
> ,"Primary Address" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8
> else "Primary Address" end
> ,"Address Type ID" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9
> else "Address Type ID" end
> ,"Address Lines" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "Address Lines" end
> where "Address ID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> however, if I look at TestDB in my subscriber, I can find
> [sp_MSupd_Entity Address] but not sp_MSupd_Entity Address, note one is
> with brackets, the other not.
> why 2 sp's anyway (with & without brackets)?
> how can I control the creation of this procedure? I looked at the
> properties tab of this publictaion at the Publisher, under Article
> Entity Address, and make sure all the options are selected (e.g. Replace
> INSERT commands with this stored procedure, etc).
> The only option I could not access is the one at the very bottom that
> causes replication to use the columns name. It was just enabled.
> Any ideas?
> btw: your book about Transactional & Snapshot replication looks good, I
> haven't found a good book on replication yet! Is it out already?
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
"Suddently" can't connect to sql server (SP security prob, maybe?)
Hi! I'm running sql server 2003 on a network for my small business; I'm
having probs connecting to the server from my desktop. What's odd is that
this prob sudently occured yesterday...I had been connecting before that
(like, for several years?) and now I can't hit the box thorugh either Query
Analyzer or Enterprise manager.
The server is online because I can map to it and see the directories. Also:
I have a notebook which still can see the SQL Server box with NO Problems!
Which begs the question: what changed over the weekend? I seem to remember
that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
these two computers recently. Are you aware of any security changes that may
have been slipped in on either the server or the client side to explain this?
I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
Thanks in advance for your help. I rally appreciate it!
Roger,
> I have a notebook which still can see the SQL Server box with NO Problems!
The box or the SQL Server i.e., the databases, tables and data. Can you
acesss the SQL Server by IP address? Does the OSQL utility work? What if
you try creating a DSN and 'test the connection' - just to rule out the SQL
tools as a possible cause? Have you tried restarting this client?
HTH
Jerry
"Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
> Hi! I'm running sql server 2003 on a network for my small business; I'm
> having probs connecting to the server from my desktop. What's odd is that
> this prob sudently occured yesterday...I had been connecting before that
> (like, for several years?) and now I can't hit the box thorugh either
> Query
> Analyzer or Enterprise manager.
> The server is online because I can map to it and see the directories.
> Also:
> I have a notebook which still can see the SQL Server box with NO Problems!
> Which begs the question: what changed over the weekend? I seem to remember
> that a OS SP/Hotfix/etc was automatically downloaded and installed on one
> of
> these two computers recently. Are you aware of any security changes that
> may
> have been slipped in on either the server or the client side to explain
> this?
> I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
> Thanks in advance for your help. I rally appreciate it!
|||Roger,
So if I read your e-mail correctly from your desktop you can login via
Query Analyzer or Enterprise Manager but from another box you can login
to the SQL Server box using another tool? Could there have been a
network change the happened on the weekend where you box got a new IP or
the network security changed?
If the SQL Server is up [you can login via another client or directly on
the box itself] then the problem has to be with your computer or network
permissions. You may check to see if your IP changed or perhaps they
access from your IP to the server was removed.
Shahryar
Roger Willcode wrote:
>Hi! I'm running sql server 2003 on a network for my small business; I'm
>having probs connecting to the server from my desktop. What's odd is that
>this prob sudently occured yesterday...I had been connecting before that
>(like, for several years?) and now I can't hit the box thorugh either Query
>Analyzer or Enterprise manager.
>The server is online because I can map to it and see the directories. Also:
>I have a notebook which still can see the SQL Server box with NO Problems!
>Which begs the question: what changed over the weekend? I seem to remember
>that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
>these two computers recently. Are you aware of any security changes that may
>have been slipped in on either the server or the client side to explain this?
>I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
>Thanks in advance for your help. I rally appreciate it!
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
having probs connecting to the server from my desktop. What's odd is that
this prob sudently occured yesterday...I had been connecting before that
(like, for several years?) and now I can't hit the box thorugh either Query
Analyzer or Enterprise manager.
The server is online because I can map to it and see the directories. Also:
I have a notebook which still can see the SQL Server box with NO Problems!
Which begs the question: what changed over the weekend? I seem to remember
that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
these two computers recently. Are you aware of any security changes that may
have been slipped in on either the server or the client side to explain this?
I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
Thanks in advance for your help. I rally appreciate it!
Roger,
> I have a notebook which still can see the SQL Server box with NO Problems!
The box or the SQL Server i.e., the databases, tables and data. Can you
acesss the SQL Server by IP address? Does the OSQL utility work? What if
you try creating a DSN and 'test the connection' - just to rule out the SQL
tools as a possible cause? Have you tried restarting this client?
HTH
Jerry
"Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
> Hi! I'm running sql server 2003 on a network for my small business; I'm
> having probs connecting to the server from my desktop. What's odd is that
> this prob sudently occured yesterday...I had been connecting before that
> (like, for several years?) and now I can't hit the box thorugh either
> Query
> Analyzer or Enterprise manager.
> The server is online because I can map to it and see the directories.
> Also:
> I have a notebook which still can see the SQL Server box with NO Problems!
> Which begs the question: what changed over the weekend? I seem to remember
> that a OS SP/Hotfix/etc was automatically downloaded and installed on one
> of
> these two computers recently. Are you aware of any security changes that
> may
> have been slipped in on either the server or the client side to explain
> this?
> I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
> Thanks in advance for your help. I rally appreciate it!
|||Roger,
So if I read your e-mail correctly from your desktop you can login via
Query Analyzer or Enterprise Manager but from another box you can login
to the SQL Server box using another tool? Could there have been a
network change the happened on the weekend where you box got a new IP or
the network security changed?
If the SQL Server is up [you can login via another client or directly on
the box itself] then the problem has to be with your computer or network
permissions. You may check to see if your IP changed or perhaps they
access from your IP to the server was removed.
Shahryar
Roger Willcode wrote:
>Hi! I'm running sql server 2003 on a network for my small business; I'm
>having probs connecting to the server from my desktop. What's odd is that
>this prob sudently occured yesterday...I had been connecting before that
>(like, for several years?) and now I can't hit the box thorugh either Query
>Analyzer or Enterprise manager.
>The server is online because I can map to it and see the directories. Also:
>I have a notebook which still can see the SQL Server box with NO Problems!
>Which begs the question: what changed over the weekend? I seem to remember
>that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
>these two computers recently. Are you aware of any security changes that may
>have been slipped in on either the server or the client side to explain this?
>I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
>Thanks in advance for your help. I rally appreciate it!
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
"Suddently" can't connect to sql server (SP security prob, maybe?)
Hi! I'm running sql server 2003 on a network for my small business; I'm
having probs connecting to the server from my desktop. What's odd is that
this prob sudently occured yesterday...I had been connecting before that
(like, for several years?) and now I can't hit the box thorugh either Query
Analyzer or Enterprise manager.
The server is online because I can map to it and see the directories. Also:
I have a notebook which still can see the SQL Server box with NO Problems!
Which begs the question: what changed over the weekend? I seem to remember
that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
these two computers recently. Are you aware of any security changes that may
have been slipped in on either the server or the client side to explain this?
I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
Thanks in advance for your help. I rally appreciate it!Roger,
> I have a notebook which still can see the SQL Server box with NO Problems!
The box or the SQL Server i.e., the databases, tables and data. Can you
acesss the SQL Server by IP address? Does the OSQL utility work? What if
you try creating a DSN and 'test the connection' - just to rule out the SQL
tools as a possible cause? Have you tried restarting this client?
HTH
Jerry
"Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
> Hi! I'm running sql server 2003 on a network for my small business; I'm
> having probs connecting to the server from my desktop. What's odd is that
> this prob sudently occured yesterday...I had been connecting before that
> (like, for several years?) and now I can't hit the box thorugh either
> Query
> Analyzer or Enterprise manager.
> The server is online because I can map to it and see the directories.
> Also:
> I have a notebook which still can see the SQL Server box with NO Problems!
> Which begs the question: what changed over the weekend? I seem to remember
> that a OS SP/Hotfix/etc was automatically downloaded and installed on one
> of
> these two computers recently. Are you aware of any security changes that
> may
> have been slipped in on either the server or the client side to explain
> this?
> I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
> Thanks in advance for your help. I rally appreciate it!|||Roger,
So if I read your e-mail correctly from your desktop you can login via
Query Analyzer or Enterprise Manager but from another box you can login
to the SQL Server box using another tool? Could there have been a
network change the happened on the weekend where you box got a new IP or
the network security changed?
If the SQL Server is up [you can login via another client or directly on
the box itself] then the problem has to be with your computer or network
permissions. You may check to see if your IP changed or perhaps they
access from your IP to the server was removed.
Shahryar
Roger Willcode wrote:
>Hi! I'm running sql server 2003 on a network for my small business; I'm
>having probs connecting to the server from my desktop. What's odd is that
>this prob sudently occured yesterday...I had been connecting before that
>(like, for several years?) and now I can't hit the box thorugh either Query
>Analyzer or Enterprise manager.
>The server is online because I can map to it and see the directories. Also:
>I have a notebook which still can see the SQL Server box with NO Problems!
>Which begs the question: what changed over the weekend? I seem to remember
>that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
>these two computers recently. Are you aware of any security changes that may
>have been slipped in on either the server or the client side to explain this?
>I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
>Thanks in advance for your help. I rally appreciate it!
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Jerry, Shahryar:
First: Thanks to both you you for replying! It works now -- although I'm not
exactly sure what changed.
If it helps for anyone else reading this post; I did restart both the client
and the server to no avail. Also, I don't think there was a change in the
network permissions; I'm the only user on this network! I *did* try
connecting with the IP address and that was also a dead end.
Just for TheHalibut, I tried Jerry's suggestion of creating a ODBC
connection. When I selected TCP/IP as the connection it failed to connect.
But when I clicked the "named pipes" radio button it worked. Even more
puzzling was that I can now go back to Enterprise Manager -- and without any
further changes -- it works again.
Any body have any clues as to what happened? Also, Jerry refered to a "OSQL
utility." Sorry to be dense, but what is that?
Thanks again for your help. I'm good for now, and if you get a chance to
post any thouughts you have on my questions, I'll be even better next time!
Take care.
"Jerry Spivey" wrote:
> Roger,
> > I have a notebook which still can see the SQL Server box with NO Problems!
> The box or the SQL Server i.e., the databases, tables and data. Can you
> acesss the SQL Server by IP address? Does the OSQL utility work? What if
> you try creating a DSN and 'test the connection' - just to rule out the SQL
> tools as a possible cause? Have you tried restarting this client?
> HTH
> Jerry
> "Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
> news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
> > Hi! I'm running sql server 2003 on a network for my small business; I'm
> > having probs connecting to the server from my desktop. What's odd is that
> > this prob sudently occured yesterday...I had been connecting before that
> > (like, for several years?) and now I can't hit the box thorugh either
> > Query
> > Analyzer or Enterprise manager.
> >
> > The server is online because I can map to it and see the directories.
> > Also:
> > I have a notebook which still can see the SQL Server box with NO Problems!
> >
> > Which begs the question: what changed over the weekend? I seem to remember
> > that a OS SP/Hotfix/etc was automatically downloaded and installed on one
> > of
> > these two computers recently. Are you aware of any security changes that
> > may
> > have been slipped in on either the server or the client side to explain
> > this?
> >
> > I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
> >
> > Thanks in advance for your help. I rally appreciate it!
>
>|||Roger,
The OSQL utility is the CMD line utility for querying SQL Server.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp
HTH
Jerry
"Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
news:1031B9B4-3AF4-4BF3-8B7D-A0A97E92057A@.microsoft.com...
> Jerry, Shahryar:
> First: Thanks to both you you for replying! It works now -- although I'm
> not
> exactly sure what changed.
> If it helps for anyone else reading this post; I did restart both the
> client
> and the server to no avail. Also, I don't think there was a change in the
> network permissions; I'm the only user on this network! I *did* try
> connecting with the IP address and that was also a dead end.
> Just for TheHalibut, I tried Jerry's suggestion of creating a ODBC
> connection. When I selected TCP/IP as the connection it failed to connect.
> But when I clicked the "named pipes" radio button it worked. Even more
> puzzling was that I can now go back to Enterprise Manager -- and without
> any
> further changes -- it works again.
> Any body have any clues as to what happened? Also, Jerry refered to a
> "OSQL
> utility." Sorry to be dense, but what is that?
> Thanks again for your help. I'm good for now, and if you get a chance to
> post any thouughts you have on my questions, I'll be even better next
> time!
> Take care.
>
> "Jerry Spivey" wrote:
>> Roger,
>> > I have a notebook which still can see the SQL Server box with NO
>> > Problems!
>> The box or the SQL Server i.e., the databases, tables and data. Can you
>> acesss the SQL Server by IP address? Does the OSQL utility work? What
>> if
>> you try creating a DSN and 'test the connection' - just to rule out the
>> SQL
>> tools as a possible cause? Have you tried restarting this client?
>> HTH
>> Jerry
>> "Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in
>> message
>> news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
>> > Hi! I'm running sql server 2003 on a network for my small business; I'm
>> > having probs connecting to the server from my desktop. What's odd is
>> > that
>> > this prob sudently occured yesterday...I had been connecting before
>> > that
>> > (like, for several years?) and now I can't hit the box thorugh either
>> > Query
>> > Analyzer or Enterprise manager.
>> >
>> > The server is online because I can map to it and see the directories.
>> > Also:
>> > I have a notebook which still can see the SQL Server box with NO
>> > Problems!
>> >
>> > Which begs the question: what changed over the weekend? I seem to
>> > remember
>> > that a OS SP/Hotfix/etc was automatically downloaded and installed on
>> > one
>> > of
>> > these two computers recently. Are you aware of any security changes
>> > that
>> > may
>> > have been slipped in on either the server or the client side to explain
>> > this?
>> >
>> > I'm running Win Server 2003 on the server and XP Prof SP2 on the
>> > desktop.
>> >
>> > Thanks in advance for your help. I rally appreciate it!
>>
having probs connecting to the server from my desktop. What's odd is that
this prob sudently occured yesterday...I had been connecting before that
(like, for several years?) and now I can't hit the box thorugh either Query
Analyzer or Enterprise manager.
The server is online because I can map to it and see the directories. Also:
I have a notebook which still can see the SQL Server box with NO Problems!
Which begs the question: what changed over the weekend? I seem to remember
that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
these two computers recently. Are you aware of any security changes that may
have been slipped in on either the server or the client side to explain this?
I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
Thanks in advance for your help. I rally appreciate it!Roger,
> I have a notebook which still can see the SQL Server box with NO Problems!
The box or the SQL Server i.e., the databases, tables and data. Can you
acesss the SQL Server by IP address? Does the OSQL utility work? What if
you try creating a DSN and 'test the connection' - just to rule out the SQL
tools as a possible cause? Have you tried restarting this client?
HTH
Jerry
"Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
> Hi! I'm running sql server 2003 on a network for my small business; I'm
> having probs connecting to the server from my desktop. What's odd is that
> this prob sudently occured yesterday...I had been connecting before that
> (like, for several years?) and now I can't hit the box thorugh either
> Query
> Analyzer or Enterprise manager.
> The server is online because I can map to it and see the directories.
> Also:
> I have a notebook which still can see the SQL Server box with NO Problems!
> Which begs the question: what changed over the weekend? I seem to remember
> that a OS SP/Hotfix/etc was automatically downloaded and installed on one
> of
> these two computers recently. Are you aware of any security changes that
> may
> have been slipped in on either the server or the client side to explain
> this?
> I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
> Thanks in advance for your help. I rally appreciate it!|||Roger,
So if I read your e-mail correctly from your desktop you can login via
Query Analyzer or Enterprise Manager but from another box you can login
to the SQL Server box using another tool? Could there have been a
network change the happened on the weekend where you box got a new IP or
the network security changed?
If the SQL Server is up [you can login via another client or directly on
the box itself] then the problem has to be with your computer or network
permissions. You may check to see if your IP changed or perhaps they
access from your IP to the server was removed.
Shahryar
Roger Willcode wrote:
>Hi! I'm running sql server 2003 on a network for my small business; I'm
>having probs connecting to the server from my desktop. What's odd is that
>this prob sudently occured yesterday...I had been connecting before that
>(like, for several years?) and now I can't hit the box thorugh either Query
>Analyzer or Enterprise manager.
>The server is online because I can map to it and see the directories. Also:
>I have a notebook which still can see the SQL Server box with NO Problems!
>Which begs the question: what changed over the weekend? I seem to remember
>that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
>these two computers recently. Are you aware of any security changes that may
>have been slipped in on either the server or the client side to explain this?
>I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
>Thanks in advance for your help. I rally appreciate it!
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Jerry, Shahryar:
First: Thanks to both you you for replying! It works now -- although I'm not
exactly sure what changed.
If it helps for anyone else reading this post; I did restart both the client
and the server to no avail. Also, I don't think there was a change in the
network permissions; I'm the only user on this network! I *did* try
connecting with the IP address and that was also a dead end.
Just for TheHalibut, I tried Jerry's suggestion of creating a ODBC
connection. When I selected TCP/IP as the connection it failed to connect.
But when I clicked the "named pipes" radio button it worked. Even more
puzzling was that I can now go back to Enterprise Manager -- and without any
further changes -- it works again.
Any body have any clues as to what happened? Also, Jerry refered to a "OSQL
utility." Sorry to be dense, but what is that?
Thanks again for your help. I'm good for now, and if you get a chance to
post any thouughts you have on my questions, I'll be even better next time!
Take care.
"Jerry Spivey" wrote:
> Roger,
> > I have a notebook which still can see the SQL Server box with NO Problems!
> The box or the SQL Server i.e., the databases, tables and data. Can you
> acesss the SQL Server by IP address? Does the OSQL utility work? What if
> you try creating a DSN and 'test the connection' - just to rule out the SQL
> tools as a possible cause? Have you tried restarting this client?
> HTH
> Jerry
> "Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
> news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
> > Hi! I'm running sql server 2003 on a network for my small business; I'm
> > having probs connecting to the server from my desktop. What's odd is that
> > this prob sudently occured yesterday...I had been connecting before that
> > (like, for several years?) and now I can't hit the box thorugh either
> > Query
> > Analyzer or Enterprise manager.
> >
> > The server is online because I can map to it and see the directories.
> > Also:
> > I have a notebook which still can see the SQL Server box with NO Problems!
> >
> > Which begs the question: what changed over the weekend? I seem to remember
> > that a OS SP/Hotfix/etc was automatically downloaded and installed on one
> > of
> > these two computers recently. Are you aware of any security changes that
> > may
> > have been slipped in on either the server or the client side to explain
> > this?
> >
> > I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
> >
> > Thanks in advance for your help. I rally appreciate it!
>
>|||Roger,
The OSQL utility is the CMD line utility for querying SQL Server.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp
HTH
Jerry
"Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
news:1031B9B4-3AF4-4BF3-8B7D-A0A97E92057A@.microsoft.com...
> Jerry, Shahryar:
> First: Thanks to both you you for replying! It works now -- although I'm
> not
> exactly sure what changed.
> If it helps for anyone else reading this post; I did restart both the
> client
> and the server to no avail. Also, I don't think there was a change in the
> network permissions; I'm the only user on this network! I *did* try
> connecting with the IP address and that was also a dead end.
> Just for TheHalibut, I tried Jerry's suggestion of creating a ODBC
> connection. When I selected TCP/IP as the connection it failed to connect.
> But when I clicked the "named pipes" radio button it worked. Even more
> puzzling was that I can now go back to Enterprise Manager -- and without
> any
> further changes -- it works again.
> Any body have any clues as to what happened? Also, Jerry refered to a
> "OSQL
> utility." Sorry to be dense, but what is that?
> Thanks again for your help. I'm good for now, and if you get a chance to
> post any thouughts you have on my questions, I'll be even better next
> time!
> Take care.
>
> "Jerry Spivey" wrote:
>> Roger,
>> > I have a notebook which still can see the SQL Server box with NO
>> > Problems!
>> The box or the SQL Server i.e., the databases, tables and data. Can you
>> acesss the SQL Server by IP address? Does the OSQL utility work? What
>> if
>> you try creating a DSN and 'test the connection' - just to rule out the
>> SQL
>> tools as a possible cause? Have you tried restarting this client?
>> HTH
>> Jerry
>> "Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in
>> message
>> news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
>> > Hi! I'm running sql server 2003 on a network for my small business; I'm
>> > having probs connecting to the server from my desktop. What's odd is
>> > that
>> > this prob sudently occured yesterday...I had been connecting before
>> > that
>> > (like, for several years?) and now I can't hit the box thorugh either
>> > Query
>> > Analyzer or Enterprise manager.
>> >
>> > The server is online because I can map to it and see the directories.
>> > Also:
>> > I have a notebook which still can see the SQL Server box with NO
>> > Problems!
>> >
>> > Which begs the question: what changed over the weekend? I seem to
>> > remember
>> > that a OS SP/Hotfix/etc was automatically downloaded and installed on
>> > one
>> > of
>> > these two computers recently. Are you aware of any security changes
>> > that
>> > may
>> > have been slipped in on either the server or the client side to explain
>> > this?
>> >
>> > I'm running Win Server 2003 on the server and XP Prof SP2 on the
>> > desktop.
>> >
>> > Thanks in advance for your help. I rally appreciate it!
>>
"Suddently" can't connect to sql server (SP security prob, maybe?)
Hi! I'm running sql server 2003 on a network for my small business; I'm
having probs connecting to the server from my desktop. What's odd is that
this prob sudently occured yesterday...I had been connecting before that
(like, for several years?) and now I can't hit the box thorugh either Query
Analyzer or Enterprise manager.
The server is online because I can map to it and see the directories. Also:
I have a notebook which still can see the SQL Server box with NO Problems!
Which begs the question: what changed over the weekend? I seem to remember
that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
these two computers recently. Are you aware of any security changes that may
have been slipped in on either the server or the client side to explain this
?
I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
Thanks in advance for your help. I rally appreciate it!Roger,
> I have a notebook which still can see the SQL Server box with NO Problems!
The box or the SQL Server i.e., the databases, tables and data. Can you
acesss the SQL Server by IP address? Does the OSQL utility work? What if
you try creating a DSN and 'test the connection' - just to rule out the SQL
tools as a possible cause? Have you tried restarting this client?
HTH
Jerry
"Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
> Hi! I'm running sql server 2003 on a network for my small business; I'm
> having probs connecting to the server from my desktop. What's odd is that
> this prob sudently occured yesterday...I had been connecting before that
> (like, for several years?) and now I can't hit the box thorugh either
> Query
> Analyzer or Enterprise manager.
> The server is online because I can map to it and see the directories.
> Also:
> I have a notebook which still can see the SQL Server box with NO Problems!
> Which begs the question: what changed over the weekend? I seem to remember
> that a OS SP/Hotfix/etc was automatically downloaded and installed on one
> of
> these two computers recently. Are you aware of any security changes that
> may
> have been slipped in on either the server or the client side to explain
> this?
> I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
> Thanks in advance for your help. I rally appreciate it!|||Roger,
So if I read your e-mail correctly from your desktop you can login via
Query Analyzer or Enterprise Manager but from another box you can login
to the SQL Server box using another tool? Could there have been a
network change the happened on the weekend where you box got a new IP or
the network security changed?
If the SQL Server is up [you can login via another client or directly on
the box itself] then the problem has to be with your computer or network
permissions. You may check to see if your IP changed or perhaps they
access from your IP to the server was removed.
Shahryar
Roger Willcode wrote:
>Hi! I'm running sql server 2003 on a network for my small business; I'm
>having probs connecting to the server from my desktop. What's odd is that
>this prob sudently occured yesterday...I had been connecting before that
>(like, for several years?) and now I can't hit the box thorugh either Query
>Analyzer or Enterprise manager.
>The server is online because I can map to it and see the directories. Also:
>I have a notebook which still can see the SQL Server box with NO Problems!
>Which begs the question: what changed over the weekend? I seem to remember
>that a OS SP/Hotfix/etc was automatically downloaded and installed on one o
f
>these two computers recently. Are you aware of any security changes that ma
y
>have been slipped in on either the server or the client side to explain thi
s?
>I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
>Thanks in advance for your help. I rally appreciate it!
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.
having probs connecting to the server from my desktop. What's odd is that
this prob sudently occured yesterday...I had been connecting before that
(like, for several years?) and now I can't hit the box thorugh either Query
Analyzer or Enterprise manager.
The server is online because I can map to it and see the directories. Also:
I have a notebook which still can see the SQL Server box with NO Problems!
Which begs the question: what changed over the weekend? I seem to remember
that a OS SP/Hotfix/etc was automatically downloaded and installed on one of
these two computers recently. Are you aware of any security changes that may
have been slipped in on either the server or the client side to explain this
?
I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
Thanks in advance for your help. I rally appreciate it!Roger,
> I have a notebook which still can see the SQL Server box with NO Problems!
The box or the SQL Server i.e., the databases, tables and data. Can you
acesss the SQL Server by IP address? Does the OSQL utility work? What if
you try creating a DSN and 'test the connection' - just to rule out the SQL
tools as a possible cause? Have you tried restarting this client?
HTH
Jerry
"Roger Willcode" <RogerWillcode@.discussions.microsoft.com> wrote in message
news:FA1EA7E3-B9D7-4E06-96B9-D619A211088D@.microsoft.com...
> Hi! I'm running sql server 2003 on a network for my small business; I'm
> having probs connecting to the server from my desktop. What's odd is that
> this prob sudently occured yesterday...I had been connecting before that
> (like, for several years?) and now I can't hit the box thorugh either
> Query
> Analyzer or Enterprise manager.
> The server is online because I can map to it and see the directories.
> Also:
> I have a notebook which still can see the SQL Server box with NO Problems!
> Which begs the question: what changed over the weekend? I seem to remember
> that a OS SP/Hotfix/etc was automatically downloaded and installed on one
> of
> these two computers recently. Are you aware of any security changes that
> may
> have been slipped in on either the server or the client side to explain
> this?
> I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
> Thanks in advance for your help. I rally appreciate it!|||Roger,
So if I read your e-mail correctly from your desktop you can login via
Query Analyzer or Enterprise Manager but from another box you can login
to the SQL Server box using another tool? Could there have been a
network change the happened on the weekend where you box got a new IP or
the network security changed?
If the SQL Server is up [you can login via another client or directly on
the box itself] then the problem has to be with your computer or network
permissions. You may check to see if your IP changed or perhaps they
access from your IP to the server was removed.
Shahryar
Roger Willcode wrote:
>Hi! I'm running sql server 2003 on a network for my small business; I'm
>having probs connecting to the server from my desktop. What's odd is that
>this prob sudently occured yesterday...I had been connecting before that
>(like, for several years?) and now I can't hit the box thorugh either Query
>Analyzer or Enterprise manager.
>The server is online because I can map to it and see the directories. Also:
>I have a notebook which still can see the SQL Server box with NO Problems!
>Which begs the question: what changed over the weekend? I seem to remember
>that a OS SP/Hotfix/etc was automatically downloaded and installed on one o
f
>these two computers recently. Are you aware of any security changes that ma
y
>have been slipped in on either the server or the client side to explain thi
s?
>I'm running Win Server 2003 on the server and XP Prof SP2 on the desktop.
>Thanks in advance for your help. I rally appreciate it!
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.
Sunday, March 11, 2012
"Start Automatically when SQL Server Agent Starts" doesn't always work
We have a SQL job that always needs to be running and have the "Start
Automatically when SQL Server Agent Starts" option enabled. We've
noticed that on occassion this job is stopped. No errors / failure
notifications.
Any idea why this would happen? How can we get this to always run, or
at least notify us when the job stops?
Thanks,
RubensAre you on the latest Service Packs ? It would also be nice if you can =mention the SQL Server version you are presetly on ... -- HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions http://www32.brinkster.com/sqlvinod
"Rubens" <rubensrose@.hotmail.com> wrote in message =news:a988e2b1.0307150438.7e58dc87@.posting.google.com...
> We have a SQL job that always needs to be running and have the "Start
> Automatically when SQL Server Agent Starts" option enabled. We've
> noticed that on occassion this job is stopped. No errors / failure
> notifications.
> > Any idea why this would happen? How can we get this to always run, or
> at least notify us when the job stops?
> > Thanks,
> Rubens|||"Vinodk" <vinodk sct@.hotmail.com> wrote in message news:<erfG7AtSDHA.2148@.TK2MSFTNGP10.phx.gbl>...
> Are you on the latest Service Packs ? It would also be nice if you can
> mention the SQL Server version you are presetly on ...
Sorry, can't believe I didn't mention that. SQL Server 2000 with SP3 on W2K SP3.
Thanks,
Rubens|||"Vinodk" <vinodk sct@.hotmail.com> wrote in message news:<erfG7AtSDHA.2148@.TK2MSFTNGP10.phx.gbl>...
> Are you on the latest Service Packs ? It would also be nice if you can
> mention the SQL Server version you are presetly on ...
Sorry, can't believe I didn't mentioned that. SQL Server 2000 SP3.
Thanks,
Rubens
Automatically when SQL Server Agent Starts" option enabled. We've
noticed that on occassion this job is stopped. No errors / failure
notifications.
Any idea why this would happen? How can we get this to always run, or
at least notify us when the job stops?
Thanks,
RubensAre you on the latest Service Packs ? It would also be nice if you can =mention the SQL Server version you are presetly on ... -- HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions http://www32.brinkster.com/sqlvinod
"Rubens" <rubensrose@.hotmail.com> wrote in message =news:a988e2b1.0307150438.7e58dc87@.posting.google.com...
> We have a SQL job that always needs to be running and have the "Start
> Automatically when SQL Server Agent Starts" option enabled. We've
> noticed that on occassion this job is stopped. No errors / failure
> notifications.
> > Any idea why this would happen? How can we get this to always run, or
> at least notify us when the job stops?
> > Thanks,
> Rubens|||"Vinodk" <vinodk sct@.hotmail.com> wrote in message news:<erfG7AtSDHA.2148@.TK2MSFTNGP10.phx.gbl>...
> Are you on the latest Service Packs ? It would also be nice if you can
> mention the SQL Server version you are presetly on ...
Sorry, can't believe I didn't mention that. SQL Server 2000 with SP3 on W2K SP3.
Thanks,
Rubens|||"Vinodk" <vinodk sct@.hotmail.com> wrote in message news:<erfG7AtSDHA.2148@.TK2MSFTNGP10.phx.gbl>...
> Are you on the latest Service Packs ? It would also be nice if you can
> mention the SQL Server version you are presetly on ...
Sorry, can't believe I didn't mentioned that. SQL Server 2000 SP3.
Thanks,
Rubens
"SQL Server does not exist or access denied", again...
I'm have that darn problem...
Here is our setup:
Remote clustered SQL Server 2000 running on a Windows Server 2003 Enterprise Edition box.
Local IIS 5.1 web server (my development box) running on Windows XP Tablet Edition.
I'm using Visual Studio.Net 2003 and using ASP.Net 1.1.4322.
I have an ASP.Net application that hits the remote SQL Server. When I'm on my development box and viewing my ASP.Net project locally via IE, the application retrieves values from the remote SQL Server with no problems. If I get onto another machine, launch IE, and then hit my development box's web server (which is running the asp.net app), I get the dreaded "SQL Server does not exist or access is denied" msg.
In my ASP.Net app, I have impersonate set to true and the website is set only to windows authentication. The remote SQL Server is set to SQL and Windows authentication.
I'm at a loss on what to do, any help would be greatly appreciated.
Thanks,
ExitusLSU
Hi,This seems to be firewall issue.
Check it after disabling firewall.
http://bhatiaworld.blogspot.com/2005/12/dot-net-error-aspnet-website-or-web.html|||
I also forgot to mention my connection string. It is:
"Data Source=<server name>;Integrated Security=SSPI;Initial Catalog=<database name>"
I've tried using the IP address, but that didn't work.
Thanks,
ExitusLSU
Thursday, March 8, 2012
"Resetting" an obsolete snapshot
Berb,
running a snapshot agent in this case will just add the
new article and is different from a reinitialization.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks, Paul. That's just what I needed to know.
One more related question...
Will this "lock" the database tables? In other words, will the snapshot
process, in this case, prevent use of the database while it is in progress?
Brent
"Paul Ibison" wrote:
> Berb,
> running a snapshot agent in this case will just add the
> new article and is different from a reinitialization.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Brent,
this'll lock the table unless you are using transactional replication with
the concurrent snapshot option.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
running a snapshot agent in this case will just add the
new article and is different from a reinitialization.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks, Paul. That's just what I needed to know.
One more related question...
Will this "lock" the database tables? In other words, will the snapshot
process, in this case, prevent use of the database while it is in progress?
Brent
"Paul Ibison" wrote:
> Berb,
> running a snapshot agent in this case will just add the
> new article and is different from a reinitialization.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Brent,
this'll lock the table unless you are using transactional replication with
the concurrent snapshot option.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
"Planning" a database enviroment - a bit off topic
Hi
Hi
I'm about to plan a upgrade to our database enviroment.
We have 7 database servers running different kinds of SQL server (7.0,
2000, 2005) in an unmanaged enviroment,
The environment services wide range of business applikations and the demand
for availabilitiy and security is increasing and the environment should meet
the operational requirements led out from/required to ITIL best practice
principles.
What i'm looking for at resources including frameworks/toolkits,
guides/recommendations, consideration/decision for planning a database
enviroment that meets these requirements (i.e. the process upgrade/migrate
from A to B).
I've been searching the net for this but I can't seem to find any resources
that takes up this matter, so any help appriciated-
-KimKim,
Try:
http://support.microsoft.com/search...s=20&1033comm=1
and
http://www.microsoft.com/sql/soluti...de/default.mspx
HTH
Jerry
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:446d06c5$1@.news.carlbro.dk...
> Hi
> Hi
> I'm about to plan a upgrade to our database enviroment.
> We have 7 database servers running different kinds of SQL server (7.0,
> 2000, 2005) in an unmanaged enviroment,
> The environment services wide range of business applikations and the
> demand for availabilitiy and security is increasing and the environment
> should meet the operational requirements led out from/required to ITIL
> best practice principles.
> What i'm looking for at resources including frameworks/toolkits,
> guides/recommendations, consideration/decision for planning a database
> enviroment that meets these requirements (i.e. the process upgrade/migrate
> from A to B).
> I've been searching the net for this but I can't seem to find any
> resources that takes up this matter, so any help appriciated-
> -Kim
>|||Thanks, I found some good articles.
I still need some resources that takes a more holistic view of the process,
ie. not just how I plan to upgrade a certain databaseserver, but also how my
database environment should be build.
By environment I mean all the database servers and applications (and other
components) that communicate with the environment, that is, I need som
Architechtual design considerations for the _whole_ environment, including
operations, support etc. etc.
-Kim
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e66rzgseGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Kim,
> Try:
> http://support.microsoft.com/search...s=20&1033comm=1
> and
> http://www.microsoft.com/sql/soluti...de/default.mspx
> HTH
> Jerry
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:446d06c5$1@.news.carlbro.dk...
>
Hi
I'm about to plan a upgrade to our database enviroment.
We have 7 database servers running different kinds of SQL server (7.0,
2000, 2005) in an unmanaged enviroment,
The environment services wide range of business applikations and the demand
for availabilitiy and security is increasing and the environment should meet
the operational requirements led out from/required to ITIL best practice
principles.
What i'm looking for at resources including frameworks/toolkits,
guides/recommendations, consideration/decision for planning a database
enviroment that meets these requirements (i.e. the process upgrade/migrate
from A to B).
I've been searching the net for this but I can't seem to find any resources
that takes up this matter, so any help appriciated-
-KimKim,
Try:
http://support.microsoft.com/search...s=20&1033comm=1
and
http://www.microsoft.com/sql/soluti...de/default.mspx
HTH
Jerry
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:446d06c5$1@.news.carlbro.dk...
> Hi
> Hi
> I'm about to plan a upgrade to our database enviroment.
> We have 7 database servers running different kinds of SQL server (7.0,
> 2000, 2005) in an unmanaged enviroment,
> The environment services wide range of business applikations and the
> demand for availabilitiy and security is increasing and the environment
> should meet the operational requirements led out from/required to ITIL
> best practice principles.
> What i'm looking for at resources including frameworks/toolkits,
> guides/recommendations, consideration/decision for planning a database
> enviroment that meets these requirements (i.e. the process upgrade/migrate
> from A to B).
> I've been searching the net for this but I can't seem to find any
> resources that takes up this matter, so any help appriciated-
> -Kim
>|||Thanks, I found some good articles.
I still need some resources that takes a more holistic view of the process,
ie. not just how I plan to upgrade a certain databaseserver, but also how my
database environment should be build.
By environment I mean all the database servers and applications (and other
components) that communicate with the environment, that is, I need som
Architechtual design considerations for the _whole_ environment, including
operations, support etc. etc.
-Kim
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e66rzgseGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Kim,
> Try:
> http://support.microsoft.com/search...s=20&1033comm=1
> and
> http://www.microsoft.com/sql/soluti...de/default.mspx
> HTH
> Jerry
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:446d06c5$1@.news.carlbro.dk...
>
"Planning" a database enviroment - a bit off topic
Hi
Hi
I'm about to plan a upgrade to our database enviroment.
We have 7 database servers running different kinds of SQL server (7.0,
2000, 2005) in an unmanaged enviroment,
The environment services wide range of business applikations and the demand
for availabilitiy and security is increasing and the environment should meet
the operational requirements led out from/required to ITIL best practice
principles.
What i'm looking for at resources including frameworks/toolkits,
guides/recommendations, consideration/decision for planning a database
enviroment that meets these requirements (i.e. the process upgrade/migrate
from A to B).
I've been searching the net for this but I can't seem to find any resources
that takes up this matter, so any help appriciated-
-KimKim,
Try:
http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1033&spid=2852&query=INF+upgrade&adv=&ast=1%2C2%2C3&mode=a&cat=False&kt=ALL&title=false&mdt=&pwt=False&res=20&1033comm=1
and
http://www.microsoft.com/sql/solutions/upgrade/default.mspx
HTH
Jerry
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:446d06c5$1@.news.carlbro.dk...
> Hi
> Hi
> I'm about to plan a upgrade to our database enviroment.
> We have 7 database servers running different kinds of SQL server (7.0,
> 2000, 2005) in an unmanaged enviroment,
> The environment services wide range of business applikations and the
> demand for availabilitiy and security is increasing and the environment
> should meet the operational requirements led out from/required to ITIL
> best practice principles.
> What i'm looking for at resources including frameworks/toolkits,
> guides/recommendations, consideration/decision for planning a database
> enviroment that meets these requirements (i.e. the process upgrade/migrate
> from A to B).
> I've been searching the net for this but I can't seem to find any
> resources that takes up this matter, so any help appriciated-
> -Kim
>|||Thanks, I found some good articles.
I still need some resources that takes a more holistic view of the process,
ie. not just how I plan to upgrade a certain databaseserver, but also how my
database environment should be build.
By environment I mean all the database servers and applications (and other
components) that communicate with the environment, that is, I need som
Architechtual design considerations for the _whole_ environment, including
operations, support etc. etc.
-Kim
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e66rzgseGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Kim,
> Try:
> http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1033&spid=2852&query=INF+upgrade&adv=&ast=1%2C2%2C3&mode=a&cat=False&kt=ALL&title=false&mdt=&pwt=False&res=20&1033comm=1
> and
> http://www.microsoft.com/sql/solutions/upgrade/default.mspx
> HTH
> Jerry
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:446d06c5$1@.news.carlbro.dk...
>> Hi
>> Hi
>> I'm about to plan a upgrade to our database enviroment.
>> We have 7 database servers running different kinds of SQL server (7.0,
>> 2000, 2005) in an unmanaged enviroment,
>> The environment services wide range of business applikations and the
>> demand for availabilitiy and security is increasing and the environment
>> should meet the operational requirements led out from/required to ITIL
>> best practice principles.
>> What i'm looking for at resources including frameworks/toolkits,
>> guides/recommendations, consideration/decision for planning a database
>> enviroment that meets these requirements (i.e. the process
>> upgrade/migrate from A to B).
>> I've been searching the net for this but I can't seem to find any
>> resources that takes up this matter, so any help appriciated-
>> -Kim
>>
>
Hi
I'm about to plan a upgrade to our database enviroment.
We have 7 database servers running different kinds of SQL server (7.0,
2000, 2005) in an unmanaged enviroment,
The environment services wide range of business applikations and the demand
for availabilitiy and security is increasing and the environment should meet
the operational requirements led out from/required to ITIL best practice
principles.
What i'm looking for at resources including frameworks/toolkits,
guides/recommendations, consideration/decision for planning a database
enviroment that meets these requirements (i.e. the process upgrade/migrate
from A to B).
I've been searching the net for this but I can't seem to find any resources
that takes up this matter, so any help appriciated-
-KimKim,
Try:
http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1033&spid=2852&query=INF+upgrade&adv=&ast=1%2C2%2C3&mode=a&cat=False&kt=ALL&title=false&mdt=&pwt=False&res=20&1033comm=1
and
http://www.microsoft.com/sql/solutions/upgrade/default.mspx
HTH
Jerry
"Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
news:446d06c5$1@.news.carlbro.dk...
> Hi
> Hi
> I'm about to plan a upgrade to our database enviroment.
> We have 7 database servers running different kinds of SQL server (7.0,
> 2000, 2005) in an unmanaged enviroment,
> The environment services wide range of business applikations and the
> demand for availabilitiy and security is increasing and the environment
> should meet the operational requirements led out from/required to ITIL
> best practice principles.
> What i'm looking for at resources including frameworks/toolkits,
> guides/recommendations, consideration/decision for planning a database
> enviroment that meets these requirements (i.e. the process upgrade/migrate
> from A to B).
> I've been searching the net for this but I can't seem to find any
> resources that takes up this matter, so any help appriciated-
> -Kim
>|||Thanks, I found some good articles.
I still need some resources that takes a more holistic view of the process,
ie. not just how I plan to upgrade a certain databaseserver, but also how my
database environment should be build.
By environment I mean all the database servers and applications (and other
components) that communicate with the environment, that is, I need som
Architechtual design considerations for the _whole_ environment, including
operations, support etc. etc.
-Kim
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e66rzgseGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Kim,
> Try:
> http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1033&spid=2852&query=INF+upgrade&adv=&ast=1%2C2%2C3&mode=a&cat=False&kt=ALL&title=false&mdt=&pwt=False&res=20&1033comm=1
> and
> http://www.microsoft.com/sql/solutions/upgrade/default.mspx
> HTH
> Jerry
> "Kim" <webdrift_nospam_@.carlbro.dk> wrote in message
> news:446d06c5$1@.news.carlbro.dk...
>> Hi
>> Hi
>> I'm about to plan a upgrade to our database enviroment.
>> We have 7 database servers running different kinds of SQL server (7.0,
>> 2000, 2005) in an unmanaged enviroment,
>> The environment services wide range of business applikations and the
>> demand for availabilitiy and security is increasing and the environment
>> should meet the operational requirements led out from/required to ITIL
>> best practice principles.
>> What i'm looking for at resources including frameworks/toolkits,
>> guides/recommendations, consideration/decision for planning a database
>> enviroment that meets these requirements (i.e. the process
>> upgrade/migrate from A to B).
>> I've been searching the net for this but I can't seem to find any
>> resources that takes up this matter, so any help appriciated-
>> -Kim
>>
>
Tuesday, March 6, 2012
"Order by" error running DBCC DBREINDEX
I'm running a simple DBCC DBREINDEX ('myTable') and I receive the
following error:
following error:
"Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list.
Columns in the order by list must be unique. DBCC execution
completed. If DBCC printed error messages, contact your system
administrator."
I can successfully reindex other tables in this database. I thought
that perhaps I had objects in the database that ended up with the same
name, but I've pretty much ruled that out.
Any suggestions?
Thanks
John D. Morris
mailto://jmorris_42@.hotmail.comjmorris_42@.hotmail.com (John Morris) wrote in message news:<5b4367be.0308050426.10612a9f@.posting.google.com>...
> I'm running a simple DBCC DBREINDEX ('myTable') and I receive the
> following error:
> "Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique. DBCC execution
> completed. If DBCC printed error messages, contact your system
> administrator."
> I can successfully reindex other tables in this database. I thought
> that perhaps I had objects in the database that ended up with the same
> name, but I've pretty much ruled that out.
> Any suggestions?
> Thanks
> John D. Morris
> mailto://jmorris_42@.hotmail.com
Followup:
As it turns out, I had a hidden "Hypothetical" index that the index
tuning wizard decided not to clean up when it was done. The only way
to see the index was in sp_helpindex. It did not appear in the object
browser. MS claims this was fixed in SP1...I guess not. Anyway,
dropped the hypothetical index and all is well.
-jdm|||John,
Can you consistently repro the bug?
--
Arun Marathe
Microsoft Corporation
Disclaimers:
(1) This posting is provided "AS IS" with no warranties, and confers no
rights.
(2) Use of included script samples are subject to the terms specified at:
http://www.microsoft.com/info/cpyright.htm
"John D. Morris" <jmorris@.sni.org> wrote in message
news:d034511a.0308051015.60cc849a@.posting.google.c om...
> jmorris_42@.hotmail.com (John Morris) wrote in message
news:<5b4367be.0308050426.10612a9f@.posting.google.com>...
> > I'm running a simple DBCC DBREINDEX ('myTable') and I receive the
> > following error:
> > "Server: Msg 169, Level 15, State 2, Line 2
> > A column has been specified more than once in the order by list.
> > Columns in the order by list must be unique. DBCC execution
> > completed. If DBCC printed error messages, contact your system
> > administrator."
> > I can successfully reindex other tables in this database. I thought
> > that perhaps I had objects in the database that ended up with the same
> > name, but I've pretty much ruled that out.
> > Any suggestions?
> > Thanks
> > John D. Morris
> > mailto://jmorris_42@.hotmail.com
> Followup:
> As it turns out, I had a hidden "Hypothetical" index that the index
> tuning wizard decided not to clean up when it was done. The only way
> to see the index was in sp_helpindex. It did not appear in the object
> browser. MS claims this was fixed in SP1...I guess not. Anyway,
> dropped the hypothetical index and all is well.
> -jdm
Saturday, February 25, 2012
"New transaction is not allowed because there are other threads running in the session." -
That is a SqlException I got at a...
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
Anyone an idea what THAT means? How do I cause it? How can I work around it?
I think you probably have an open connection somewhere. See if you can start the transaction after you close the connection.|||I have about 25 open connections. Because that is - quess what - an ASP.NET application.
Once connection per page.
And every page has - one thread. No multi threading at all.
Plus it is legal for a thread to have multiple open connections.
|||Have you enabled Multiple Active Resultsets (MARS)?
For connections with MARS enabled, manual transactions started via TSQL BEGIN TRAN must end (COMMIT or ROLLBACK) within the same scope that started the transaction
If your issue meets the the aboving conditions, you can try this work aroud:
To begin tansactions that are not required to be scoped under
MARS-enabled connections, use the client driver API constructs to begin and end transactions. If existing code relies on BEGIN TRAN operations without
corresponding COMMIT/ROLLBACK, MARS can be turned off until the application can be modified to use the client driver transaction APIs
"Maximum Memory" setting in SQL Server properties
I have inherrited a sql 2k standard server running Win2K standard Ed with 3.5
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and has
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> > I have inherrited a sql 2k standard server running Win2K standard Ed with
> 3.5
> > gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
> has
> > 4 CPUs.
> >
> > The "maximum memory" setting is set to 3500 MB and SQL is using around
> 1.75
> > GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
> inputs
> > on if the "maximum memory" setting should be left to 3500mb or should it
> be
> > reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> > transaction log should not be on a raid 5 for performance reasons and I
> know
> > that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
> of
> > RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> > considered very high.
> >
> > Any inputs would be appreciated on this setting while we order the SQL2K
> > Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> > drives...
> >
> >
>
>|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > SQL Server Standard Edition is limited to 2GB of RAM. If you have
more, it
> > will not use it. You need to install SQL Server Expertise Edition to
use
> > more RAM (and set /3GB switch in your boot.ini). SQL Server knows it
must
> > leave space for the OS, so the .25 is left for the OS in the sub 2GB
memory
> > area.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> > news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> > > I have inherrited a sql 2k standard server running Win2K standard Ed
with
> > 3.5
> > > gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL
only and
> > has
> > > 4 CPUs.
> > >
> > > The "maximum memory" setting is set to 3500 MB and SQL is using
around
> > 1.75
> > > GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB).
Any
> > inputs
> > > on if the "maximum memory" setting should be left to 3500mb or
should it
> > be
> > > reduced to 2000 MB so SQL uses only the physical ram? I am aware
that the
> > > transaction log should not be on a raid 5 for performance reasons
and I
> > know
> > > that SQL 2k Standard + Win2k standard will not let me use more than
2 GB
> > of
> > > RAM for SQL. The average Physical Disk (_total) activity is 56%
which is
> > > considered very high.
> > >
> > > Any inputs would be appreciated on this setting while we order the
SQL2K
> > > Enterprise on Win2K Advanced Ed. and add an extra controller and 15
RPM
> > > drives...
> > >
> > >
> >
> >
> >
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and has
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> > I have inherrited a sql 2k standard server running Win2K standard Ed with
> 3.5
> > gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
> has
> > 4 CPUs.
> >
> > The "maximum memory" setting is set to 3500 MB and SQL is using around
> 1.75
> > GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
> inputs
> > on if the "maximum memory" setting should be left to 3500mb or should it
> be
> > reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> > transaction log should not be on a raid 5 for performance reasons and I
> know
> > that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
> of
> > RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> > considered very high.
> >
> > Any inputs would be appreciated on this setting while we order the SQL2K
> > Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> > drives...
> >
> >
>
>|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > SQL Server Standard Edition is limited to 2GB of RAM. If you have
more, it
> > will not use it. You need to install SQL Server Expertise Edition to
use
> > more RAM (and set /3GB switch in your boot.ini). SQL Server knows it
must
> > leave space for the OS, so the .25 is left for the OS in the sub 2GB
memory
> > area.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> > news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> > > I have inherrited a sql 2k standard server running Win2K standard Ed
with
> > 3.5
> > > gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL
only and
> > has
> > > 4 CPUs.
> > >
> > > The "maximum memory" setting is set to 3500 MB and SQL is using
around
> > 1.75
> > > GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB).
Any
> > inputs
> > > on if the "maximum memory" setting should be left to 3500mb or
should it
> > be
> > > reduced to 2000 MB so SQL uses only the physical ram? I am aware
that the
> > > transaction log should not be on a raid 5 for performance reasons
and I
> > know
> > > that SQL 2k Standard + Win2k standard will not let me use more than
2 GB
> > of
> > > RAM for SQL. The average Physical Disk (_total) activity is 56%
which is
> > > considered very high.
> > >
> > > Any inputs would be appreciated on this setting while we order the
SQL2K
> > > Enterprise on Win2K Advanced Ed. and add an extra controller and 15
RPM
> > > drives...
> > >
> > >
> >
> >
> >
"Maximum Memory" setting in SQL Server properties
I have inherrited a sql 2k standard server running Win2K standard Ed with 3.5
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and has
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...
Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>
|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> 3.5
> has
> 1.75
> inputs
> be
> know
> of
>
>
|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
[vbcol=seagreen]
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
more, it[vbcol=seagreen]
use[vbcol=seagreen]
must[vbcol=seagreen]
memory[vbcol=seagreen]
with[vbcol=seagreen]
only and[vbcol=seagreen]
around[vbcol=seagreen]
Any[vbcol=seagreen]
should it[vbcol=seagreen]
that the[vbcol=seagreen]
and I[vbcol=seagreen]
2 GB[vbcol=seagreen]
which is[vbcol=seagreen]
SQL2K[vbcol=seagreen]
RPM[vbcol=seagreen]
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and has
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...
Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>
|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> 3.5
> has
> 1.75
> inputs
> be
> know
> of
>
>
|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
[vbcol=seagreen]
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
more, it[vbcol=seagreen]
use[vbcol=seagreen]
must[vbcol=seagreen]
memory[vbcol=seagreen]
with[vbcol=seagreen]
only and[vbcol=seagreen]
around[vbcol=seagreen]
Any[vbcol=seagreen]
should it[vbcol=seagreen]
that the[vbcol=seagreen]
and I[vbcol=seagreen]
2 GB[vbcol=seagreen]
which is[vbcol=seagreen]
SQL2K[vbcol=seagreen]
RPM[vbcol=seagreen]
"Maximum Memory" setting in SQL Server properties
I have inherrited a sql 2k standard server running Win2K standard Ed with 3.
5
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and ha
s
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memor
y
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> 3.5
> has
> 1.75
> inputs
> be
> know
> of
>
>|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
[vbcol=seagreen]
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
>
more, it[vbcol=seagreen]
use[vbcol=seagreen]
must[vbcol=seagreen]
memory[vbcol=seagreen]
with[vbcol=seagreen]
only and[vbcol=seagreen]
around[vbcol=seagreen]
Any[vbcol=seagreen]
should it[vbcol=seagreen]
that the[vbcol=seagreen]
and I[vbcol=seagreen]
2 GB[vbcol=seagreen]
which is[vbcol=seagreen]
SQL2K[vbcol=seagreen]
RPM[vbcol=seagreen]
5
gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and ha
s
4 CPUs.
The "maximum memory" setting is set to 3500 MB and SQL is using around 1.75
GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any inputs
on if the "maximum memory" setting should be left to 3500mb or should it be
reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
transaction log should not be on a raid 5 for performance reasons and I know
that SQL 2k Standard + Win2k standard will not let me use more than 2 GB of
RAM for SQL. The average Physical Disk (_total) activity is 56% which is
considered very high.
Any inputs would be appreciated on this setting while we order the SQL2K
Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
drives...Hi
SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
will not use it. You need to install SQL Server Expertise Edition to use
more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
leave space for the OS, so the .25 is left for the OS in the sub 2GB memory
area.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> I have inherrited a sql 2k standard server running Win2K standard Ed with
3.5
> gb RAM (!?!) on a raid 5 (!?!). The server is dedicated for SQL only and
has
> 4 CPUs.
> The "maximum memory" setting is set to 3500 MB and SQL is using around
1.75
> GB of physical ram and 1.75 GB of Virtual Memory (total:3500 MB). Any
inputs
> on if the "maximum memory" setting should be left to 3500mb or should it
be
> reduced to 2000 MB so SQL uses only the physical ram? I am aware that the
> transaction log should not be on a raid 5 for performance reasons and I
know
> that SQL 2k Standard + Win2k standard will not let me use more than 2 GB
of
> RAM for SQL. The average Physical Disk (_total) activity is 56% which is
> considered very high.
> Any inputs would be appreciated on this setting while we order the SQL2K
> Enterprise on Win2K Advanced Ed. and add an extra controller and 15 RPM
> drives...
>|||Thanks for your reply.
I fully understand what you are saying (as it was also statement in my
request). I am interested to know about he virtual memory portion of it.
Thanks again
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server Standard Edition is limited to 2GB of RAM. If you have more, it
> will not use it. You need to install SQL Server Expertise Edition to use
> more RAM (and set /3GB switch in your boot.ini). SQL Server knows it must
> leave space for the OS, so the .25 is left for the OS in the sub 2GB memor
y
> area.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:E5260014-1A9E-47AF-9247-0F8F5B25F8DF@.microsoft.com...
> 3.5
> has
> 1.75
> inputs
> be
> know
> of
>
>|||"Anonymous" <Anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1D46A3CC-2D84-4FAA-AA97-24DDA8A27443@.microsoft.com...
> Thanks for your reply.
> I fully understand what you are saying (as it was also statement in my
> request). I am interested to know about he virtual memory portion of it.
From the application's point of view physical and virtual memory are just
the same. You must not sum them up. Physical mem in TaskMGR shows the
portion of the application that's actually in main memory while virtual
mem shows the total allocated size. Your case is optimal with regard to
swapping: the process is completely in physical mem.
As Mike said, your SQL Server will never use more than what it uses at the
moment unless you install enterprise edition.
You should use perfmon to determine what causes the high disk activity.
Regards
robert
[vbcol=seagreen]
> Thanks again
> "Mike Epprecht (SQL MVP)" wrote:
>
more, it[vbcol=seagreen]
use[vbcol=seagreen]
must[vbcol=seagreen]
memory[vbcol=seagreen]
with[vbcol=seagreen]
only and[vbcol=seagreen]
around[vbcol=seagreen]
Any[vbcol=seagreen]
should it[vbcol=seagreen]
that the[vbcol=seagreen]
and I[vbcol=seagreen]
2 GB[vbcol=seagreen]
which is[vbcol=seagreen]
SQL2K[vbcol=seagreen]
RPM[vbcol=seagreen]
"Lock Pages in Memory" Service Account and SQL Agent
Microsoft recommends using the Lock Pages in Memory privilege for SQl
Server 2005 x64 running on Windows Server 2003 R2 x64.
If granting this privilege allows this user to Lock Pages in Memory,
should SQL Agent be changed to use a different service account that
does not have this privilege? (both SQL and Agent use the same account
currently)
I am thinking that if both SQL Server and SQL Agent are running under
a account with this privilege if they will conflict. Is this the case?Perhaps you are talking about this on BOL
"Although it is not required, we recommend locking pages in memory when
using 64-bit operating systems. For 32-bit operating systems, Lock pages in
memory permission must be granted before AWE is configured for SQL Server."
I do not see why there could be a conflict if both services are using the
same Windows account and this account has the Lock pages in memory permission
granted. It should be Ok.
(BOL note from
Enabling Memory Support for Over 4 GB of Physical Memory
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-0a0a440eb443.htm
)
Hope this helps,
Ben Nevarez
"Neufusion" wrote:
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?
>|||The main SQL Server service is specifically designed for managing large
amounts of memory and has an option to use AWE but the SQL Agent only works
like any other regular program as far as memory management's concerned
(Virtual Memory). Therefore there's no reason to think both services would
make large AWE allocations at startup.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Neufusion" <mikeymiller@.gmail.com> wrote in message
news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?|||If I remember correctly, the "Lock pages in memory" setting only applies to
Enterprise, not Standard edition. not sure which you are running
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Neufusion" <mikeymiller@.gmail.com> wrote in message
news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?|||This was true for SQL 2000 but not SQL 2005
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:Oh8WQgnYIHA.3964@.TK2MSFTNGP03.phx.gbl...
> If I remember correctly, the "Lock pages in memory" setting only applies
> to Enterprise, not Standard edition. not sure which you are running
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Neufusion" <mikeymiller@.gmail.com> wrote in message
> news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
>> Microsoft recommends using the Lock Pages in Memory privilege for SQl
>> Server 2005 x64 running on Windows Server 2003 R2 x64.
>> If granting this privilege allows this user to Lock Pages in Memory,
>> should SQL Agent be changed to use a different service account that
>> does not have this privilege? (both SQL and Agent use the same account
>> currently)
>> I am thinking that if both SQL Server and SQL Agent are running under
>> a account with this privilege if they will conflict. Is this the case?
>|||http://support.microsoft.com/kb/918483/en-us
Note For 64-bit systems, SQL Server 2005 Enterprise Edition is the only
edition that is designed to use lock pages in memory.
Am I misreading? I have a 3 node, 2 instance cluster my customer is about
to upgrade for this very reason...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:utExsLrYIHA.4196@.TK2MSFTNGP04.phx.gbl...
> This was true for SQL 2000 but not SQL 2005
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:Oh8WQgnYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> If I remember correctly, the "Lock pages in memory" setting only applies
>> to Enterprise, not Standard edition. not sure which you are running
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Neufusion" <mikeymiller@.gmail.com> wrote in message
>> news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
>> Microsoft recommends using the Lock Pages in Memory privilege for SQl
>> Server 2005 x64 running on Windows Server 2003 R2 x64.
>> If granting this privilege allows this user to Lock Pages in Memory,
>> should SQL Agent be changed to use a different service account that
>> does not have this privilege? (both SQL and Agent use the same account
>> currently)
>> I am thinking that if both SQL Server and SQL Agent are running under
>> a account with this privilege if they will conflict. Is this the case?
>>
>
Server 2005 x64 running on Windows Server 2003 R2 x64.
If granting this privilege allows this user to Lock Pages in Memory,
should SQL Agent be changed to use a different service account that
does not have this privilege? (both SQL and Agent use the same account
currently)
I am thinking that if both SQL Server and SQL Agent are running under
a account with this privilege if they will conflict. Is this the case?Perhaps you are talking about this on BOL
"Although it is not required, we recommend locking pages in memory when
using 64-bit operating systems. For 32-bit operating systems, Lock pages in
memory permission must be granted before AWE is configured for SQL Server."
I do not see why there could be a conflict if both services are using the
same Windows account and this account has the Lock pages in memory permission
granted. It should be Ok.
(BOL note from
Enabling Memory Support for Over 4 GB of Physical Memory
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/34950321-2bfd-4679-8f1b-0a0a440eb443.htm
)
Hope this helps,
Ben Nevarez
"Neufusion" wrote:
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?
>|||The main SQL Server service is specifically designed for managing large
amounts of memory and has an option to use AWE but the SQL Agent only works
like any other regular program as far as memory management's concerned
(Virtual Memory). Therefore there's no reason to think both services would
make large AWE allocations at startup.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Neufusion" <mikeymiller@.gmail.com> wrote in message
news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?|||If I remember correctly, the "Lock pages in memory" setting only applies to
Enterprise, not Standard edition. not sure which you are running
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Neufusion" <mikeymiller@.gmail.com> wrote in message
news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
> Microsoft recommends using the Lock Pages in Memory privilege for SQl
> Server 2005 x64 running on Windows Server 2003 R2 x64.
> If granting this privilege allows this user to Lock Pages in Memory,
> should SQL Agent be changed to use a different service account that
> does not have this privilege? (both SQL and Agent use the same account
> currently)
> I am thinking that if both SQL Server and SQL Agent are running under
> a account with this privilege if they will conflict. Is this the case?|||This was true for SQL 2000 but not SQL 2005
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:Oh8WQgnYIHA.3964@.TK2MSFTNGP03.phx.gbl...
> If I remember correctly, the "Lock pages in memory" setting only applies
> to Enterprise, not Standard edition. not sure which you are running
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Neufusion" <mikeymiller@.gmail.com> wrote in message
> news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
>> Microsoft recommends using the Lock Pages in Memory privilege for SQl
>> Server 2005 x64 running on Windows Server 2003 R2 x64.
>> If granting this privilege allows this user to Lock Pages in Memory,
>> should SQL Agent be changed to use a different service account that
>> does not have this privilege? (both SQL and Agent use the same account
>> currently)
>> I am thinking that if both SQL Server and SQL Agent are running under
>> a account with this privilege if they will conflict. Is this the case?
>|||http://support.microsoft.com/kb/918483/en-us
Note For 64-bit systems, SQL Server 2005 Enterprise Edition is the only
edition that is designed to use lock pages in memory.
Am I misreading? I have a 3 node, 2 instance cluster my customer is about
to upgrade for this very reason...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:utExsLrYIHA.4196@.TK2MSFTNGP04.phx.gbl...
> This was true for SQL 2000 but not SQL 2005
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:Oh8WQgnYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> If I remember correctly, the "Lock pages in memory" setting only applies
>> to Enterprise, not Standard edition. not sure which you are running
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Neufusion" <mikeymiller@.gmail.com> wrote in message
>> news:9a4a6b46-f949-4c38-a277-9757134c0714@.d21g2000prg.googlegroups.com...
>> Microsoft recommends using the Lock Pages in Memory privilege for SQl
>> Server 2005 x64 running on Windows Server 2003 R2 x64.
>> If granting this privilege allows this user to Lock Pages in Memory,
>> should SQL Agent be changed to use a different service account that
>> does not have this privilege? (both SQL and Agent use the same account
>> currently)
>> I am thinking that if both SQL Server and SQL Agent are running under
>> a account with this privilege if they will conflict. Is this the case?
>>
>
Thursday, February 23, 2012
"Invalid Cursor Error"
Dear,
Does anyone have a clue when Microsoft is going to release it's next service
pack for SQL-Server? I'm running build 859 at this moment and I can't alter
any table anymore in my enterprise manager. ALTER TABLE is fine as long as
you don't wish to change a column into an identity column. I do not intend
to spend money on this by calling the support line, since I know what the
problem is and spending money on this seems absurd. There is no kb article
for that error as well(although it's is fixed in higher builds than 859) so
there is a big chance they don't want to give me a higher build because of
that (at least that is what appears from other articles). Anyone who can
help me? My development time reduces drastically and reinstalling isn't an
option!
yours sincerly
Michael
"Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Dear,
> Does anyone have a clue when Microsoft is going to release it's next
service
> pack for SQL-Server? I'm running build 859 at this moment and I can't
alter
> any table anymore in my enterprise manager. ALTER TABLE is fine as long as
> you don't wish to change a column into an identity column. I do not intend
> to spend money on this by calling the support line, since I know what the
> problem is and spending money on this seems absurd. There is no kb article
1) If they can't fix that problem, you dont have to spend money, they'll pay
that back.
2) What's your code? Do you use ADO or something like that?
I can tell you that *no* alter table /alter column statement supports that
natively..
--does *not* work
ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
Therefore, I think that the 'cursor error' is raised because some
object/component that you use, did not test this possibility and now it bugs
out...
3) Having the latest ODBC / oledb drivers might help.
Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so on...
4)
this below works...
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
nID int NOT NULL IDENTITY (1, 1),
adfs char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
nID
) ON [PRIMARY]
GO
COMMIT
> for that error as well(although it's is fixed in higher builds than 859)
so
> there is a big chance they don't want to give me a higher build because of
> that (at least that is what appears from other articles). Anyone who can
> help me? My development time reduces drastically and reinstalling isn't an
> option!
> yours sincerly
> Michael
>
|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> wrote in
message news:#BisWlsNEHA.1276@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
> news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> service
> alter
as[vbcol=seagreen]
intend[vbcol=seagreen]
the[vbcol=seagreen]
article
> 1) If they can't fix that problem, you dont have to spend money, they'll
pay
> that back.
>
Well they were nice and are going to send me the patch. I was a bit
frustrated but if they send me the fix, my problems should be over
. But a
few nightly hours searching can drive you mad every now and then; :-D
> 2) What's your code? Do you use ADO or something like that?
>
Just plain simple Enterprise Manager. I tried to work around it with the web
data administrator but that didn't work either.
> I can tell you that *no* alter table /alter column statement supports that
> natively..
> --does *not* work
> ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
> Therefore, I think that the 'cursor error' is raised because some
> object/component that you use, did not test this possibility and now it
bugs
> out...
>
Yep that's what I figured out as well. Enterprise Manager generates some
extra statements to support that I've read somewhere.
> 3) Having the latest ODBC / oledb drivers might help.
> Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so
on...
>
I will check that...
> 4)
> this below works...
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> nID int NOT NULL IDENTITY (1, 1),
> adfs char(10) NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 ON
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
> SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> ALTER TABLE dbo.Table1 ADD CONSTRAINT
> PK_Table1 PRIMARY KEY CLUSTERED
> (
> nID
> ) ON [PRIMARY]
> GO
> COMMIT
>
I will try that as well. It's always usefull to have such workarounds.
Tnx a lot 4 your time.
yours sincerly
Michael
Does anyone have a clue when Microsoft is going to release it's next service
pack for SQL-Server? I'm running build 859 at this moment and I can't alter
any table anymore in my enterprise manager. ALTER TABLE is fine as long as
you don't wish to change a column into an identity column. I do not intend
to spend money on this by calling the support line, since I know what the
problem is and spending money on this seems absurd. There is no kb article
for that error as well(although it's is fixed in higher builds than 859) so
there is a big chance they don't want to give me a higher build because of
that (at least that is what appears from other articles). Anyone who can
help me? My development time reduces drastically and reinstalling isn't an
option!
yours sincerly
Michael
"Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Dear,
> Does anyone have a clue when Microsoft is going to release it's next
service
> pack for SQL-Server? I'm running build 859 at this moment and I can't
alter
> any table anymore in my enterprise manager. ALTER TABLE is fine as long as
> you don't wish to change a column into an identity column. I do not intend
> to spend money on this by calling the support line, since I know what the
> problem is and spending money on this seems absurd. There is no kb article
1) If they can't fix that problem, you dont have to spend money, they'll pay
that back.
2) What's your code? Do you use ADO or something like that?
I can tell you that *no* alter table /alter column statement supports that
natively..
--does *not* work
ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
Therefore, I think that the 'cursor error' is raised because some
object/component that you use, did not test this possibility and now it bugs
out...
3) Having the latest ODBC / oledb drivers might help.
Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so on...
4)
this below works...
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
nID int NOT NULL IDENTITY (1, 1),
adfs char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
nID
) ON [PRIMARY]
GO
COMMIT
> for that error as well(although it's is fixed in higher builds than 859)
so
> there is a big chance they don't want to give me a higher build because of
> that (at least that is what appears from other articles). Anyone who can
> help me? My development time reduces drastically and reinstalling isn't an
> option!
> yours sincerly
> Michael
>
|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.invalid> wrote in
message news:#BisWlsNEHA.1276@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Michael Gaillez" <michael.gaillez@.howest.be> wrote in message
> news:%23Xuj6WnNEHA.1348@.TK2MSFTNGP12.phx.gbl...
> service
> alter
as[vbcol=seagreen]
intend[vbcol=seagreen]
the[vbcol=seagreen]
article
> 1) If they can't fix that problem, you dont have to spend money, they'll
pay
> that back.
>
Well they were nice and are going to send me the patch. I was a bit
frustrated but if they send me the fix, my problems should be over
. But afew nightly hours searching can drive you mad every now and then; :-D
> 2) What's your code? Do you use ADO or something like that?
>
Just plain simple Enterprise Manager. I tried to work around it with the web
data administrator but that didn't work either.
> I can tell you that *no* alter table /alter column statement supports that
> natively..
> --does *not* work
> ALTER TABLE table1 ALTER COLUMN nid ADD IDENTITY (1, 1)
> Therefore, I think that the 'cursor error' is raised because some
> object/component that you use, did not test this possibility and now it
bugs
> out...
>
Yep that's what I figured out as well. Enterprise Manager generates some
extra statements to support that I've read somewhere.
> 3) Having the latest ODBC / oledb drivers might help.
> Mine is 3.525.1022.0 (odbc32.dll) 2000.85.1022.0(sqloledb.dll) and so
on...
>
I will check that...
> 4)
> this below works...
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> nID int NOT NULL IDENTITY (1, 1),
> adfs char(10) NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 ON
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (nID, adfs)
> SELECT nID, adfs FROM dbo.Table1 TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> ALTER TABLE dbo.Table1 ADD CONSTRAINT
> PK_Table1 PRIMARY KEY CLUSTERED
> (
> nID
> ) ON [PRIMARY]
> GO
> COMMIT
>
I will try that as well. It's always usefull to have such workarounds.
Tnx a lot 4 your time.
yours sincerly
Michael
Subscribe to:
Posts (Atom)
