Showing posts with label error. Show all posts
Showing posts with label error. Show all posts

Wednesday, March 28, 2012

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert Meiss
See MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert MeissSee MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Optimizing Job fails on tables with computed fields

Hi,
we have some tables in SQL Server 2000 with computed fields. When we try to
run an optimization job, it return the error
"Rebuilding indexes for table 'FI_Period'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER'"
After removing the field the job runs without errors. I cannot see any link
between a computed field and the Quoted Identifier setting.
Any input is appreciated.
TIA, Norbert MeissSee MSKB 301292 <http://support.microsoft.com/kb/q301292/>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:436B7DE5-21CC-413B-A0D6-854FD3721894@.microsoft.com...
> Hi,
> we have some tables in SQL Server 2000 with computed fields. When we try
> to
> run an optimization job, it return the error
> "Rebuilding indexes for table 'FI_Period'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]DBCC failed because the following SET option
s
> have
> incorrect settings: 'QUOTED_IDENTIFIER'"
> After removing the field the job runs without errors. I cannot see any
> link
> between a computed field and the Quoted Identifier setting.
> Any input is appreciated.
> TIA, Norbert Meiss

Optimizing a query

Hi
First of all the first script throws a syntax error. Is that mysql syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =
table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi
I have a query which I would like to optimize and thought I had found a
solution.
The query is a join of several selects from several different tables.
So we have in general the following:
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
JOIN
SELECT table1.Col1 FROM table2 WHERE exp2
ON table1.Col1 = table2.Col1
)
or something like that. Of course my query was a lot larger than this,
but it is just to illustrate the problem.
The above was too slow, and I made an attempt to optimize it and
thought it did work. The following is what I did:
Instead of the SELECT's I could create temporary tables and select into
those so I get the following:
SELECT table1.Col1
INTO #temp_table1
FROM table1 WHERE exp1
SELECT table1.Col1
INTO #temp_table2
FROM table2 WHERE exp2
And then join those two tables:
SELECT Col1 FROM
(
#temp_table1
JOIN
#temp_table2
ON #temp_table1.Col1 = #temp_table2.Col1
)
and at the end drop the temporary tables.
DROP Table #temp_table1
DROP Table #temp_table2
This gives the same result as the original statement.
I profiled a bit and saw that if I created the temp tables, and ran the
join statement several times after each other, the first time it is run
it take a lot of time compared to the following. Can anyone explain to
me why this is the case? I thought that the temporary tables were
created and loaded into the cache, and therefore all calls to the big
JOIN statement should take equally long time.
The thing is that I cannot reuse the temporary tables, so I get no
benefit from all the above.
Thank you very much in advance
Jeeji|||Hi
First of all the first script throws a syntax error. Is that mysql syntax?
SELECT Col1 FROM
(
SELECT table1.Col1 FROM table1 JOIN table2 ON table1.Col1 =
table2.Col1 WHERE exp1 AND ....
) AS Der
--Or
SELECT * FROM
(
SELECT table1.Col1 FROM table1 WHERE exp1
UNION ALL
SELECT table1.Col1 FROM table2 WHERE exp2
) AS Der
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Uri,
I think that is just a small fragment of the query. (At least I hope it's
just a fragment...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jeeji" <jihad_dk@.yahoo.com> wrote in message
news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...
> Hi
> I have a query which I would like to optimize and thought I had found a
> solution.
> The query is a join of several selects from several different tables.
> So we have in general the following:
> SELECT Col1 FROM
> (
> SELECT table1.Col1 FROM table1 WHERE exp1
> JOIN
> SELECT table1.Col1 FROM table2 WHERE exp2
> ON table1.Col1 = table2.Col1
> )
> or something like that. Of course my query was a lot larger than this,
> but it is just to illustrate the problem.
> The above was too slow, and I made an attempt to optimize it and
> thought it did work. The following is what I did:
> Instead of the SELECT's I could create temporary tables and select into
> those so I get the following:
> SELECT table1.Col1
> INTO #temp_table1
> FROM table1 WHERE exp1
> SELECT table1.Col1
> INTO #temp_table2
> FROM table2 WHERE exp2
> And then join those two tables:
> SELECT Col1 FROM
> (
> #temp_table1
> JOIN
> #temp_table2
> ON #temp_table1.Col1 = #temp_table2.Col1
> )
> and at the end drop the temporary tables.
> DROP Table #temp_table1
> DROP Table #temp_table2
> This gives the same result as the original statement.
> I profiled a bit and saw that if I created the temp tables, and ran the
> join statement several times after each other, the first time it is run
> it take a lot of time compared to the following. Can anyone explain to
> me why this is the case? I thought that the temporary tables were
> created and loaded into the cache, and therefore all calls to the big
> JOIN statement should take equally long time.
> The thing is that I cannot reuse the temporary tables, so I get no
> benefit from all the above.
> Thank you very much in advance
> Jeeji
>|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:[vbcol=seagreen]
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
[vbcol=seagreen]
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:|||Hi.
I thought I made it clear that it was just a fragment of the code. But
now something new came to my attention.
I can see that if I clear the SQL server cache and call the first SQL
statement (the one without the temporary table), the SQL profiler shows
that this call takes longer (around twice) than it takes if I clear the
server cache and run the temporary table version of the call.
However if I do the same calls from a C# application, the opposite
occurs.
To clear the SQL server cache I use:
DBCC FreeSystemCache('All')
And for the C# application I am using .Net 2.0, using the
System.Data.SQLClient calls.
Any advice would just be great.
Arnie Rowland wrote:[vbcol=seagreen]
> Uri,
> I think that is just a small fragment of the query. (At least I hope it's
> just a fragment...)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "jeeji" <jihad_dk@.yahoo.com> wrote in message
> news:1151500670.143984.181140@.x69g2000cwx.googlegroups.com...|||Sorry for the distrubance guys. I figured it out myself.
The problem is that I did not clear the cache from the C# application.
If I did, the same results show.
Jeeji
jeeji skrev:
[vbcol=seagreen]
> Hi.
> I thought I made it clear that it was just a fragment of the code. But
> now something new came to my attention.
> I can see that if I clear the SQL server cache and call the first SQL
> statement (the one without the temporary table), the SQL profiler shows
> that this call takes longer (around twice) than it takes if I clear the
> server cache and run the temporary table version of the call.
> However if I do the same calls from a C# application, the opposite
> occurs.
> To clear the SQL server cache I use:
> DBCC FreeSystemCache('All')
> And for the C# application I am using .Net 2.0, using the
> System.Data.SQLClient calls.
> Any advice would just be great.
> Arnie Rowland wrote:

Monday, March 26, 2012

OPTIMIZER LOCK HINTS

I want to populate ULF with all the records from ULF_Backup in my License
database.
I can't get around this error, "OPTIMIZER LOCK HINTS"
Here is the SQL code I'm running from Query Analyzer.
sp_dbcmptlevel 'license', 70
GO
SET IDENTITY_INSERT ULF ON
INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
STATUS_DATE)
SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
LICENSE_STATUS, STATUS_DATE)
WITH (NO LOCK)
SET IDENTITY_INSERT ULF OFFPull out the select statement by itself and try to execute it. Before you
do that, do you see anything wrong with the sytax you are using? Hint -
there is something very wrong.|||The SELECT statement column was misplaced. Also, there was an extraneous
space in the NOLOCK hint. Try:
SET IDENTITY_INSERT ULF ON
GO
INSERT INTO ULF (
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
SELECT
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
GO
SET IDENTITY_INSERT ULF OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:9AFF21C9-C89A-4051-9C2F-9E5C4D91B3B4@.microsoft.com...
>I want to populate ULF with all the records from ULF_Backup in my License
> database.
> I can't get around this error, "OPTIMIZER LOCK HINTS"
> Here is the SQL code I'm running from Query Analyzer.
> sp_dbcmptlevel 'license', 70
> GO
> SET IDENTITY_INSERT ULF ON
> INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
> AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
> NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
> STATUS_DATE)
> SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
> CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
> TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
> LICENSE_STATUS, STATUS_DATE)
> WITH (NO LOCK)
> SET IDENTITY_INSERT ULF OFF|||no sure what that error is, as there are multiple errors for locking hints
but there are more than that here:
1. mixed INSERT..SELECT and INSERT..VALUES syntax.
2. NOLOCK hint is one word
3. are you truncating the ULF table first? if not, you'll need to check
for existing rows in the ULF table, based on the key. [as no DDL posted,
assuming SPORTSMAN_KEY, LICENSE_NUM is the key)
e.g.
INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
STATUS_DATE)
SELECT SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
WHERE NOT EXISTS (
SELECT * FROM ULF
WHERE SPORTSMAN_KEY = ULF_Backup.SPORTSMAN_KEY
AND LICENSE_NUM = ULF_Backup.LICENSE_NUM
)
Mike L wrote:
> I want to populate ULF with all the records from ULF_Backup in my License
> database.
> I can't get around this error, "OPTIMIZER LOCK HINTS"
> Here is the SQL code I'm running from Query Analyzer.
> sp_dbcmptlevel 'license', 70
> GO
> SET IDENTITY_INSERT ULF ON
> INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
> AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
> NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
> STATUS_DATE)
> SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
> CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
> TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
> LICENSE_STATUS, STATUS_DATE)
> WITH (NO LOCK)
> SET IDENTITY_INSERT ULF OFF|||Mike,
My guess is that you are passing a column name as a parameter to
a user-defined function (or something the parser thinks is one). What
is FROM ULF_Backup(column names here?) supposed to mean?
Generally the only time there is a parameter list in this part of a FROM
clause is when the source is a table-valued user-defined function, and
in that case the parameters cannot be column names.
Steve Kass
Drew University
Mike L wrote:

