Monday, March 26, 2012
MSRepl_Commands
"query MSDistribution_status in your distribution database - this will help
you to understand where the problem is
Otherwise run the distribution clean up agent."
Distribution Agent doesn't appear to be clearing these old transactions.
Is there a way to clean out the old Undelivered commands that I told the Agent to skip/ignore?
Any suggestions on how I can get MSRepl_Commands table back under 3 million rows?
Thanx!
JLS
A quick and dirty way to do this is to drop the subscriptions and then
recreate them using a nosync subscription.
You can also manually delete them if you are very careful and know which
ones to remove. If you are using anonymous subscriptions they will hang
around longer than if you are using named subscriptions. Can you convert
them to named subscriptions?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%231mF$ZW4FHA.400@.TK2MSFTNGP09.phx.gbl...
I followed your advise Hilary,
"query MSDistribution_status in your distribution database - this will help
you to understand where the problem is
Otherwise run the distribution clean up agent."
Distribution Agent doesn't appear to be clearing these old transactions.
Is there a way to clean out the old Undelivered commands that I told the
Agent to skip/ignore?
Any suggestions on how I can get MSRepl_Commands table back under 3 million
rows?
Thanx!
JLS
|||I don't use anonymous subscriptions.
What is the harm in deleting from msrepl_commands?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uToQ7yb4FHA.3948@.TK2MSFTNGP09.phx.gbl...
A quick and dirty way to do this is to drop the subscriptions and then
recreate them using a nosync subscription.
You can also manually delete them if you are very careful and know which
ones to remove. If you are using anonymous subscriptions they will hang
around longer than if you are using named subscriptions. Can you convert
them to named subscriptions?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%231mF$ZW4FHA.400@.TK2MSFTNGP09.phx.gbl...
I followed your advise Hilary,
"query MSDistribution_status in your distribution database - this will help
you to understand where the problem is
Otherwise run the distribution clean up agent."
Distribution Agent doesn't appear to be clearing these old transactions.
Is there a way to clean out the old Undelivered commands that I told the
Agent to skip/ignore?
Any suggestions on how I can get MSRepl_Commands table back under 3 million
rows?
Thanx!
JLS
Friday, March 23, 2012
MSOLAP Error. Please help!
Error when tried once -
OLE DB provider 'MSOLAP' reported an error. The provider ran out of memory.
[OLE/DB provider returned message: An error occurred during attempt to get a cell value.]
[OLE/DB provider returned message: The operation has failed because of an error in the COM component - unknown error ]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IRowset::GetNextRows returned 0x8007000e: The provider ran out of memory.].
The second time I received -
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 77 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: An unexpected internal error has occurred.] [Microsoft][ODBC SQL Server Driver]Function sequence error
Thanks,
SauravRevisiting archived thread here: http://www.dbforums.com/archive/index.php/t-534601.html
Wednesday, March 21, 2012
MSmerge_agents table
Pull subscription with MSDE.
The subscribers need info on replication status.
I query MSmerge_history and MSmerge_agents with a join on agent ID.
I would like to filter by subscriber_name, but this field is empty for each
subscriber.
I've tried to drop a pull subsriptions many times. But this field stays
empty.
The field subscriber_id has a correct value and everything with replication
looks OK.
I think this should be the only one table with both fields subscriber_id and
subscriber_name together. So maybe the only way to know the ID of the
subscriber with his name.
1. Do I have another way to know pair subscriber_id <-> subscriber_name. So
I don't filter with subscriber_name, but with subscriber_id.
2. Can I without risk to update manually MSmerge_agents table after pull
subscription to fill correctly subscriber_name.
3. I've made something wrong during merge replication installation?
4. Bug (I can correct) ...
Thanks for help
Olivier
May be I was not so clear... Shorter version:
Is it normal to have null value in field subscriber_name
(distribution-MS_merge_agents) ?
Can I manually enter the correct value? I'm not used to do changes in system
tables without help of SP.
Thanks for help
Olivier
sql
Msg 9002, Level 17, State 4, Line 2
error:
I'm just running a Delete Query using Date Diff function. I read in some
web site that this maybe a problem with SQL Server Management Tools. I
appreciate any help to get around this and any suggestions would be great.
Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'EventSentry' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Thanks for answering.Fialoa,
Your delete transaction is writting too many log records and filling it up.
If you want to be able to recover then you need to make the log bigger or
make your delete statement work on a smaller subset.
Chris
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||Hi fiaola
SQL Server has to log every data modification operation, every insert,
update and delete. If the log is full, nothing can be written to it and no
data modifications can be done. This has nothing to do with the tool you are
using.
Just do what the messages says. Look at the sys.databases view and tell us
what the log_reuse_wait_desc says. Also look at the recovery model
SELECT log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'EventSentry'
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||Thanks for the quick reply, here are the results.
Log_Reuse_Wait_Desc = NOTHING
Recovery_Model_Desc = SIMPLE
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eMohs$WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Hi fiaola
> SQL Server has to log every data modification operation, every insert,
> update and delete. If the log is full, nothing can be written to it and no
> data modifications can be done. This has nothing to do with the tool you
> are using.
> Just do what the messages says. Look at the sys.databases view and tell us
> what the log_reuse_wait_desc says. Also look at the recovery model
> SELECT log_reuse_wait_desc, recovery_model_desc
> FROM sys.databases
> WHERE name = 'EventSentry'
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "fiaola" <fiaola@.mail.com> wrote in message
> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in some
>> web site that this maybe a problem with SQL Server Management Tools. I
>> appreciate any help to get around this and any suggestions would be
>> great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>|||It might just be that your log is not big enough for the operation you were
doing.
How big is your log file, and is it set to autogrow?
You can get the information in the sys.files view in the database that is
having the problems.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uH1ss$YUIHA.5816@.TK2MSFTNGP06.phx.gbl...
> Thanks for the quick reply, here are the results.
> Log_Reuse_Wait_Desc = NOTHING
> Recovery_Model_Desc = SIMPLE
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eMohs$WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Hi fiaola
>> SQL Server has to log every data modification operation, every insert,
>> update and delete. If the log is full, nothing can be written to it and
>> no data modifications can be done. This has nothing to do with the tool
>> you are using.
>> Just do what the messages says. Look at the sys.databases view and tell
>> us what the log_reuse_wait_desc says. Also look at the recovery model
>> SELECT log_reuse_wait_desc, recovery_model_desc
>> FROM sys.databases
>> WHERE name = 'EventSentry'
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||Modify your delete to do it in batches. Pick date ranges that will only
delete 1-5% of the table at once max for each range and then you will
probably be ok.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||But we still don't know how big the log is. I've seen logs that are set at
the default 1 MB with no autogrow.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o71gecmi9nmfb@.corp.supernews.com...
> Modify your delete to do it in batches. Pick date ranges that will only
> delete 1-5% of the table at once max for each range and then you will
> probably be ok.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "fiaola" <fiaola@.mail.com> wrote in message
> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in some
>> web site that this maybe a problem with SQL Server Management Tools. I
>> appreciate any help to get around this and any suggestions would be
>> great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>|||Problem fixed. Yes, i had to change the query to process less data and i
have set my logs to Autogrow for any future problems.
Thanks for your help. This is a GREAT news group with fast response.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
> But we still don't know how big the log is. I've seen logs that are set at
> the default 1 MB with no autogrow.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||Good point. I haven't seen tlogs set with no autogrow that I recall.
Almost every client I have been at still has the defaults for data/log size
and growths (2MB and 1MB growth for data and 1MB and 10% growth for tlog,
IIRC). Makes me look good when I have them defrag the hard drive and pick
up a big perf gain. :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
> But we still don't know how big the log is. I've seen logs that are set at
> the default 1 MB with no autogrow.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||You're welcome. I'm glad our suggestions helped.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:ubRniqiUIHA.536@.TK2MSFTNGP06.phx.gbl...
> Problem fixed. Yes, i had to change the query to process less data and i
> have set my logs to Autogrow for any future problems.
> Thanks for your help. This is a GREAT news group with fast response.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column
>> in sys.databases
>> Thanks for answering.
>>
>>
>>
>|||Kevin,
We also try and size our data and log files with limited growth. I have seen
problems with an unlimited log and percent full log alerts.
How much improvement would a disk defrag give you on todays raided disks?
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o7ttr3dli55fd@.corp.supernews.com...
> Good point. I haven't seen tlogs set with no autogrow that I recall.
> Almost every client I have been at still has the defaults for data/log
> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
> tlog, IIRC). Makes me look good when I have them defrag the hard drive
> and pick up a big perf gain. :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column
>> in sys.databases
>> Thanks for answering.
>>
>>
>>
>|||Limiting your log size is fine, but you should also make sure you have a
good idea of how big it needs to be and then add a generous percentage extra
before setting the upper limit.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Kevin,
> We also try and size our data and log files with limited growth. I have
> seen problems with an unlimited log and percent full log alerts.
> How much improvement would a disk defrag give you on todays raided disks?
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>|||Kalen,
We do that as best we can. We have alerts fired if the data file expands and
then we would look at the log size too. We also would get messages if the
log does get full.
My question is to see how much improvement we might get by defragging.
Thanks
Chris
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OAAg9h1UIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Limiting your log size is fine, but you should also make sure you have a
> good idea of how big it needs to be and then add a generous percentage
> extra before setting the upper limit.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>>
>|||I had a client with 300K+ file fragments when I arrived. 18% throughput
improvement from OS file defrag operation. It was on a small RAID10 set (6
drives IIRC).
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Kevin,
> We also try and size our data and log files with limited growth. I have
> seen problems with an unlimited log and percent full log alerts.
> How much improvement would a disk defrag give you on todays raided disks?
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>|||kevin,
What can we use to see how many file fragments there are? Running W2K3 SP2.
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13od5m2iv7ec365@.corp.supernews.com...
>I had a client with 300K+ file fragments when I arrived. 18% throughput
>improvement from OS file defrag operation. It was on a small RAID10 set (6
>drives IIRC).
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>>
>|||Hi Chris
Actually my comment was primarily directly at the OP. You've been around
long enough that I'm sure you've figured out most of the important stuff.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O9J8RU7UIHA.5264@.TK2MSFTNGP02.phx.gbl...
> Kalen,
> We do that as best we can. We have alerts fired if the data file expands
> and then we would look at the log size too. We also would get messages if
> the log does get full.
> My question is to see how much improvement we might get by defragging.
> Thanks
> Chris
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OAAg9h1UIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Limiting your log size is fine, but you should also make sure you have a
>> good idea of how big it needs to be and then add a generous percentage
>> extra before setting the upper limit.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read in
>>> some web site that this maybe a problem with SQL Server Management
>>> Tools. I appreciate any help to get around this and any suggestions
>>> would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>
>>
>>
>>
>>
>|||I would recommend diskkeeper. There are many others from symantec,
perfectdisk (raxco maybe?), mcaffee probably has one, one built into windows
(downgraded version of diskkeeper IIRC).
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
> kevin,
> What can we use to see how many file fragments there are? Running W2K3
> SP2.
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read in
>>> some web site that this maybe a problem with SQL Server Management
>>> Tools. I appreciate any help to get around this and any suggestions
>>> would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>
>>
>>
>>
>>
>|||Last question on this.
Can these products run in a scan mode when SQL is running so we have a
picture of the fragmentation? I am the DBA, the Infrastructure guys do the
Windows maintenance.
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13odvkmn08vcfa0@.corp.supernews.com...
>I would recommend diskkeeper. There are many others from symantec,
>perfectdisk (raxco maybe?), mcaffee probably has one, one built into
>windows (downgraded version of diskkeeper IIRC).
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
>> kevin,
>> What can we use to see how many file fragments there are? Running W2K3
>> SP2.
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>>> Modify your delete to do it in batches. Pick date ranges that will
>>> only delete 1-5% of the table at once max for each range and then
>>> you will probably be ok.
>>>
>>> --
>>> Kevin G. Boles
>>> Indicium Resources, Inc.
>>> SQL Server MVP
>>> kgboles a earthlink dt net
>>>
>>>
>>> "fiaola" <fiaola@.mail.com> wrote in message
>>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read
>>> in some web site that this maybe a problem with SQL Server
>>> Management Tools. I appreciate any help to get around this and any
>>> suggestions would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||I know for a fact that several of them can. I don't know about all of them.
BTW, It is recommended that you shut sql server down during a defrag
operation, but I have at least 2 clients using diskkeeper that do not do
this. Do so at your own risk though.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:uoeWXFHVIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Last question on this.
> Can these products run in a scan mode when SQL is running so we have a
> picture of the fragmentation? I am the DBA, the Infrastructure guys do the
> Windows maintenance.
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13odvkmn08vcfa0@.corp.supernews.com...
>>I would recommend diskkeeper. There are many others from symantec,
>>perfectdisk (raxco maybe?), mcaffee probably has one, one built into
>>windows (downgraded version of diskkeeper IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
>> kevin,
>> What can we use to see how many file fragments there are? Running W2K3
>> SP2.
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I
>> have seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for
>> data/log size and growths (2MB and 1MB growth for data and 1MB and
>> 10% growth for tlog, IIRC). Makes me look good when I have them
>> defrag the hard drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>>> But we still don't know how big the log is. I've seen logs that are
>>> set at the default 1 MB with no autogrow.
>>>
>>> --
>>> HTH
>>> Kalen Delaney, SQL Server MVP
>>> www.InsideSQLServer.com
>>> http://blog.kalendelaney.com
>>>
>>>
>>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>>> news:13o71gecmi9nmfb@.corp.supernews.com...
>>> Modify your delete to do it in batches. Pick date ranges that will
>>> only delete 1-5% of the table at once max for each range and then
>>> you will probably be ok.
>>>
>>> --
>>> Kevin G. Boles
>>> Indicium Resources, Inc.
>>> SQL Server MVP
>>> kgboles a earthlink dt net
>>>
>>>
>>> "fiaola" <fiaola@.mail.com> wrote in message
>>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read
>>> in some web site that this maybe a problem with SQL Server
>>> Management Tools. I appreciate any help to get around this and
>>> any suggestions would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find
>>> out why space in the log cannot be reused, see the
>>> log_reuse_wait_desc column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
Monday, March 19, 2012
Msg 8621: The query processor ran out of stack space during query optimization
I got the above error when I tried to execute the following query
delete from BUSINESS_ASSOCIATE
where ba_name is null
As you can see the query isn't exactly complicated.
What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.
So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.
-Jamie
anyone?
|||
Hi Jamie,
I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?
Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.
Cheers
Rob
|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
|||
Jamie Thomson wrote:
Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892
Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.
-Jamie
|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||
Umachandar Jayachandran - MS wrote:
This is not related to the memory allocated to SQL Server or any of those settings.
Thanks Uma. I was just about to go away and test that. Good job I checked here first
-Jamie
|||
It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.
Msg 8621: The query processor ran out of stack space during query optimization
I got the above error when I tried to execute the following query
delete from BUSINESS_ASSOCIATE
where ba_name is null
As you can see the query isn't exactly complicated.
What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.
So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.
-Jamie
anyone?
|||
Hi Jamie,
I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?
Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.
Cheers
Rob
|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
|||
Jamie Thomson wrote:
Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892
Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.
-Jamie
|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||
Umachandar Jayachandran - MS wrote:
This is not related to the memory allocated to SQL Server or any of those settings.
Thanks Uma. I was just about to go away and test that. Good job I checked here first
-Jamie
|||
It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.
msg 8152
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Can anyone please help? Thanks.Hi
It is happening because you try to insert a string value which is greater
then you have declared.
CREATE TABLE #Test
(
col VARCHAR(5)
)
INSERT INTO #Test VALUES ('AAAAAA')--6 characters
" -00Eric Clapton" <a@.b.com> wrote in message
news:Oz1KFHcuFHA.2948@.TK2MSFTNGP15.phx.gbl...
> When I try to run an insert query, I got the following error message:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
>
> Can anyone please help? Thanks.
>|||Hi Eric,
Thanks for the post.
For error message 8152, it usually means when the length of the value
entered by you into a char, varchar, nchar, nvarchar column is longer than
the maximum length of the column. For example, inserting 'FAQ' into a
char(2) column would result in this error.
You cou use Profiler to troubleshooting this error message. Tracing what
statement causes this.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
msg 8152
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Can anyone please help? Thanks.
Hi
It is happening because you try to insert a string value which is greater
then you have declared.
CREATE TABLE #Test
(
col VARCHAR(5)
)
INSERT INTO #Test VALUES ('AAAAAA')--6 characters
" -00Eric Clapton" <a@.b.com> wrote in message
news:Oz1KFHcuFHA.2948@.TK2MSFTNGP15.phx.gbl...
> When I try to run an insert query, I got the following error message:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
>
> Can anyone please help? Thanks.
>
|||Hi Eric,
Thanks for the post.
For error message 8152, it usually means when the length of the value
entered by you into a char, varchar, nchar, nvarchar column is longer than
the maximum length of the column. For example, inserting 'FAQ' into a
char(2) column would result in this error.
You cou use Profiler to troubleshooting this error message. Tracing what
statement causes this.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
msg 8152
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Can anyone please help? Thanks.Hi
It is happening because you try to insert a string value which is greater
then you have declared.
CREATE TABLE #Test
(
col VARCHAR(5)
)
INSERT INTO #Test VALUES ('AAAAAA')--6 characters
" -00Eric Clapton" <a@.b.com> wrote in message
news:Oz1KFHcuFHA.2948@.TK2MSFTNGP15.phx.gbl...
> When I try to run an insert query, I got the following error message:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
>
> Can anyone please help? Thanks.
>|||Hi Eric,
Thanks for the post.
For error message 8152, it usually means when the length of the value
entered by you into a char, varchar, nchar, nvarchar column is longer than
the maximum length of the column. For example, inserting 'FAQ' into a
char(2) column would result in this error.
You cou use Profiler to troubleshooting this error message. Tracing what
statement causes this.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Msg 7391 / JoinTransaction returned 0x8004d00a
boils down to this example.
This works in Query Analyzer:
--begin distributed transaction
insert into [39.80.0.26\x3].[sm].dbo.events
select * from events
where time = @.time and seq = @.seq
--commit
But it does not work in Query Analyzer when I uncomment the distributed
xaction and commit.
I get this error:
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I'm using sqlserver 8.0.818 on Win2003 server on both sides.
I tried several things to fix it, but to no avail:
@.@.servername matches the %computername% on each machine.
I enabled DTC Network Access using dcomcnfg.exe (all four options now
enabled). I then restarted DTC and sqlserver.
I verified DTC runs as NetworkService.
I even used telnet to verify the two machines can connect to each other's
port 135.
** WHAT HAVE I MISSED ? **
How do I get distributed transactions to work on these servers?
--MikeHi
have you checked out:
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
John
"Mike" wrote:
> I'm having trouble doing distributed transactions in a stored procedure.
It
> boils down to this example.
> This works in Query Analyzer:
> --begin distributed transaction
> insert into [39.80.0.26\x3].[sm].dbo.events
> select * from events
> where time = @.time and seq = @.seq
> --commit
> But it does not work in Query Analyzer when I uncomment the distributed
> xaction and commit.
> I get this error:
> Server: Msg 7391, Level 16, State 1, Line 12
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I'm using sqlserver 8.0.818 on Win2003 server on both sides.
> I tried several things to fix it, but to no avail:
> @.@.servername matches the %computername% on each machine.
> I enabled DTC Network Access using dcomcnfg.exe (all four options now
> enabled). I then restarted DTC and sqlserver.
> I verified DTC runs as NetworkService.
> I even used telnet to verify the two machines can connect to each other's
> port 135.
> ** WHAT HAVE I MISSED ? **
> How do I get distributed transactions to work on these servers?
> --Mike
Monday, March 12, 2012
Msg 4712: cannot truncate table
Hello,
I am running SQL Server 2005 SP1. Whenever I issue a 'truncate' query to the DB engine it fails if the table to be modified is currently referenced by a foreign key constraint.
A code snippet (T-SQL) that demonstrates the problem is beneath.
Does anybody know a fix-it?
Thanks in advance,
Intenion
USE tempdb
Go
-- Clearing the 'tmpdb' database
IFOBJECT_ID('user')ISNOTNULL
DROPTABLE [user];
IFOBJECT_ID('address')ISNOTNULL
DROPTABLE address;
-- Creating tables: 'address' and 'user'
CREATETABLE address
(
id INTIDENTITY(0, 1)CONSTRAINT pk_address PRIMARYKEY,
zip SMALLINTNOTNULLCONSTRAINT chk_zip CHECK(zip > 0),
street NVARCHAR(50)NOTNULLCONSTRAINT chk_street CHECK(LEN(street)> 0),
number SMALLINTNOTNULLCONSTRAINT chk_number CHECK(number > 0)
CONSTRAINT unq_address UNIQUE(zip, street, number)
);
Go
CREATETABLE [user]
(
email VARCHAR(50)CONSTRAINT pk_user PRIMARYKEY,
address INTNULLCONSTRAINT fk_user_address FOREIGNKEYREFERENCES address (id)ONDELETESETNULL
);
Go
-- Populating tables
INSERTINTO address VALUES(2365,'street 1', 2);
INSERTINTO address VALUES(23369,'street 2', 2);
Go
INSERTINTO [user] VALUES('someone@.isp.net', 0);
INSERTINTO [user] VALUES('somebody@.isp-r-us.com', 1);
Go
-- Truncate query that fails
TRUNCATETABLE address;
-- Delete query that passes
--DELETE address;
Hi,this is by design. YOu either have to drop the FK first or use the DELETE statement.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Hi,
Maybe a documentation update would be welcome for the 'truncate table' topic in Books Online.
Thank you for your answer,
Intenion.
Jens K. Suessmeyer wrote:
Hi, this is by design. YOu either have to drop the FK first or use the DELETE statement.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
I think I am running into the same situation. Would you mind showing how (using SMO) that I would drop the FK (then readd it so that the table is not changed)? It seems from the documentation that DELETE also tries to enforce the FK constraint so the error would be reasserted if I used DELETE.
Kevin
Msg 4712: cannot truncate table
Hello,
I am running SQL Server 2005 SP1. Whenever I issue a 'truncate' query to the DB engine it fails if the table to be modified is currently referenced by a foreign key constraint.
A code snippet (T-SQL) that demonstrates the problem is beneath.
Does anybody know a fix-it?
Thanks in advance,
Intenion
USE tempdb
Go
-- Clearing the 'tmpdb' database
IF OBJECT_ID('user') IS NOT NULL
DROP TABLE [user];
IF OBJECT_ID('address') IS NOT NULL
DROP TABLE address;
-- Creating tables: 'address' and 'user'
CREATE TABLE address
(
id INT IDENTITY (0, 1) CONSTRAINT pk_address PRIMARY KEY,
zip SMALLINT NOT NULL CONSTRAINT chk_zip CHECK (zip > 0),
street NVARCHAR(50) NOT NULL CONSTRAINT chk_street CHECK (LEN(street) > 0),
number SMALLINT NOT NULL CONSTRAINT chk_number CHECK (number > 0)
CONSTRAINT unq_address UNIQUE (zip, street, number)
);
Go
CREATE TABLE [user]
(
email VARCHAR(50) CONSTRAINT pk_user PRIMARY KEY,
address INT NULL CONSTRAINT fk_user_address FOREIGN KEY REFERENCES address (id) ON DELETE SET NULL
);
Go
-- Populating tables
INSERT INTO address VALUES (2365, 'street 1', 2);
INSERT INTO address VALUES (23369, 'street 2', 2);
Go
INSERT INTO [user] VALUES ('someone@.isp.net', 0);
INSERT INTO [user] VALUES ('somebody@.isp-r-us.com', 1);
Go
-- Truncate query that fails
TRUNCATE TABLE address;
-- Delete query that passes
--DELETE address;
Hi,this is by design. YOu either have to drop the FK first or use the DELETE statement.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Hi,
Maybe a documentation update would be welcome for the 'truncate table' topic in Books Online.
Thank you for your answer,
Intenion.
Jens K. Suessmeyer wrote:
Hi, this is by design. YOu either have to drop the FK first or use the DELETE statement.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
I think I am running into the same situation. Would you mind showing how (using SMO) that I would drop the FK (then readd it so that the table is not changed)? It seems from the documentation that DELETE also tries to enforce the FK constraint so the error would be reasserted if I used DELETE.
Kevin
msg 3721
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.
Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>
|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>
msg 3721
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>>I tried to run the following in query analyzer:
>> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
>> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
>> And I tried to search Microsoft support website for this message and none
>> of them is relevant to my case. Can anyone please help? Thanks.
>>
>|||You didn't post the message text. I looked in SQL Server 2000 sysmessages table and didn't find
error 3721, which makes it very hard for us to suggest something.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
" 009SteveJobs" <steve@.jobs.com> wrote in message news:e88ceZFDHHA.3604@.TK2MSFTNGP03.phx.gbl...
> Just checked. It's correct.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> See if the table name provided is correct.
>> Thanks
>> Hari
>> " 009SteveJobs" <steve@.jobs.com> wrote in message news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>>I tried to run the following in query analyzer:
>> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
>> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
>> And I tried to search Microsoft support website for this message and none of them is relevant to
>> my case. Can anyone please help? Thanks.
>>
>>
>
msg 3721
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>|||You didn't post the message text. I looked in SQL Server 2000 sysmessages ta
ble and didn't find
error 3721, which makes it very hard for us to suggest something.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
" 009SteveJobs" <steve@.jobs.com> wrote in message news:e88ceZFDHHA.3604@.TK2MSFTNGP03.phx.gbl
..
> Just checked. It's correct.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
>
Msg 242 when converting date from string (date in 'YYYY-MM-DD hh:mm:ss.ms' format)
MS SQL Server 2000 SP4 Spanish
It doesn't accept such values into query, although it shows them in this format.
select * from values where Dt > '2006-06-26 00:00:00.000'
returns with Msg 242,...
What am I doing wrong?
You have me baffled, because it seems to convert fine for me; however, try this in case it is a locale issue:
Code Snippet
select convert (datetime, '2006-06-26 00:00:00.000' , 121)
as explicitConversion
/*
explicitConversion
-
2006-06-26 00:00:00.000
*/
|||Explicit conversion works, but I need to get MS SQL Server to accept dates in this format (ODBC)
Ok, found problem. My fault.
Actually ODBC format requires {ts 'YYYY-MM-DD hh:mms'}, not just time string.
Msg 208 / Temp Tables
I issue the following commands in the Query Analyzer:
Select * from Prod into #Temp1
Insert into #Temp1 Select * from OldProd
I Get the following error:
Server: Msg 208, Level 16, State 1, Line 121
Invalid object name '#Temp1'.
Any ideas?
Thanks,
KFTo create the table using SELECT...INTO the syntax should be:
Select * into #Temp1 from Prod
Then run
Insert into #Temp1 Select * from OldProd
- Vishal|||Below executes OK on my machine...
Select * into #Temp1 from authors
Insert into #Temp1 Select * from authors
If you post a repro which we can execute, we might be able to help...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kflash" <kflash@.ameriflashtech.com> wrote in message
news:1f36401c389c5$9b9bfdc0$a601280a@.phx.gbl...
> I know. I got the syntax wrong on the original post but
> its right in the Query analyzer.
> When I run these commands:
> Select * into #Temp1 from Prod
> Insert into #Temp1 Select * from OldProd
> I get this Result:
> Server: Msg 208, Level 16, State 1, Line 121
> Invalid object name '#Temp1'.
> If I highlight the above commands and run them by
> themselves, it returns the correct result.
> It seems that Microsoft has issues with Temp tables. I'm
> hoping that there is a work around.
> Keith
>
> >--Original Message--
> >To create the table using SELECT...INTO the syntax
> should be:
> >
> >Select * into #Temp1 from Prod
> >
> >Then run
> >
> >Insert into #Temp1 Select * from OldProd
> >
> >
> >--
> >- Vishal
> >
> >
> >.
> >
Friday, March 9, 2012
Msg 107, Level 16, State 2, Line 1
I am getting the following error from the query below against SQL
Server 8.00.2039 (SP4)
Error:
====
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'd' does not match with a table name or alias name
used in the query.
Select Statement:
=============
select ...
from trades a,
gems_product_groups b,
portfolio_nav_mapping c,
products d,
limit_types e
left outer join gems_prod_trade_mod f on d.product_id =
f.product_ID
I know this was a known bug in MS-SQL7, but I thought it had been fixed
in 2000.
Can anyone help?
Thanks(billdonovan1947@.yahoo.com.au) writes:
> I am getting the following error from the query below against SQL
> Server 8.00.2039 (SP4)
> Error:
>====
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'd' does not match with a table name or alias name
> used in the query.
> Select Statement:
>=============
> select ...
> from trades a,
> gems_product_groups b,
> portfolio_nav_mapping c,
> products d,
> limit_types e
> left outer join gems_prod_trade_mod f on d.product_id =
> f.product_ID
> I know this was a known bug in MS-SQL7, but I thought it had been fixed
> in 2000.
Without seeing the entire query, it's difficult to see what might be
wrong, but try rewrite the query to use JOIN syntax throughout:
SELECT ...
FROM trades a
JOIN gems_product_groups b ON ...
JOIN portfolio_nav_mapping c ON ...
JOIN products d ON ...
JOIN limit_types e ON ...
LEFT JOIN gems_prod_trade_mod f on d.product_id = ...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>From what I've observed, "left join" requires the tables that you're
joining to be adjacent in the list, at least when mixed with other
joins written the way you have them. In your case you have limit_types
between products and the left join. If you simply swap the positions of
products and limit_types then it should work. e.g:
select ...
from trades a,
gems_product_groups b,
portfolio_nav_mapping c,
limit_types e,
products d
left outer join gems_prod_trade_mod f on d.product_id =
f.product_ID
Malcolm|||>>>From what I've observed, "left join" requires the tables that you're
>>joining to be adjacent in the list, at least when mixed with other
>>joins written the way you have them.
Brilliant! That works. Thanks for all your help.
Msg 107, Level 16, State 2, Line 1
I am getting the following error from the query below against SQL
Server 8.00.2039 (SP4)
Error:
====
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'd' does not match with a table name or alias name
used in the query.
Select Statement:
=============
select ...
from trades a,
gems_product_groups b,
portfolio_nav_mapping c,
products d,
limit_types e
left outer join gems_prod_trade_mod f on d.product_id =
f.product_ID
I know this was a known bug in MS-SQL7, but I thought it had been fixed
in 2000.
Can anyone help?
ThanksBill
It worked on my workstation (SQL Server 2000 SP3)
BTW why would you want to use an 'old' style and not OUTER JOIN?
"bill1947" <billdonovan1947@.yahoo.com.au> wrote in message
news:1138768112.996524.20860@.g14g2000cwa.googlegroups.com...
> Hi,
> I am getting the following error from the query below against SQL
> Server 8.00.2039 (SP4)
> Error:
> ====
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'd' does not match with a table name or alias name
> used in the query.
> Select Statement:
> =============
> select ...
> from trades a,
> gems_product_groups b,
> portfolio_nav_mapping c,
> products d,
> limit_types e
> left outer join gems_prod_trade_mod f on d.product_id =
> f.product_ID
> I know this was a known bug in MS-SQL7, but I thought it had been fixed
> in 2000.
> Can anyone help?
> Thanks
>|||Uri,
I changed the order of the tables to
gems_product_groups b,
portfolio_nav_mapping c,
limit_types e,
products d
left outer join gems_prod_trade_mod f on d.product_id =
f.product_ID
and it works. In the previous incarnation I tried every type of join
and got the same error.
msg : 2714
"Server: Msg 2714, Level 16, State 4, Line 1
There is already an object named 'ACTIVITY_TABLE' in the database."
the table does not exists in the DB I used following scripts to create table
create table dbo.ACTIVITY_TABLE (
ACTIVITY_DEPT_CODE numeric(10) not null,
ACTIVITY_CODE numeric(2) not null,
ACTIVITY_LOCAL_CODE numeric(9) null,
ACTIVITY_DESC varchar(200) null,
CORDINATOR_CODE varchar(50) null,
LASTUSERID varchar(8) not null,
LASTDATE datetime not null,
ACTIVITY_IND varchar(1) null,
constraint ACTIVITY_TABLE primary key (ACTIVITY_DEPT_CODE, ACTIVITY_CODE)
)
GoHi
Rename you constraint to something else. Only 1 object in a DB can have a
specific name. Your Table and your Constraint have the same name which is
not allowed
e.g.
constraint cons_ACTIVITY_TABLE primary key (ACTIVITY_DEPT_CODE,
ACTIVITY_CODE)
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/
"Adel" <Adel@.discussions.microsoft.com> wrote in message
news:3FE4F90F-D387-4F81-9576-51C31C9D79C1@.microsoft.com...
> II am trying to create table through Query Analyzer. an error message
appears
> "Server: Msg 2714, Level 16, State 4, Line 1
> There is already an object named 'ACTIVITY_TABLE' in the database."
> the table does not exists in the DB I used following scripts to create
table
> create table dbo.ACTIVITY_TABLE (
> ACTIVITY_DEPT_CODE numeric(10) not null,
> ACTIVITY_CODE numeric(2) not null,
> ACTIVITY_LOCAL_CODE numeric(9) null,
> ACTIVITY_DESC varchar(200) null,
> CORDINATOR_CODE varchar(50) null,
> LASTUSERID varchar(8) not null,
> LASTDATE datetime not null,
> ACTIVITY_IND varchar(1) null,
> constraint ACTIVITY_TABLE primary key (ACTIVITY_DEPT_CODE,
ACTIVITY_CODE)
> )
> Go
>
>|||Many people use a postscript, or prescript kind of name,,, I would use
Activity_table_PK, so when names are sorted all of the activity-table stuff
sorts together..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Adel" <Adel@.discussions.microsoft.com> wrote in message
news:3FE4F90F-D387-4F81-9576-51C31C9D79C1@.microsoft.com...
> II am trying to create table through Query Analyzer. an error message
> appears
> "Server: Msg 2714, Level 16, State 4, Line 1
> There is already an object named 'ACTIVITY_TABLE' in the database."
> the table does not exists in the DB I used following scripts to create
> table
> create table dbo.ACTIVITY_TABLE (
> ACTIVITY_DEPT_CODE numeric(10) not null,
> ACTIVITY_CODE numeric(2) not null,
> ACTIVITY_LOCAL_CODE numeric(9) null,
> ACTIVITY_DESC varchar(200) null,
> CORDINATOR_CODE varchar(50) null,
> LASTUSERID varchar(8) not null,
> LASTDATE datetime not null,
> ACTIVITY_IND varchar(1) null,
> constraint ACTIVITY_TABLE primary key (ACTIVITY_DEPT_CODE,
> ACTIVITY_CODE)
> )
> Go
>
>