>I want to populate ULF with all the records from ULF_Backup in my License
>database.
>I can't get around this error, "OPTIMIZER LOCK HINTS"
>Here is the SQL code I'm running from Query Analyzer.
>sp_dbcmptlevel 'license', 70
>GO
>SET IDENTITY_INSERT ULF ON
>INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
>ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
>AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
>NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
>STATUS_DATE)
>SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
>CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
>HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
>TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
>LICENSE_STATUS, STATUS_DATE)
>WITH (NO LOCK)
>SET IDENTITY_INSERT ULF OFF
>|||I get the following error message when I run your code. Why and how do I
correct this error?
Server: Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'ULF' when
IDENTITY_INSERT is set to ON.
"Dan Guzman" wrote:

> The SELECT statement column was misplaced. Also, there was an extraneous
> space in the NOLOCK hint. Try:
> SET IDENTITY_INSERT ULF ON
> GO
> INSERT INTO ULF (
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
> SELECT
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
> FROM ULF_Backup WITH (NOLOCK)
> GO
> SET IDENTITY_INSERT ULF OFF
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:9AFF21C9-C89A-4051-9C2F-9E5C4D91B3B4@.microsoft.com...
>
>|||I don't get an error. Can you post your CREATE TABLE statements?
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:0B0CF504-D831-4B47-A44A-49387B11514B@.microsoft.com...
>I get the following error message when I run your code. Why and how do I
> correct this error?
> Server: Msg 545, Level 16, State 1, Line 1
> Explicit value must be specified for identity column in table 'ULF' when
> IDENTITY_INSERT is set to ON.
> "Dan Guzman" wrote:
>|||The code is long because I created a script of the table. Here is the scrip
t.
CREATE TABLE [dbo].[ULF] (
[ULF_KEY] [Surrogate_Key] IDENTITY (1, 1) NOT NULL ,
[SPORTSMAN_KEY] [int] NULL ,
[LICENSE_YEAR] [smallint] NULL ,
[LICENSE_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONFIRMATION] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULF_FORM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BOOK_FORM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BOOK_NUM] [int] NULL ,
[DEALER_BATCH_KEY] [int] NULL ,
[HARVEST_FLAG] [tinyint] NULL ,
[AMOUNT] [money] NULL ,
[COUNTY_OF_DEALER] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HARVEST_DATE] [datetime] NULL ,
[HARVEST_TIME] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRANSACTION_DATE] [datetime] NULL ,
[NO_PENALTY_FLAG] [tinyint] NULL ,
[BP_CONFIRM_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DUPLICATION_DATE] [datetime] NULL ,
[LICENSE_STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STATUS_DATE] [datetime] NOT NULL ,
[Lchg_Userid] [Last_Chg_Userid] NOT NULL ,
[Lchg_Date] [Last_Chg_Date] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [XIF418ULF] ON [dbo].[ULF]([BOOK_FORM_TYPE],
[BOOK_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[ULF] ADD
CONSTRAINT [DF__ULF__Lchg_Userid__789EE131] DEFAULT (user_name()) FOR
[Lchg_Userid],
CONSTRAINT [DF__ULF__Lchg_Date__7993056A] DEFAULT (getdate()) FOR
[Lchg_Date],
CONSTRAINT [PK__ULF__7A8729A3] PRIMARY KEY NONCLUSTERED
(
[ULF_KEY]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [Num_Form_Type_Idx] ON [dbo].[ULF]([LICENSE_NUM],
[ULF_FORM_TYPE], [LICENSE_STATUS]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF332ULF] ON [dbo].[ULF]([SPORTSMAN_KEY]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [XIF349ULF] ON [dbo].[ULF]([LICENSE_STATUS], [AMOUNT]) WITH
FILLFACTOR = 90, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [XIF455ULF] ON [dbo].[ULF]([DEALER_BATCH_KEY]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF421ULF] ON [dbo].[ULF]([COUNTY_OF_DEALER]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF432ULF] ON [dbo].[ULF]([LICENSE_YEAR]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [XIF440ULF] ON [dbo].[ULF]([ULF_FORM_TYPE]) WITH FILLFACTOR
= 90, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [ULF_Num_Idx] ON [dbo].[ULF]([LICENSE_NUM]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [IX_ULF] ON [dbo].[ULF]([TRANSACTION_DATE]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [Index_BookNum] ON [dbo].[ULF]([BOOK_NUM]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[ZERO_ONE]', N'[ULF].[HARVEST_FLAG]'
GO
EXEC sp_bindrule N'[dbo].[ZERO_ONE]', N'[ULF].[NO_PENALTY_FLAG]'
GO
setuser
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tD_ULF on dbo.ULF for DELETE as
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DELETE trigger on ULF */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/461 DEALER_TRAN ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,DEALER_TRAN
where
/* %JoinFKPK(DEALER_TRAN,deleted," = "," and") */
DEALER_TRAN.LOST_ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because DEALER_TRAN exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/451 REVENUE_DETAIL ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,REVENUE_DETAIL
where
/* %JoinFKPK(REVENUE_DETAIL,deleted," = "," and") */
REVENUE_DETAIL.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because REVENUE_DETAIL exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/333 ULF_QUESTIONS ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ULF_QUESTIONS
where
/* %JoinFKPK(ULF_QUESTIONS,deleted," = "," and") */
ULF_QUESTIONS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because ULF_QUESTIONS exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/4 ULF_PRIVILEGES ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ULF_PRIVILEGES
where
/* %JoinFKPK(ULF_PRIVILEGES,deleted," = "," and") */
ULF_PRIVILEGES.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because ULF_PRIVILEGES exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/335 HARVEST_TOTALS ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,HARVEST_TOTALS
where
/* %JoinFKPK(HARVEST_TOTALS,deleted," = "," and") */
HARVEST_TOTALS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because HARVEST_TOTALS exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tI_ULF on dbo.ULF for INSERT as
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* INSERT trigger on ULF */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.errno int,
@.errmsg varchar(255)
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* Update Last Change UserID and Last Change Date */
UPDATE ULF
SET lchg_date = GETDATE(),
lchg_userid = CURRENT_USER
FROM ULF, inserted
WHERE ULF.ULF_KEY = inserted.ULF_KEY
select @.numrows = @.@.rowcount
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF_FORM_TYPE R/440 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ULF_FORM_TYPE)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,ULF_FORM_TYPE
where
/* %JoinFKPK(inserted,ULF_FORM_TYPE) */
inserted.ULF_FORM_TYPE = ULF_FORM_TYPE.ULF_FORM_TYPE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because ULF_FORM_TYPE does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_YEAR R/432 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_YEAR)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_YEAR
where
/* %JoinFKPK(inserted,LICENSE_YEAR) */
inserted.LICENSE_YEAR = LICENSE_YEAR.LICENSE_YEAR
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.LICENSE_YEAR is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_YEAR does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* COUNTY R/421 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(COUNTY_OF_DEALER)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,COUNTY
where
/* %JoinFKPK(inserted,COUNTY) */
inserted.COUNTY_OF_DEALER = COUNTY.COUNTY_CODE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.COUNTY_OF_DEALER is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because COUNTY does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DEALER_BATCH R/455 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(DEALER_BATCH_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,DEALER_BATCH
where
/* %JoinFKPK(inserted,DEALER_BATCH) */
inserted.DEALER_BATCH_KEY = DEALER_BATCH.DEALER_BATCH_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.DEALER_BATCH_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because DEALER_BATCH does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_BOOK R/418 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(BOOK_FORM_TYPE) or
update(BOOK_NUM)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_BOOK
where
/* %JoinFKPK(inserted,LICENSE_BOOK) */
inserted.BOOK_FORM_TYPE = LICENSE_BOOK.BOOK_FORM_TYPE and
inserted.BOOK_NUM = LICENSE_BOOK.BOOK_NUM
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.BOOK_FORM_TYPE is null and
inserted.BOOK_NUM is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_BOOK does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_STATUS R/349 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_STATUS)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_STATUS
where
/* %JoinFKPK(inserted,LICENSE_STATUS) */
inserted.LICENSE_STATUS = LICENSE_STATUS.LICENSE_STATUS
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_STATUS does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* SPORTSMAN R/332 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(SPORTSMAN_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,SPORTSMAN
where
/* %JoinFKPK(inserted,SPORTSMAN) */
inserted.SPORTSMAN_KEY = SPORTSMAN.SPORTSMAN_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.SPORTSMAN_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because SPORTSMAN does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tU_ULF on dbo.ULF for UPDATE as
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* UPDATE trigger on ULF */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.insULF_KEY Surrogate_Key,
@.errno int,
@.errmsg varchar(255)
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* Update Last Change UserID and Last Change Date */
UPDATE ULF
SET lchg_date = GETDATE(),
lchg_userid = CURRENT_USER
FROM ULF, inserted
WHERE ULF.ULF_KEY = inserted.ULF_KEY
select @.numrows = @.@.rowcount
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/461 DEALER_TRAN ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,DEALER_TRAN
where
/* %JoinFKPK(DEALER_TRAN,deleted," = "," and") */
DEALER_TRAN.LOST_ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because DEALER_TRAN exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/451 REVENUE_DETAIL ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,REVENUE_DETAIL
where
/* %JoinFKPK(REVENUE_DETAIL,deleted," = "," and") */
REVENUE_DETAIL.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because REVENUE_DETAIL exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/333 ULF_QUESTIONS ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,ULF_QUESTIONS
where
/* %JoinFKPK(ULF_QUESTIONS,deleted," = "," and") */
ULF_QUESTIONS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because ULF_QUESTIONS exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/4 ULF_PRIVILEGES ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,ULF_PRIVILEGES
where
/* %JoinFKPK(ULF_PRIVILEGES,deleted," = "," and") */
ULF_PRIVILEGES.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because ULF_PRIVILEGES exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/335 HARVEST_TOTALS ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,HARVEST_TOTALS
where
/* %JoinFKPK(HARVEST_TOTALS,deleted," = "," and") */
HARVEST_TOTALS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because HARVEST_TOTALS exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF_FORM_TYPE R/440 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ULF_FORM_TYPE)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,ULF_FORM_TYPE
where
/* %JoinFKPK(inserted,ULF_FORM_TYPE) */
inserted.ULF_FORM_TYPE = ULF_FORM_TYPE.ULF_FORM_TYPE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because ULF_FORM_TYPE does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_YEAR R/432 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_YEAR)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_YEAR
where
/* %JoinFKPK(inserted,LICENSE_YEAR) */
inserted.LICENSE_YEAR = LICENSE_YEAR.LICENSE_YEAR
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.LICENSE_YEAR is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_YEAR does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* COUNTY R/421 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(COUNTY_OF_DEALER)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,COUNTY
where
/* %JoinFKPK(inserted,COUNTY) */
inserted.COUNTY_OF_DEALER = COUNTY.COUNTY_CODE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.COUNTY_OF_DEALER is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because COUNTY does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DEALER_BATCH R/455 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(DEALER_BATCH_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,DEALER_BATCH
where
/* %JoinFKPK(inserted,DEALER_BATCH) */
inserted.DEALER_BATCH_KEY = DEALER_BATCH.DEALER_BATCH_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.DEALER_BATCH_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because DEALER_BATCH does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_BOOK R/418 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(BOOK_FORM_TYPE) or
update(BOOK_NUM)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_BOOK
where
/* %JoinFKPK(inserted,LICENSE_BOOK) */
inserted.BOOK_FORM_TYPE = LICENSE_BOOK.BOOK_FORM_TYPE and
inserted.BOOK_NUM = LICENSE_BOOK.BOOK_NUM
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.BOOK_FORM_TYPE is null and
inserted.BOOK_NUM is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_BOOK does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_STATUS R/349 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_STATUS)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_STATUS
where
/* %JoinFKPK(inserted,LICENSE_STATUS) */
inserted.LICENSE_STATUS = LICENSE_STATUS.LICENSE_STATUS
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_STATUS does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* SPORTSMAN R/332 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(SPORTSMAN_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,SPORTSMAN
where
/* %JoinFKPK(inserted,SPORTSMAN) */
inserted.SPORTSMAN_KEY = SPORTSMAN.SPORTSMAN_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.SPORTSMAN_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because SPORTSMAN does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Dan Guzman" wrote:

> I don't get an error. Can you post your CREATE TABLE statements?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:0B0CF504-D831-4B47-A44A-49387B11514B@.microsoft.com...
>
>|||Assuming the ULF_Backup backup table has the same schema as table ULF, all
you need to do is add the ULF_KEY column so that you can specify
IDENTITY_INSERT:
SET IDENTITY_INSERT ULF ON
GO
INSERT INTO ULF (
ULF_KEY,
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
SELECT
ULF_KEY,
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:F0077D93-CEA3-445E-A86A-688817B67352@.microsoft.com...
> The code is long because I created a script of the table. Here is the
> script.|||That fixed the problem.
Thank you.
"Dan Guzman" wrote:

> Assuming the ULF_Backup backup table has the same schema as table ULF, all
> you need to do is add the ULF_KEY column so that you can specify
> IDENTITY_INSERT:
> SET IDENTITY_INSERT ULF ON
> GO
> INSERT INTO ULF (
> ULF_KEY,
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
> SELECT
> ULF_KEY,
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
> FROM ULF_Backup WITH (NOLOCK)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:F0077D93-CEA3-445E-A86A-688817B67352@.microsoft.com...
>
>

Tuesday, March 20, 2012

Optimization and Integrity Error

Is there any way to run an optimization and integrity check against the
master and msdb databases? Below are the log files. Thanks!
- Gabe
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
Starting maintenance plan 'System Databases - Optimizations and Integrity'
on 6/2/2007 2:00:01 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[3] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'System Databases - Optimizations and Integrity' on
6/2/2007 2:00:01 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Gabe,
Sure, but you need to turn off the "repair minor problems" option.
http://support.microsoft.com/kb/290622
RLF
"Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Is there any way to run an optimization and integrity check against the
> master and msdb databases? Below are the log files. Thanks!
> - Gabe
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
> Starting maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
> database 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while other
> users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [3] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
|||Thanks!!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23qevkq6pHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Gabe,
> Sure, but you need to turn off the "repair minor problems" option.
> http://support.microsoft.com/kb/290622
> RLF
> "Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
> news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
>

Optimization and Integrity Error

Is there any way to run an optimization and integrity check against the
master and msdb databases? Below are the log files. Thanks!
- Gabe
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
Starting maintenance plan 'System Databases - Optimizations and Integrity'
on 6/2/2007 2:00:01 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[3] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'System Databases - Optimizations and Integrity' on
6/2/2007 2:00:01 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Gabe,
Sure, but you need to turn off the "repair minor problems" option.
http://support.microsoft.com/kb/290622
RLF
"Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Is there any way to run an optimization and integrity check against the
> master and msdb databases? Below are the log files. Thanks!
> - Gabe
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
> Starting maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
> database 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while other
> users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [3] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
|||Thanks!!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23qevkq6pHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Gabe,
> Sure, but you need to turn off the "repair minor problems" option.
> http://support.microsoft.com/kb/290622
> RLF
> "Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
> news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
>

Optimization and Integrity Error

Is there any way to run an optimization and integrity check against the
master and msdb databases? Below are the log files. Thanks!
- Gabe
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
Starting maintenance plan 'System Databases - Optimizations and Integrity'
on 6/2/2007 2:00:01 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[1] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs
to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other u
sers
are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE st
atement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[3] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs
to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'System Databases - Optimizations and Integrity' on
6/2/2007 2:00:01 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Gabe,
Sure, but you need to turn off the "repair minor problems" option.
http://support.microsoft.com/kb/290622
RLF
"Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Is there any way to run an optimization and integrity check against the
> master and msdb databases? Below are the log files. Thanks!
> - Gabe
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
> Starting maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
> database 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [1] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while o
ther
> users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [3] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>|||Thanks!!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23qevkq6pHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Gabe,
> Sure, but you need to turn off the "repair minor problems" option.
> http://support.microsoft.com/kb/290622
> RLF
> "Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
> news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
>

Optimization and Integrity Error

Is there any way to run an optimization and integrity check against the
master and msdb databases? Below are the log files. Thanks!
- Gabe
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
Starting maintenance plan 'System Databases - Optimizations and Integrity'
on 6/2/2007 2:00:01 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[3] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'System Databases - Optimizations and Integrity' on
6/2/2007 2:00:01 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Gabe,
Sure, but you need to turn off the "repair minor problems" option.
http://support.microsoft.com/kb/290622
RLF
"Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Is there any way to run an optimization and integrity check against the
> master and msdb databases? Below are the log files. Thanks!
> - Gabe
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
> Starting maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
> database 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while other
> users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [3] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>|||Thanks!!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23qevkq6pHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Gabe,
> Sure, but you need to turn off the "repair minor problems" option.
> http://support.microsoft.com/kb/290622
> RLF
> "Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
> news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to run an optimization and integrity check against the
>> master and msdb databases? Below are the log files. Thanks!
>> - Gabe
>> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
>> Starting maintenance plan 'System Databases - Optimizations and
>> Integrity' on 6/2/2007 2:00:01 AM
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
>> database 'MASTER'.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [1] Database master: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [2] Database model: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Database state cannot be changed while
>> other users are using the database 'msdb'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [3] Database msdb: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> Deleting old text reports... 0 file(s) deleted.
>> End of maintenance plan 'System Databases - Optimizations and Integrity'
>> on 6/2/2007 2:00:01 AM
>> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>>
>

Monday, March 19, 2012

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

Wednesday, March 7, 2012

Operator Mail Problems

I have set up an operator under the SQL Server Agent, added an email address
and click Test, and I get the following error message:
Error 22022: SQLServerAgent Error: The SQLServerAgent mail session is not
running; check the mail profile and/or the SQLServerAgent service startup
account int the SQLServerAgent Properties dialog.
Now, This machine has outlook installed and under Support Services -> SQL
Mail -> Properties, the profile name (Outlook) is selected and I can Test it
(Successfully started (and stopped) a MAPI session with this profile.)
Under the SQL Server Agent settings I have the profile set there (Outlook)
and I can successfully test it (Successfully started (and stopped) a mail
session with this profile)
Both the SQL Server and SQL Server Agent are run under the same domain
account. I can log on with this account and open Outlook and send and
recieve email messages with no problems. I can also use xp_sendmail in
Query Analyzer to send email messages as well.
I have searched extensively through google and have yet to find any settings
that I am missing, however, I am unable to recieve alerts to my operators,
I'm not even able to get the test message to work. Does anyone have any
ideas as to what could be causing this problem.
Thanks for the help,
MattHave you been through the following...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;263556
&
http://support.microsoft.com/default.aspx?scid=kb;en-
us;315886
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
Peter The Spate
>--Original Message--
>I have set up an operator under the SQL Server Agent,
added an email address
>and click Test, and I get the following error message:
>Error 22022: SQLServerAgent Error: The SQLServerAgent
mail session is not
>running; check the mail profile and/or the SQLServerAgent
service startup
>account int the SQLServerAgent Properties dialog.
>Now, This machine has outlook installed and under Support
Services -> SQL
>Mail -> Properties, the profile name (Outlook) is
selected and I can Test it
>(Successfully started (and stopped) a MAPI session with
this profile.)
>Under the SQL Server Agent settings I have the profile
set there (Outlook)
>and I can successfully test it (Successfully started (and
stopped) a mail
>session with this profile)
>Both the SQL Server and SQL Server Agent are run under
the same domain
>account. I can log on with this account and open Outlook
and send and
>recieve email messages with no problems. I can also use
xp_sendmail in
>Query Analyzer to send email messages as well.
>I have searched extensively through google and have yet
to find any settings
>that I am missing, however, I am unable to recieve alerts
to my operators,
>I'm not even able to get the test message to work. Does
anyone have any
>ideas as to what could be causing this problem.
>Thanks for the help,
>Matt
>
>.
>|||> Have you been through the following...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;263556
> &
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;315886
> Peter
Yes, I have been through both, but neither cover the problem I am having.

Operation Timed Out

Hi All,

I am getting below error when I am selecting all members of the dimension. It is the largest dimension among others. My query is very simple. An example is given below :

With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS })'
Select NON EMPTY ( {[MEASURES].[Sales Value]} ) ON COLUMNS ,
NON EMPTY { Distinct( { [Customer].[Customer].Members
From myCube
Where ([MyDimension].[SelectedDimension])

This query is throwing below exceptions:

Source: Microsoft? OLE DB Provider for Analysis Services
Message: The operation requested failed due to timeout
Stack Trace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()

1. What will be the reason for this exception?
2. This is not connection time out issue. I wanted confirm that since there is lot of data being fetched from olap database the system is not able to complete the operation. Hence the exception thrown.
3. This is dynamic query. Meaning query will be generated at runtime with selected measures and dimension and applying filters selected. Is there any other way of writing query so that performance is improved?

First, if you remove () from NON EMPTY ( {[MEASURES].[Sales Value]} ) . NON EMPTY is not a function, by definition.

Next, what are you trying to achieve by this, With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS })?

In SSAS 2005 you will have to point to [dimension].[hierarchy] not [dimension].[dimension]

With hierarchy i mean user hierarchies that you have defined.

Are you using SSAS 2000 or 2005?

Regards

Thomas Ivarsson

|||

Thanks for your reply Thomas.

I am using SSAS 2000.

With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS }) is used to make set of tuples to use it in Where clause.

I have web application to show reports. User will select dimensions he/she wants in report and applies filters on them.

Therefore Aggregate is used for filter purposes.

But when user selects dimension Customer (which is having huge records) the above mentioned exception is thrown. Even if filters are applied on it the excpetions occurs.

I wanted to know under what conditions the exception occurs.

Will it occurs if OLAP database is taking too much time to return all records for the selected dimension?

Regards,

Raghu Deshpande

Operation not allowed in this context

This error message is showing up when I execute the following code (called in a function):

Set rs = New ADODB.Recordset
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic
With rs
For j = 0 To .RecordCount - 1
rs(0).Value = j + 1
If j < .RecordCount - 1 Then .MoveNext
Next j
.Close
End With
Set rs = Nothing

Any idea what causes this to fire?what line is giving the error?

Set rs = New ADODB.Recordset
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic
For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
rs.Close
Set rs = Nothing|||...rs.Close.|||I have no idea. But try this one:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & " AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic

For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
rs.Close
Set rs = Nothing|||...what I have noticed is that if I comment out the rs.Close it runs just fine. That's not to say that it is ok to not close out your recordsets but, since I am new to ADO (always worked in DAO before) I am a little uncertain as to when that is/isn't necessary.

Any ideas on that?|||I suggest that you close your connection to the database and set the recordset to nothig:

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT RacePl FROM IndResults, PartData WHERE RaceID = " & lRaceID & _
" AND IndResults.PartID = PartData.PartID ORDER BY EventPl", conn, adOpenKeyset, adLockOptimistic

For j = 0 To rs.RecordCount - 1
rs(0).Value = j + 1
If j < rs.RecordCount - 1 Then rs.MoveNext
Next j
conn.Close 'Close the connection
Set conn = Nothing
Set rs = Nothing|||open due to the frequent requests for data in my app. But I usually close the recordsets immediately. Comment please!|||Originally posted by Bobba Buoy
open due to the frequent requests for data in my app. But I usually close the recordsets immediately. Comment please!

What should I comment?|||You suggested that closing the connection was probably a good idea. I am noting that I usually leave the connection open during the life of the application since it is usually pretty active. I was wondering how bad of an idea you thought that was.|||Originally posted by Bobba Buoy
You suggested that closing the connection was probably a good idea. I am noting that I usually leave the connection open during the life of the application since it is usually pretty active. I was wondering how bad of an idea you thought that was.

If your application is used by many users at same time, it's a good idea to close the database connection. One open connection takes several kilobytes in memory.

Operation is not valid due to the current state of the object

Hello,

We are getting this error when a user clients any report. they can see the directories OK, but when we upload a new report the error still happens.

Error is "Operation is not valid due to the current state of the object."

Any ideas?

Thanks

Michael

Hi Michael,

I am also getting the same error whenever I tried to open a report through browser. Please let me know if you have resolved this issue and the fix for the same.

Thanks & Regards,

Sathya

|||

This was address in a previous forum by Brian Hartman, I have provided you with a link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=212322&SiteID=1

Ham

Operation is not valid due to the current state of the object

Hello,

We are getting this error when a user clients any report. they can see the directories OK, but when we upload a new report the error still happens.

Error is "Operation is not valid due to the current state of the object."

Any ideas?

Thanks

Michael

Hi Michael,

I am also getting the same error whenever I tried to open a report through browser. Please let me know if you have resolved this issue and the fix for the same.

Thanks & Regards,

Sathya

|||

This was address in a previous forum by Brian Hartman, I have provided you with a link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=212322&SiteID=1

Ham

Operation is not valid due to the current state of the object

One of our developers intermittently gets this error "Operation is not valid
due to the current state of the object" when exporting a report to Excel from
Preview in Designer. It seems to happen when he attempts to export a second
time after changing a parameter value in Preview. Any thoughts?I'm getting the same error, only it doesn't happen in Report Designer, only
when viewing it on the web using the ReportServer. Very strange... And only
with certain parameters.
"toolman_2000" wrote:
> One of our developers intermittently gets this error "Operation is not valid
> due to the current state of the object" when exporting a report to Excel from
> Preview in Designer. It seems to happen when he attempts to export a second
> time after changing a parameter value in Preview. Any thoughts?

Operation is not allowed while the object is closed (was "Hmmm")

never seen this before, but I keep getting an error > "Operationg is not allowed while the object is closed" I thought maybe I was getting this due to an error in my stored procedure, however the procedure runs fine. Could this be an error in my procedure that it's just not catching? Or is it more likely to be in my VB code? Anyone know in general why this happens? Thanks.Yes this is sounds like an ADO error in your VB code. You are either trying to run a procedure against a closed connection object or you trying to access the data from a recordset object that has not been set or open.

Operation cancelled by user

Hello Guys,

I am currently experiencing a Sql error: operation cancelled by user.

I don't know where this error come from. Is it from ADO.NET or SQL Server 2000.

This error doesn't always occur. It only occurs when many users access the web site. I want to know who issue the error. Why issue the error.

Let me know if you have any ideas

Thanks in advanced

See if the following post helps

http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic10278.aspx

|||

Hi Raj Kasi,

Thanks for replying. I did not use sqlcommand.cancel(). Also, the error occurs before I call the finally.

Here is my code:

Dim sqlConnection As New sqlConnection(ConfigurationSettings.AppSettings(String.Format("db.{0}.SQLconnString", Env)))

Dim sbTable As New StringBuilder

Dim strCmpy As String

Dim tblCounters As String

strCmpy = Server.UrlDecode(Request.Cookies("Companies").Value)

Dim sqlCommand As New sqlCommand("sp_getCompanyInfo", sqlConnection)

Dim sqlDataReader As sqlDataReader

Dim myParam As SqlParameter

Dim errLog As New errReport

Try

sqlCommand.CommandType = CommandType.StoredProcedure

sqlConnection.Open()

myParam = sqlCommand.Parameters.Add("@.Companies", SqlDbType.VarChar, 50)

myParam.Value = strCmpy

myParam = sqlCommand.Parameters.Add("@.Class", SqlDbType.VarChar, 2)

myParam.Value = selClass

myParam = sqlCommand.Parameters.Add("@.BrchTmpl", SqlDbType.VarChar, 6)

myParam.Value = selBrchTmpl

sqlDataReader = sqlCommand.ExecuteReader()

Catch ex As Exception

errLog.reportError("Error=" & ex.ToString())

Finally

If Not sqlCommand Is Nothing Then

If Not sqlConnection Is Nothing AndAlso sqlConnection.State <> ConnectionState.Closed Then

sqlCommand.Close()

End If

sqlCommand.Dispose()

End If

If Not sqlConnection Is Nothing Then sqlConnection.Dispose()

End Try

The error occurs when I call sqlCommand.ExecuteReader(). This error only occurs when many users come to use my asp.net application. I use asp.net 1.1 on windows 2000 server framework 1.1 with IIS 6.0.

Thanks

Troy123

|||

Hi Troy123,

Could you see if hitting "stop" or navigating away from the page during operation reproduces this problem? You may need to specify how you treat people navigating away from the page to ensure that it does not kill the back end process explicitly (or maybe you want that to lower the load on your server).

Hope something comes of this,

John (MSFT)

|||

Hi Guys,

I have found the problem. In other pages, I put command.cancel. Now, I removed all command.cancel. It is ok now.

I use ADO.NET 1.1. I think ADO.NET 1.1 doesn't manage command.cancel well.

Regards,

Troy123

Saturday, February 25, 2012

Operating system error code 3(The system cannot find the path specified.).

Hi All,

I use Bulk insert to put data to myTable.
When the SQL server is in local machin, it works well. But when I put
the data in a sql server situated not locally, then I get a error
message like this:

Could not bulk insert because file 'C:\Data\2003
txtfiles\abif_20031130.txt' could not be opened. Operating system
error code 3(The system cannot find the path specified.).

BULK INSERT myTable
FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
with (
-- codepage = ' + char(39) + 'ACP' + char(39) + ',
fieldterminator = ';',
rowterminator = '\n',
keepnulls,
maxerrors=0)

Someone can explan me what the error shows up

Thanks in advance
- Loi -The location of the file is relative to the SQL Server on which the BULK
INSERT command runs. To import a file from another machine, specify an UNC
path rather than use drive letters. For example:

BULK INSERT myTable
FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'

Note that the SQL Server service account needs permissions to the share.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Loi" <votanloi@.hotmail.com> wrote in message
news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> Hi All,
> I use Bulk insert to put data to myTable.
> When the SQL server is in local machin, it works well. But when I put
> the data in a sql server situated not locally, then I get a error
> message like this:
> Could not bulk insert because file 'C:\Data\2003
> txtfiles\abif_20031130.txt' could not be opened. Operating system
> error code 3(The system cannot find the path specified.).
> BULK INSERT myTable
> FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> with (
> -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> fieldterminator = ';',
> rowterminator = '\n',
> keepnulls,
> maxerrors=0)
> Someone can explan me what the error shows up
> Thanks in advance
> - Loi -|||Hi Dan,
Thanks for your reply.
It does not work yet.
A next question:
SQL Server service account needs permissions to the share.
In my case SQL server is setup following:
In the security tab: Authentication - SQL server and windows.
Startup sevice account - system account.
Can somebody give me any hints

Regards
- Loi -

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<laEvc.20183$Tn6.12968@.newsread1.news.pas.earthlink .net>...
> The location of the file is relative to the SQL Server on which the BULK
> INSERT command runs. To import a file from another machine, specify an UNC
> path rather than use drive letters. For example:
> BULK INSERT myTable
> FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'
> Note that the SQL Server service account needs permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Loi" <votanloi@.hotmail.com> wrote in message
> news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> > Hi All,
> > I use Bulk insert to put data to myTable.
> > When the SQL server is in local machin, it works well. But when I put
> > the data in a sql server situated not locally, then I get a error
> > message like this:
> > Could not bulk insert because file 'C:\Data\2003
> > txtfiles\abif_20031130.txt' could not be opened. Operating system
> > error code 3(The system cannot find the path specified.).
> > BULK INSERT myTable
> > FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> > with (
> > -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> > fieldterminator = ';',
> > rowterminator = '\n',
> > keepnulls,
> > maxerrors=0)
> > Someone can explan me what the error shows up
> > Thanks in advance
> > - Loi -|||> Startup sevice account - system account.

Try using a domain account rather than the local system account and ensure
the account has permissions to the share.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Loi Tan Vo" <loi.tan.vo@.fnh.no> wrote in message
news:73f04281.0406040358.5a342f93@.posting.google.c om...
> Hi Dan,
> Thanks for your reply.
> It does not work yet.
> A next question:
> SQL Server service account needs permissions to the share.
> In my case SQL server is setup following:
> In the security tab: Authentication - SQL server and windows.
> Startup sevice account - system account.
> Can somebody give me any hints
> Regards
> - Loi -
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<laEvc.20183$Tn6.12968@.newsread1.news.pas.earthlink .net>...
> > The location of the file is relative to the SQL Server on which the BULK
> > INSERT command runs. To import a file from another machine, specify an
UNC
> > path rather than use drive letters. For example:
> > BULK INSERT myTable
> > FROM '\\MyOtherServer\MyShare\Data\2003 txtfiles\abif_20031130.txt'
> > Note that the SQL Server service account needs permissions to the share.
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Loi" <votanloi@.hotmail.com> wrote in message
> > news:af9512d5.0406030313.73ed644b@.posting.google.c om...
> > > Hi All,
> > > > I use Bulk insert to put data to myTable.
> > > When the SQL server is in local machin, it works well. But when I put
> > > the data in a sql server situated not locally, then I get a error
> > > message like this:
> > > > Could not bulk insert because file 'C:\Data\2003
> > > txtfiles\abif_20031130.txt' could not be opened. Operating system
> > > error code 3(The system cannot find the path specified.).
> > > > BULK INSERT myTable
> > > FROM 'C:\Data\2003 txtfiles\abif_20031130.txt'
> > > with (
> > > -- codepage = ' + char(39) + 'ACP' + char(39) + ',
> > > fieldterminator = ';',
> > > rowterminator = '\n',
> > > keepnulls,
> > > maxerrors=0)
> > > > Someone can explan me what the error shows up
> > > > Thanks in advance
> > > - Loi -