Intermittent SQL Server shutdown

I am running a scheduled job through SQL Server Agent every morning, Tuesday
to Saturday, at 3:00 am.
It runs perfectly for ws, sometimes months. Then, one morning, something
happens to cause SQL Server
and SQL Server Agent to stop running. *Nothing* has changed in the scheduled
job.
When this situation occurs, it always occurs while running the scheduled job
(i.e., the machine is not idle).
The scheduled job executes my stored procedure si_ImportMaster.
Furthermore, si_ImportMaster simply does some error checking and then execut
es a series of 10 stored procedures,
and when the error happens, it is always when si_ImportMaster is executing t
he same stored procedure (of those 10).
When I come in to work and realize what has happened, I re-start SQL Server
and SQL Server Agent
and execute the rest of the stored procedures in the process (including the
one it stopped on).
That "re-run" process completes without a hitch.
I would be happy to post the contents of the stored procedure in question, b
ut my thinking was that
if it runs correctly for ws or months, it can't be the fault of the SQL c
ode. Can it . . . ?
I have been given the job of troubleshooting this. I am much more familiar w
ith SQL Server programming
than the DBA side of things, so any and all assistance is appreciated.
We're running SQL 7.0 SP2.
If you need me to post anything else, let me know.
Thanks in advance -- Carl
Following is some error log output. I was able to find 4 relevant log files:
...\LOG\ERRORLOG.1 -- excerpted below
...\LOG\SQLAGENT.1 -- excerpted below
...\LOG\SQL00031.dmp -- 48K of mostly technical stuff (core dump info, etc
.)
...\LOG\SQL00032.dmp -- same as above.
Anyway,
Here's some output from ...\LOG\ERRORLOG.1
2005-03-13 19:05:06.75 kernel Microsoft SQL Server 7.00 - 7.00.842 (Intel
X86)
Mar 2 2000 06:49:37
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
----
--
2005-03-15 04:18:04.09 spid9 Process 9 unlocking unowned resource: KEY: 7
:1451737566:5 (57028e42a833)
2005-03-15 04:18:04.12 spid9 Process 9 unlocking unowned resource: KEY: 7
:1451737566:5 (57028e42a833)
2005-03-15 04:18:04.28 spid9 Using 'sqlimage.dll' version '4.0.5'
Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
Stack Dump being sent to E:\log\SQL00031.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/15/05 04:18:17 spid 9
*
* Input Buffer 40 bytes -
* E X E C s i _ I m p o r t M a s t e r
*
****************************************
************************************
***
----
--
Short Stack Dump
0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
2005-03-15 04:18:17.81 kernel SQL Server Assertion: File: <proc.c>, line=1
927
Failed Assertion = 'm_activeSdesList.Head () == NULL'.
2005-03-15 04:18:17.87 spid9 Using 'sqlimage.dll' version '4.0.5'
Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
Stack Dump being sent to E:\log\SQL00032.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/15/05 04:18:22 spid 9
*
* Input Buffer 40 bytes -
* E X E C s i _ I m p o r t M a s t e r
*
****************************************
************************************
***
----
--
Short Stack Dump
0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
0x0077b371 Module(sqlservr+37b371) (ex_raise2+35b)
0x0077b3dd Module(sqlservr+37b3dd) (ex_raise+5f)
0x00784f16 Module(sqlservr+384f16) (utassert_fail+37d)
0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned re
source KEY: 7:1451737566:5 (57028e42a833)..
2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned re
source KEY: 7:1451737566:5 (57028e42a833)..
Here's the relevant output from ...\LOG\SQLAGENT.1
3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 4, Connection broken. [SQLST
ATE 08S01]
3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 109, ConnectionRead (GetOver
LappedResult()). [SQLSTATE 01000]
3/15/2005 4:18:23 AM - ! [382] Logon to server '(local)' failed (ConnAttempt
CachableOp)
3/15/2005 4:18:28 AM - + [360] SQLServerAgent initiating shutdown following
MSSQLServer shutdown
3/15/2005 4:18:44 AM - + [188] Scheduler engine timed out (after 15 seconds)
waiting for 1 jobs(s) to stop
3/15/2005 4:18:44 AM - ! [359] The local host server is not running
3/15/2005 4:18:45 AM - + [098] SQLServerAgent terminated (forcefully)We had this problem too when I used to work as DBA.
The problem was with the Code that was written in the SP that was being
called in a Job. It used to crash
the SQLServer Engine (and along with it SQLAgent) and this is used to happen
randomly.
Finally we traced it to that part of the code. Fortunately, Microsoft has a
fix for this.
You dont seem to have the latest SQLServer 7 Service Pack. May be your
problem has been addressed
in one of them.
Did you the SQLServer Log file for any errors, Assertions ... etc.
Gopi
"Carl Imthurn" <nospam@.all.com> wrote in message
news:ua9EVkYKFHA.1096@.tk2msftngp13.phx.gbl...
>I am running a scheduled job through SQL Server Agent every morning,
>Tuesday to Saturday, at 3:00 am.
> It runs perfectly for ws, sometimes months. Then, one morning,
> something happens to cause SQL Server
> and SQL Server Agent to stop running. *Nothing* has changed in the
> scheduled job.
> When this situation occurs, it always occurs while running the scheduled
> job (i.e., the machine is not idle).
> The scheduled job executes my stored procedure si_ImportMaster.
> Furthermore, si_ImportMaster simply does some error checking and then
> executes a series of 10 stored procedures,
> and when the error happens, it is always when si_ImportMaster is executing
> the same stored procedure (of those 10).
> When I come in to work and realize what has happened, I re-start SQL
> Server and SQL Server Agent
> and execute the rest of the stored procedures in the process (including
> the one it stopped on).
> That "re-run" process completes without a hitch.
> I would be happy to post the contents of the stored procedure in question,
> but my thinking was that
> if it runs correctly for ws or months, it can't be the fault of the SQL
> code. Can it . . . ?
> I have been given the job of troubleshooting this. I am much more familiar
> with SQL Server programming
> than the DBA side of things, so any and all assistance is appreciated.
> We're running SQL 7.0 SP2.
> If you need me to post anything else, let me know.
> Thanks in advance -- Carl
> Following is some error log output. I was able to find 4 relevant log
> files:
> ...\LOG\ERRORLOG.1 -- excerpted below
> ...\LOG\SQLAGENT.1 -- excerpted below
> ...\LOG\SQL00031.dmp -- 48K of mostly technical stuff (core dump info,
> etc.)
> ...\LOG\SQL00032.dmp -- same as above.
> Anyway,
> Here's some output from ...\LOG\ERRORLOG.1
> 2005-03-13 19:05:06.75 kernel Microsoft SQL Server 7.00 - 7.00.842
> (Intel X86)
> Mar 2 2000 06:49:37
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
> ----
--
> 2005-03-15 04:18:04.09 spid9 Process 9 unlocking unowned resource: KEY:
> 7:1451737566:5 (57028e42a833)
> 2005-03-15 04:18:04.12 spid9 Process 9 unlocking unowned resource: KEY:
> 7:1451737566:5 (57028e42a833)
> 2005-03-15 04:18:04.28 spid9 Using 'sqlimage.dll' version '4.0.5'
> Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
> Stack Dump being sent to E:\log\SQL00031.dmp
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 03/15/05 04:18:17 spid 9
> *
> * Input Buffer 40 bytes -
> * E X E C s i _ I m p o r t M a s t e r
> *
> ****************************************
**********************************
*****
> ----
--
> Short Stack Dump
> 0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
> 0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
> 0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
> 0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
> 0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
> 0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
> 0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
> 0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
> 0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
> 0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
> 2005-03-15 04:18:17.81 kernel SQL Server Assertion: File: <proc.c>,
> line=1927
> Failed Assertion = 'm_activeSdesList.Head () == NULL'.
> 2005-03-15 04:18:17.87 spid9 Using 'sqlimage.dll' version '4.0.5'
> Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
> Stack Dump being sent to E:\log\SQL00032.dmp
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 03/15/05 04:18:22 spid 9
> *
> * Input Buffer 40 bytes -
> * E X E C s i _ I m p o r t M a s t e r
> *
> ****************************************
**********************************
*****
> ----
--
> Short Stack Dump
> 0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
> 0x0077b371 Module(sqlservr+37b371) (ex_raise2+35b)
> 0x0077b3dd Module(sqlservr+37b3dd) (ex_raise+5f)
> 0x00784f16 Module(sqlservr+384f16) (utassert_fail+37d)
> 0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
> 0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
> 0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
> 0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
> 0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
> 0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
> 0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
> 0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
> 2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
> 2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned
> resource KEY: 7:1451737566:5 (57028e42a833)..
> 2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
> 2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned
> resource KEY: 7:1451737566:5 (57028e42a833)..
>
> Here's the relevant output from ...\LOG\SQLAGENT.1
> 3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 4, Connection broken.
> [SQLSTATE 08S01]
> 3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 109, ConnectionRead
> (GetOverLappedResult()). [SQLSTATE 01000]
> 3/15/2005 4:18:23 AM - ! [382] Logon to server '(local)' failed
> (ConnAttemptCachableOp)
> 3/15/2005 4:18:28 AM - + [360] SQLServerAgent initiating shutdown
> following MSSQLServer shutdown
> 3/15/2005 4:18:44 AM - + [188] Scheduler engine timed out (after 15
> seconds) waiting for 1 jobs(s) to stop
> 3/15/2005 4:18:44 AM - ! [359] The local host server is not running
> 3/15/2005 4:18:45 AM - + [098] SQLServerAgent terminated (forcefully)
>|||Thanks Gopi -- I had wondered about that myself.
I will install the latest SP and see if that cures the problem.
Thanks for your time.
rgn wrote:
> We had this problem too when I used to work as DBA.
> The problem was with the Code that was written in the SP that was being
> called in a Job. It used to crash
> the SQLServer Engine (and along with it SQLAgent) and this is used to happ
en
> randomly.
> Finally we traced it to that part of the code. Fortunately, Microsoft has
a
> fix for this.
> You dont seem to have the latest SQLServer 7 Service Pack. May be your
> problem has been addressed
> in one of them.
> Did you the SQLServer Log file for any errors, Assertions ... etc.
> Gopi
> "Carl Imthurn" <nospam@.all.com> wrote in message
> news:ua9EVkYKFHA.1096@.tk2msftngp13.phx.gbl...
>
>
>sql

Intermittent ORDER BY not functionning

Hi!
I have this query running well, except that when
executing some times, the result change and doesn't start
with the urgence24 column order as requested!!! It's
really strange. Does somenone can help me or tell me where
to find?
Does the SELECT INTO with temporary table can make a
problem?
SELECT noLIFNR,
noLIFNR [LIFNR],
raisonSociale,
CASE urgence24 WHEN 1 THEN 'exclamation.gif'
ELSE 'spacer.gif' END AS urgence24,
indRegionRespVentes + '-' + telRespVentes AS
telRespVentes,
respVentes,
indRegion1 + '-' + tel1 AS tel1,
ville,
nomRegion,
nomMrc
INTO #Temp
FROM vFournisseurs
WHERE actif = 1
and version = 1
ORDER BY urgence24 ASC,
raisonSociale ASC,
1 ASC
select * from #temp
Thanks in advance
DavidDavid Parenteau wrote:
> Hi!
> I have this query running well, except that when
> executing some times, the result change and doesn't start
> with the urgence24 column order as requested!!! It's
> really strange. Does somenone can help me or tell me where
> to find?
> Does the SELECT INTO with temporary table can make a
> problem?
Yes. While ORDER BY will order the results selected, the insertion order
into the temp table is not guaranteed.
<snip>
> ORDER BY urgence24 ASC,
> raisonSociale ASC,
> 1 ASC
> select * from #temp
The only way to guarantee a particular order when selecting rows is to use
an ORDER BY clause in the SELECT statement that retrieves the rows:
select * from #temp
ORDER BY urgence24 ASC,
raisonSociale ASC,
1 ASC
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||IIRC, your technique is not guaranteed. There is a thread with the subject
"order by and identity" that recently discussed various techniques for doing
this, one of which is currently "guaranteed" according to MS.
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0d8401c503ad$94e0e780$a601280a@.phx.gbl...
> Hi!
> I have this query running well, except that when
> executing some times, the result change and doesn't start
> with the urgence24 column order as requested!!! It's
> really strange. Does somenone can help me or tell me where
> to find?
> Does the SELECT INTO with temporary table can make a
> problem?
> SELECT noLIFNR,
> noLIFNR [LIFNR],
> raisonSociale,
> CASE urgence24 WHEN 1 THEN 'exclamation.gif'
> ELSE 'spacer.gif' END AS urgence24,
> indRegionRespVentes + '-' + telRespVentes AS
> telRespVentes,
> respVentes,
> indRegion1 + '-' + tel1 AS tel1,
> ville,
> nomRegion,
> nomMrc
> INTO #Temp
> FROM vFournisseurs
> WHERE actif = 1
> and version = 1
> ORDER BY urgence24 ASC,
> raisonSociale ASC,
> 1 ASC
> select * from #temp
>
> Thanks in advance
> David|||Wow... I have read some part of that thread, but my
example does'nt use a IDENTITY column. How it applies the
same way? Does the problem is the SELECT INTO that don't
insert the rows after the ORDER BY occurs?
I need to give an answer to my client.
Thanks!
David

>--Original Message--
>IIRC, your technique is not guaranteed. There is a
thread with the subject
>"order by and identity" that recently discussed various
techniques for doing
>this, one of which is currently "guaranteed" according to
MS.
>"David Parenteau" <david.parenteau@.compuware.com> wrote
in message
>news:0d8401c503ad$94e0e780$a601280a@.phx.gbl...
start
where
>
>.
>|||> Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
A table should be treated as an unordered set of rows. You need to specify
ORDER BY when selecting *from* the table in order to guarantee a particular
sequence. Insertion sequence isn't relevant in this case.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...
> Wow... I have read some part of that thread, but my
> example does'nt use a IDENTITY column. How it applies the
> same way? Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
> I need to give an answer to my client.
> Thanks!
> David
>
> thread with the subject
> techniques for doing
> MS.
> in message
> start
> where|||My understanding is that the guaranteed technique involves the creation of a
temp table with an identity column and the use of an insert/select
statement. However, the topic of the referenced thread is not exactly the
same as the issue you are experiencing (now that I read the post a bit more
carefully). Bob's post is actually more appropriate.
I don't particularly understand why you use a temp table when you can simply
return the result set generated select/into statement (just omit the "into"
part). In any event, you should always consider a table as an unordered set
of rows and that a select statement without an order by clause is never
guaranteed to return the rows in any consistent order. I believe that is
your fundamental flaw. In addition, I also find fault with the use of the
asterisk ("*"), especially if this is production code. Both of these issues
are frequently discussed in the newsgroup.
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...
> Wow... I have read some part of that thread, but my
> example does'nt use a IDENTITY column. How it applies the
> same way? Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
> I need to give an answer to my client.
> Thanks!
> David
>
> thread with the subject
> techniques for doing
> MS.
> in message
> start
> where|||David,
Your concern is not with the order in which rows
are inserted into #temp. Your concern is with the order
of rows in the result set of the select query:
SELECT * FROM #temp
At least that's what it sounds like - sometimes you see
the resulting rows in an order you don't want.
The easy solution, and the only guaranteed solution, if
you need to see results in a particular order, is to add
an ORDER BY clause. You will always get the order
you want if you execute
SELECT * FROM #temp
ORDER BY urgence24, raisonSociale, noLIFNR
The rows in a table are not in any order - they are
more like apples in a bag. They don't automatically
fall out of the bag in the exact order they went into
the bag.
In this particular case, the order in which the
rows were inserted is irrelevant, since you have no
IDENTITY column to record that order. The issue
here is the order or results from your SELECT query,
and that can be controlled with ORDER BY. Parallelism,
indexes, concurrency, and many other factors can change
the order in which the rows are returned, if there is no
ORDER BY clause.
Steve Kass
Drew University
David Parenteau wrote:
>Wow... I have read some part of that thread, but my
>example does'nt use a IDENTITY column. How it applies the
>same way? Does the problem is the SELECT INTO that don't
>insert the rows after the ORDER BY occurs?
>I need to give an answer to my client.
>Thanks!
>David
>
>
>thread with the subject
>
>techniques for doing
>
>MS.
>
>in message
>
>start
>
>where
>|||Ok, thanks for the *, I practice this too. The code here
was only the first part of all my code.
That table is used later to get only the record in the
middle of the entire result set (a particular page).
I need the order, as adviced here, only after that SELECT
INTO, to get always the same rows in the same page (row
460 to 469 to get the page 46 for example)
Thanks a lot of your help all you guys!

>--Original Message--
>My understanding is that the guaranteed technique
involves the creation of a
>temp table with an identity column and the use of an
insert/select
>statement. However, the topic of the referenced thread
is not exactly the
>same as the issue you are experiencing (now that I read
the post a bit more
>carefully). Bob's post is actually more appropriate.
>I don't particularly understand why you use a temp table
when you can simply
>return the result set generated select/into statement
(just omit the "into"
>part). In any event, you should always consider a table
as an unordered set
>of rows and that a select statement without an order by
clause is never
>guaranteed to return the rows in any consistent order. I
believe that is
>your fundamental flaw. In addition, I also find fault
with the use of the
>asterisk ("*"), especially if this is production code.
Both of these issues
>are frequently discussed in the newsgroup.
>"David Parenteau" <david.parenteau@.compuware.com> wrote
in message
>news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...|||Well that clarifies things. Usually questions about a particular technique
generate "better" answers when the reason driving its usage is included.
Perhaps you will find the following link useful:
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:072801c503b7$34654e00$a401280a@.phx.gbl...
> Ok, thanks for the *, I practice this too. The code here
> was only the first part of all my code.
> That table is used later to get only the record in the
> middle of the entire result set (a particular page).
> I need the order, as adviced here, only after that SELECT
> INTO, to get always the same rows in the same page (row
> 460 to 469 to get the page 46 for example)
> Thanks a lot of your help all you guys!
>
> involves the creation of a
> insert/select
> is not exactly the
> the post a bit more
> when you can simply
> (just omit the "into"
> as an unordered set
> clause is never
> believe that is
> with the use of the
> Both of these issues
> in message
>|||Thanks, I will go to that page :)
David
>--Original Message--
>slipped ... and that link is
http://www.aspfaq.com/show.asp?id=2120
>"Scott Morris" <bogus@.bogus.com> wrote in message
>news:eKW$No7AFHA.1400@.TK2MSFTNGP11.phx.gbl...
particular
>technique
usage is included.
in message
here
SELECT
(row
thread
read
table
table
by
order. I
code.
wrote
>
>.
>

intermittent openquery error "..The OLE DB provider MSDASQL indicates that the object has

Help,

I am running a pass through query to oracle from SQL server 2000 as
follows;

select * from openquery(nbsp, 'select * from FND_FLEX_VALUES')

I have run this query through both DTS and the query analyzer and get
the foloowing error;

Server: Msg 7357, Level 16, State 2, Line 3
Could not process object 'select * from FND_FLEX_VALUES'. The OLE DB
provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to
process object, since the object has no columnsProviderName='MSDASQL',
Query=select * from FND_FLEX_VALUES'].

The really strange thing is, I'll get this error the first time I
execute the query but if I execute it immeadiatley after it will run
fine.

Any help would be most appreciated!

Cheers"Kevin" <kevin.morrell@.nbs.nhs.uk> wrote in message
news:3fff8324.0409150032.783cfd99@.posting.google.c om...
> Help,
> I am running a pass through query to oracle from SQL server 2000 as
> follows;
> select * from openquery(nbsp, 'select * from FND_FLEX_VALUES')
> I have run this query through both DTS and the query analyzer and get
> the foloowing error;
> Server: Msg 7357, Level 16, State 2, Line 3
> Could not process object 'select * from FND_FLEX_VALUES'. The OLE DB
> provider 'MSDASQL' indicates that the object has no columns.
> OLE DB error trace [Non-interface error: OLE DB provider unable to
> process object, since the object has no columnsProviderName='MSDASQL',
> Query=select * from FND_FLEX_VALUES'].
> The really strange thing is, I'll get this error the first time I
> execute the query but if I execute it immeadiatley after it will run
> fine.
> Any help would be most appreciated!
> Cheers

There are a couple of KB articles for error 7357, but they only relate to
situations where the query doesn't return a result set, which doesn't seem
to be the case with your query:

http://support.microsoft.com/defaul...8&Product=sql2k
http://support.microsoft.com/defaul...9&Product=sql2k

Is there some reason why you're using the ODBC OLE DB provider instead of
the Oracle one (MSDAORA)? You might want to try the Oracle one to see if
that makes a difference, and upgrading MDAC to the latest version might also
be worth a try.

Simon

Intermittent issue with outer joins on subqueries

Hello, we are running into an issue which seems to be a bug in the way
MSSQL joins on subqueries. The below query is run daily by a scheduled
task and usually runs correctly. About once a week the query produces
a resultset that makes it appear that the outer join on the subquery
(alias: POINFO) was changed to an inner join. The resultset has only a
percentage of the records it should have as a result. Are there any
know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
problem happens on a Win2000 server. Thanks.
SELECT *
FROM rc.dbo.LineItems LI
INNER JOIN rc.dbo.orders O
ON O.guidPK = LI.orderguidPK
INNER JOIN decode DECODE
ON O.status = DECODE.status
INNER JOIN products P
ON LI.productPK = P.PK
/* The below join periodically seems to become an inner join
*/
LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
from purchase_orders PO
INNER JOIN purchase_orders_lines POL
ON PO.PK = POL.purchaseorderPK
INNER JOIN fulfillment_products FP
ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
INNER JOIN I18NManufacturer MAN
ON FP.manPK = MAN.PK
INNER JOIN suppliers S
ON FP.supplierPK = SM.PK
WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
ON O.order_number = POINFO.order_number AND LI.productPK =
POINFO.productPK
WHERE O.order_type <> 1 and O.active=1
and O.status between 2 and 32
Can you capture the plan when the bad result happens, and compare it to the
normal plan?
(In other words, does it happen frequently enough that if you tried, you
could reproduce the problem in Query Analyzer?)
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK =
> POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32
|||I would be much more likely to think the WHERE clause is limiting the rows
'unexpectedly'. Perhaps you should set up an audit trail whereby you store
the COUNT(*) of the entire SELECT as well as the COUNT(*) for the SELECT
without the join to POINFO by itself into a table each time this process is
run (with enough identifying information added to ensure you can pinpoint
the rows for each run). This will help you see if you are affecting fewer
rows with the main query simply because of the WHERE clause or is the LOJ
magically translating into an INNER JOIN. That will give you definitive
evidence to take to Microsoft if it is a bug too (which I will definitely
admit isn't out of the question). :-)
Doing a quick check of post-SP4 hotfixes, see if this one applies
http://support.microsoft.com/kb/892310/
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK =
> POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32

Intermittent issue with outer joins on subqueries

Hello, we are running into an issue which seems to be a bug in the way
MSSQL joins on subqueries. The below query is run daily by a scheduled
task and usually runs correctly. About once a week the query produces
a resultset that makes it appear that the outer join on the subquery
(alias: POINFO) was changed to an inner join. The resultset has only a
percentage of the records it should have as a result. Are there any
know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
problem happens on a Win2000 server. Thanks.
SELECT *
FROM rc.dbo.LineItems LI
INNER JOIN rc.dbo.orders O
ON O.guidPK = LI.orderguidPK
INNER JOIN decode DECODE
ON O.status = DECODE.status
INNER JOIN products P
ON LI.productPK = P.PK
/* The below join periodically seems to become an inner join
*/
LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
from purchase_orders PO
INNER JOIN purchase_orders_lines POL
ON PO.PK = POL.purchaseorderPK
INNER JOIN fulfillment_products FP
ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
INNER JOIN I18NManufacturer MAN
ON FP.manPK = MAN.PK
INNER JOIN suppliers S
ON FP.supplierPK = SM.PK
WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
ON O.order_number = POINFO.order_number AND LI.productPK = POINFO.productPK
WHERE O.order_type <> 1 and O.active=1
and O.status between 2 and 32Can you capture the plan when the bad result happens, and compare it to the
normal plan?
(In other words, does it happen frequently enough that if you tried, you
could reproduce the problem in Query Analyzer?)
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK => POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32|||I would be much more likely to think the WHERE clause is limiting the rows
'unexpectedly'. Perhaps you should set up an audit trail whereby you store
the COUNT(*) of the entire SELECT as well as the COUNT(*) for the SELECT
without the join to POINFO by itself into a table each time this process is
run (with enough identifying information added to ensure you can pinpoint
the rows for each run). This will help you see if you are affecting fewer
rows with the main query simply because of the WHERE clause or is the LOJ
magically translating into an INNER JOIN. That will give you definitive
evidence to take to Microsoft if it is a bug too (which I will definitely
admit isn't out of the question). :-)
Doing a quick check of post-SP4 hotfixes, see if this one applies
http://support.microsoft.com/kb/892310/
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jesse Hogan" <JesseHogan0@.gmail.com> wrote in message
news:049ac85e-1cde-4fd9-8755-0b03494302d2@.1g2000hsl.googlegroups.com...
> Hello, we are running into an issue which seems to be a bug in the way
> MSSQL joins on subqueries. The below query is run daily by a scheduled
> task and usually runs correctly. About once a week the query produces
> a resultset that makes it appear that the outer join on the subquery
> (alias: POINFO) was changed to an inner join. The resultset has only a
> percentage of the records it should have as a result. Are there any
> know issues with MSSQL 2000 SP4 that are know to cause this. BTW, the
> problem happens on a Win2000 server. Thanks.
> SELECT *
> FROM rc.dbo.LineItems LI
> INNER JOIN rc.dbo.orders O
> ON O.guidPK = LI.orderguidPK
> INNER JOIN decode DECODE
> ON O.status = DECODE.status
> INNER JOIN products P
> ON LI.productPK = P.PK
> /* The below join periodically seems to become an inner join
> */
> LEFT OUTER JOIN (SELECT PO.OrderNumber, FP.ProductNumber
> from purchase_orders PO
> INNER JOIN purchase_orders_lines POL
> ON PO.PK = POL.purchaseorderPK
> INNER JOIN fulfillment_products FP
> ON POL.intProductID = FP.PK AND PO.supplierPK = FP.supplierPK
> INNER JOIN I18NManufacturer MAN
> ON FP.manPK = MAN.PK
> INNER JOIN suppliers S
> ON FP.supplierPK = SM.PK
> WHERE PO.status <>9 AND MAN.locale = 1041) AS POINFO
> ON O.order_number = POINFO.order_number AND LI.productPK => POINFO.productPK
> WHERE O.order_type <> 1 and O.active=1
> and O.status between 2 and 32

Intermittent connection

I am running SQL2000 on windows2003. I often connect to the database through
the SQL Server Enterprise Manager on a client machine to administer it. The
strange thing is sometimes I can connect to this database but other times c
annot. THe error message is
"SQL does not exist or not known to be running". I have checked the server s
tatus at the host terminal and it is running.
Anyone knows what's the problem for such intermitent connection? Network pro
b? SQL registration at the client side? ODBC setting?
Someone suggests that it may be because the host and the client are sitting
on different domain in the network. Is this true?Hi,
Due the problem situation, can you run a ping comand to the SQL Server
machine (Ping <ipaddress of sql server> ). If a request time out occurs
contact the "Network admin".
THis type of errors will come only if there is a slow network.
Thanks
Hari
MCDBA
"Tay" <Tay@.discussions.microsoft.com> wrote in message
news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> I am running SQL2000 on windows2003. I often connect to the database
through the SQL Server Enterprise Manager on a client machine to administer
it. The strange thing is sometimes I can connect to this database but other
times cannot. THe error message is "SQL does not exist or not known to be
running". I have checked the server status at the host terminal and it is
running.
> Anyone knows what's the problem for such intermitent connection? Network
prob? SQL registration at the client side? ODBC setting?
> Someone suggests that it may be because the host and the client are
sitting on different domain in the network. Is this true?|||Hi, Hari.
I can ping the sql server machine from the client terminal. But the client a
nd host are in different domain. Is this the problem?
Thanks.
Tay
"Hari Prasad" wrote:

> Hi,
> Due the problem situation, can you run a ping comand to the SQL Server
> machine (Ping <ipaddress of sql server> ). If a request time out occurs
> contact the "Network admin".
> THis type of errors will come only if there is a slow network.
>
> --
> Thanks
> Hari
> MCDBA
>
> "Tay" <Tay@.discussions.microsoft.com> wrote in message
> news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> through the SQL Server Enterprise Manager on a client machine to administe
r
> it. The strange thing is sometimes I can connect to this database but othe
r
> times cannot. THe error message is "SQL does not exist or not known to be
> running". I have checked the server status at the host terminal and it is
> running.
> prob? SQL registration at the client side? ODBC setting?
> sitting on different domain in the network. Is this true?
>
>|||HI,
If you have a Trust relation ship between both domains, then it should not
be a issue at all.
Thanks
Hari
MCDBA
"Tay" <Tay@.discussions.microsoft.com> wrote in message
news:90E8545E-343F-4061-8DA6-E5AACB0F385F@.microsoft.com...
> Hi, Hari.
> I can ping the sql server machine from the client terminal. But the client
and host are in different domain. Is this the problem?[vbcol=seagreen]
> Thanks.
> Tay
> "Hari Prasad" wrote:
>
administer[vbcol=seagreen]
other[vbcol=seagreen]
be[vbcol=seagreen]
is[vbcol=seagreen]
Network[vbcol=seagreen]|||hi
I just found out that both the host and the client did not join any domain b
ut in the same workgroup. I can successfully search for the sqlserver after
I login to a shared folder from my client terminal. It seems like the client
need to establish the netw
ork path to the host before it can find the sqlserver.
Any way to avoid logging in yet still can locate the sqlserver?
Thanks.
"Hari Prasad" wrote:

> HI,
> If you have a Trust relation ship between both domains, then it should not
> be a issue at all.
> --
> Thanks
> Hari
> MCDBA
> "Tay" <Tay@.discussions.microsoft.com> wrote in message
> news:90E8545E-343F-4061-8DA6-E5AACB0F385F@.microsoft.com...
> and host are in different domain. Is this the problem?
> administer
> other
> be
> is
> Network
>
>sql

intermittent connectivity issues in virtual server images

Hello,
I have a few virtual server images running for development purposes and
have been experiencing intermittent connectivity issues for quite a
while. First of all my configuration...
VPC image 1:
Windows Server 2003 Enterprise Edition SP1
Being used as domain controller and DNS server for all other images
VPC image 2:
Windows Server 2003 Enterprise Edition SP1
SQL Server Enterprise Edition 2000 SP3 (8.00.760)
VPC image 3:
Windows Server 2003 Enterprise Edition SP1
IIS 6.0
.NET Framework 1.0 (for ASP.NET stuff)
All these images are running on a Pentium M 1.75ghz with 2GB of RAM
(which seems to be enough horsepower to handle everything) and using
Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
On to my problem...
Intermittently, an ASP.NET application I wrote would error out upon
trying to establish a connection to the SQL Server (using
System.Data.SQLClient). I return the error...
"System.Data.SqlClient.SqlException: SQL Server does not exist or
access denied"
...the stack trace doesn't provide much insight as to why. This error
will generally occur multiple times until an attempt succeeds and then
it appears to keep working as long as I don't leave the system idle too
long (i.e. twenty minutes or so).
This could of course be caused by numorous virtual networking problems
but we can eliminate all of that (I think) by this second issue.
Today I attempted to import some data via the DTS Import/Export Wizard
in Enterprise Manager, as soon as I attempt to click Next on the
"Choose a Data Source" dialog I receive...
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Context: Error during initialization of the provider.
This is occuring while running EM on the Virtual Image containing SQL
Server. Someone with more knowledge of how SQL Server establishes
named pipes connections may be able to determine if this eliminates the
virtual network as a culpret or not (I'm not positive it does).
Anyway, ideas?
Thanks,
Ben.
PS I should add, while the errors are being thrown I am still able to
ping the sql virtual image from my iis virtual image. I also have
another ASP.NET app (MS CRM) that is able to communicate with the
server the whole time and I am able to establish a Query Analyzer
connection without fail (using both integrated and sql server
authentication). I'm about stumped.<ben.usenet.alias@.gmail.com> wrote in message
news:1146078164.773174.237320@.j33g2000cwa.googlegroups.com...
> Hello,
> I have a few virtual server images running for development purposes and
> have been experiencing intermittent connectivity issues for quite a
> while. First of all my configuration...
> VPC image 1:
> Windows Server 2003 Enterprise Edition SP1
> Being used as domain controller and DNS server for all other images
> VPC image 2:
> Windows Server 2003 Enterprise Edition SP1
> SQL Server Enterprise Edition 2000 SP3 (8.00.760)
> VPC image 3:
> Windows Server 2003 Enterprise Edition SP1
> IIS 6.0
> .NET Framework 1.0 (for ASP.NET stuff)
> All these images are running on a Pentium M 1.75ghz with 2GB of RAM
> (which seems to be enough horsepower to handle everything) and using
> Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
> On to my problem...
> Intermittently, an ASP.NET application I wrote would error out upon
> trying to establish a connection to the SQL Server (using
> System.Data.SQLClient). I return the error...
> "System.Data.SqlClient.SqlException: SQL Server does not exist or
> access denied"
> ...the stack trace doesn't provide much insight as to why. This error
> will generally occur multiple times until an attempt succeeds and then
> it appears to keep working as long as I don't leave the system idle too
> long (i.e. twenty minutes or so).
> This could of course be caused by numorous virtual networking problems
> but we can eliminate all of that (I think) by this second issue.
> Today I attempted to import some data via the DTS Import/Export Wizard
> in Enterprise Manager, as soon as I attempt to click Next on the
> "Choose a Data Source" dialog I receive...
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serv
er
> does not exist or access denied.
> Context: Error during initialization of the provider.
> This is occuring while running EM on the Virtual Image containing SQL
> Server. Someone with more knowledge of how SQL Server establishes
> named pipes connections may be able to determine if this eliminates the
> virtual network as a culpret or not (I'm not positive it does).
>
What network are the VM's bound to? Your host OS's wireless network?
Here's what I do
1) Install the Loopback Adapter in the Host OS|||<ben.usenet.alias@.gmail.com> wrote in message
news:1146078164.773174.237320@.j33g2000cwa.googlegroups.com...
> Hello,
> I have a few virtual server images running for development purposes and
> have been experiencing intermittent connectivity issues for quite a
> while. First of all my configuration...
> VPC image 1:
> Windows Server 2003 Enterprise Edition SP1
> Being used as domain controller and DNS server for all other images
> VPC image 2:
> Windows Server 2003 Enterprise Edition SP1
> SQL Server Enterprise Edition 2000 SP3 (8.00.760)
> VPC image 3:
> Windows Server 2003 Enterprise Edition SP1
> IIS 6.0
> .NET Framework 1.0 (for ASP.NET stuff)
> All these images are running on a Pentium M 1.75ghz with 2GB of RAM
> (which seems to be enough horsepower to handle everything) and using
> Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
> On to my problem...
> Intermittently, an ASP.NET application I wrote would error out upon
> trying to establish a connection to the SQL Server (using
> System.Data.SQLClient). I return the error...
> "System.Data.SqlClient.SqlException: SQL Server does not exist or
> access denied"
> ...the stack trace doesn't provide much insight as to why. This error
> will generally occur multiple times until an attempt succeeds and then
> it appears to keep working as long as I don't leave the system idle too
> long (i.e. twenty minutes or so).
> This could of course be caused by numorous virtual networking problems
> but we can eliminate all of that (I think) by this second issue.
> Today I attempted to import some data via the DTS Import/Export Wizard
> in Enterprise Manager, as soon as I attempt to click Next on the
> "Choose a Data Source" dialog I receive...
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serv
er
> does not exist or access denied.
> Context: Error during initialization of the provider.
> This is occuring while running EM on the Virtual Image containing SQL
> Server. Someone with more knowledge of how SQL Server establishes
> named pipes connections may be able to determine if this eliminates the
> virtual network as a culpret or not (I'm not positive it does).
> Anyway, ideas?
Oops.
Here's what I do to connect my VM's to each other and my host OS.
-Install the Looblack Adapter in the host OS
How to install the Microsoft Loopback adapter in Windows XP
http://support.microsoft.com/defaul...kb;en-us;839013
Assign a private IP address to the Loopback adapter. I use 10.240.0.1 mask
255.255.255.0
Then in Virtual Server add a Virtual Network for that adapter and enable the
virtual server DHCP server and let DHCP assign a range of the 10.240.0.X
network, reserving some address for VM's with fixed IP's.
David|||I like that idea. I've been toying around with the idea of isolating
my test environment further (putting it on it's own subnet) but I still
do need to connect my virtual servers to the internet for some items.
I'm considering adding a second virtual NIC on my virtual domain
controller. I'd run the internal against the loopback adapter (as you
described) and the second virtual NIC against my laptop's wired NIC.
Then I'd throw ISA on the virtual domain controller to route outside
traffic. Everything on the inside would exist on 10.x.x.x addys with a
255.255.255.0 netmask. The DNS on my virtual domain controller already
forwards unhandled requests to the corporate DNS here and that would
continue as is. The only downside I see to this is that I wouldn't be
able to hit my virtual server's web service from my laptop (I like to
point my laptop's browser at my virtual server running IIS to test
client functionality).
As to my original problem, I think I'm close to a solution (unrelated
to this conversation). I'll post more when I have something concrete
so that other's can benefit from it.
Ben.

intermittent connectivity issues in virtual server images

Hello,
I have a few virtual server images running for development purposes and
have been experiencing intermittent connectivity issues for quite a
while. First of all my configuration...
VPC image 1:
Windows Server 2003 Enterprise Edition SP1
Being used as domain controller and DNS server for all other images
VPC image 2:
Windows Server 2003 Enterprise Edition SP1
SQL Server Enterprise Edition 2000 SP3 (8.00.760)
VPC image 3:
Windows Server 2003 Enterprise Edition SP1
IIS 6.0
.NET Framework 1.0 (for ASP.NET stuff)
All these images are running on a Pentium M 1.75ghz with 2GB of RAM
(which seems to be enough horsepower to handle everything) and using
Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
On to my problem...
Intermittently, an ASP.NET application I wrote would error out upon
trying to establish a connection to the SQL Server (using
System.Data.SQLClient). I return the error...
"System.Data.SqlClient.SqlException: SQL Server does not exist or
access denied"
...the stack trace doesn't provide much insight as to why. This error
will generally occur multiple times until an attempt succeeds and then
it appears to keep working as long as I don't leave the system idle too
long (i.e. twenty minutes or so).
This could of course be caused by numorous virtual networking problems
but we can eliminate all of that (I think) by this second issue.
Today I attempted to import some data via the DTS Import/Export Wizard
in Enterprise Manager, as soon as I attempt to click Next on the
"Choose a Data Source" dialog I receive...
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Context: Error during initialization of the provider.
This is occuring while running EM on the Virtual Image containing SQL
Server. Someone with more knowledge of how SQL Server establishes
named pipes connections may be able to determine if this eliminates the
virtual network as a culpret or not (I'm not positive it does).
Anyway, ideas?
Thanks,
Ben.
PS I should add, while the errors are being thrown I am still able to
ping the sql virtual image from my iis virtual image. I also have
another ASP.NET app (MS CRM) that is able to communicate with the
server the whole time and I am able to establish a Query Analyzer
connection without fail (using both integrated and sql server
authentication). I'm about stumped.<ben.usenet.alias@.gmail.com> wrote in message
news:1146078164.773174.237320@.j33g2000cwa.googlegroups.com...
> Hello,
> I have a few virtual server images running for development purposes and
> have been experiencing intermittent connectivity issues for quite a
> while. First of all my configuration...
> VPC image 1:
> Windows Server 2003 Enterprise Edition SP1
> Being used as domain controller and DNS server for all other images
> VPC image 2:
> Windows Server 2003 Enterprise Edition SP1
> SQL Server Enterprise Edition 2000 SP3 (8.00.760)
> VPC image 3:
> Windows Server 2003 Enterprise Edition SP1
> IIS 6.0
> .NET Framework 1.0 (for ASP.NET stuff)
> All these images are running on a Pentium M 1.75ghz with 2GB of RAM
> (which seems to be enough horsepower to handle everything) and using
> Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
> On to my problem...
> Intermittently, an ASP.NET application I wrote would error out upon
> trying to establish a connection to the SQL Server (using
> System.Data.SQLClient). I return the error...
> "System.Data.SqlClient.SqlException: SQL Server does not exist or
> access denied"
> ...the stack trace doesn't provide much insight as to why. This error
> will generally occur multiple times until an attempt succeeds and then
> it appears to keep working as long as I don't leave the system idle too
> long (i.e. twenty minutes or so).
> This could of course be caused by numorous virtual networking problems
> but we can eliminate all of that (I think) by this second issue.
> Today I attempted to import some data via the DTS Import/Export Wizard
> in Enterprise Manager, as soon as I attempt to click Next on the
> "Choose a Data Source" dialog I receive...
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
> does not exist or access denied.
> Context: Error during initialization of the provider.
> This is occuring while running EM on the Virtual Image containing SQL
> Server. Someone with more knowledge of how SQL Server establishes
> named pipes connections may be able to determine if this eliminates the
> virtual network as a culpret or not (I'm not positive it does).
>
What network are the VM's bound to? Your host OS's wireless network?
Here's what I do
1) Install the Loopback Adapter in the Host OS|||<ben.usenet.alias@.gmail.com> wrote in message
news:1146078164.773174.237320@.j33g2000cwa.googlegroups.com...
> Hello,
> I have a few virtual server images running for development purposes and
> have been experiencing intermittent connectivity issues for quite a
> while. First of all my configuration...
> VPC image 1:
> Windows Server 2003 Enterprise Edition SP1
> Being used as domain controller and DNS server for all other images
> VPC image 2:
> Windows Server 2003 Enterprise Edition SP1
> SQL Server Enterprise Edition 2000 SP3 (8.00.760)
> VPC image 3:
> Windows Server 2003 Enterprise Edition SP1
> IIS 6.0
> .NET Framework 1.0 (for ASP.NET stuff)
> All these images are running on a Pentium M 1.75ghz with 2GB of RAM
> (which seems to be enough horsepower to handle everything) and using
> Microsoft Virtual PC 5.3.582.27 under Windows XP Pro SP2.
> On to my problem...
> Intermittently, an ASP.NET application I wrote would error out upon
> trying to establish a connection to the SQL Server (using
> System.Data.SQLClient). I return the error...
> "System.Data.SqlClient.SqlException: SQL Server does not exist or
> access denied"
> ...the stack trace doesn't provide much insight as to why. This error
> will generally occur multiple times until an attempt succeeds and then
> it appears to keep working as long as I don't leave the system idle too
> long (i.e. twenty minutes or so).
> This could of course be caused by numorous virtual networking problems
> but we can eliminate all of that (I think) by this second issue.
> Today I attempted to import some data via the DTS Import/Export Wizard
> in Enterprise Manager, as soon as I attempt to click Next on the
> "Choose a Data Source" dialog I receive...
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
> does not exist or access denied.
> Context: Error during initialization of the provider.
> This is occuring while running EM on the Virtual Image containing SQL
> Server. Someone with more knowledge of how SQL Server establishes
> named pipes connections may be able to determine if this eliminates the
> virtual network as a culpret or not (I'm not positive it does).
> Anyway, ideas?
Oops.
Here's what I do to connect my VM's to each other and my host OS.
-Install the Looblack Adapter in the host OS
How to install the Microsoft Loopback adapter in Windows XP
http://support.microsoft.com/default.aspx?scid=kb;en-us;839013
Assign a private IP address to the Loopback adapter. I use 10.240.0.1 mask
255.255.255.0
Then in Virtual Server add a Virtual Network for that adapter and enable the
virtual server DHCP server and let DHCP assign a range of the 10.240.0.X
network, reserving some address for VM's with fixed IP's.
David|||I like that idea. I've been toying around with the idea of isolating
my test environment further (putting it on it's own subnet) but I still
do need to connect my virtual servers to the internet for some items.
I'm considering adding a second virtual NIC on my virtual domain
controller. I'd run the internal against the loopback adapter (as you
described) and the second virtual NIC against my laptop's wired NIC.
Then I'd throw ISA on the virtual domain controller to route outside
traffic. Everything on the inside would exist on 10.x.x.x addys with a
255.255.255.0 netmask. The DNS on my virtual domain controller already
forwards unhandled requests to the corporate DNS here and that would
continue as is. The only downside I see to this is that I wouldn't be
able to hit my virtual server's web service from my laptop (I like to
point my laptop's browser at my virtual server running IIS to test
client functionality).
As to my original problem, I think I'm close to a solution (unrelated
to this conversation). I'll post more when I have something concrete
so that other's can benefit from it.
Ben.

Intermittent connection

I am running SQL2000 on windows2003. I often connect to the database through the SQL Server Enterprise Manager on a client machine to administer it. The strange thing is sometimes I can connect to this database but other times cannot. THe error message is
"SQL does not exist or not known to be running". I have checked the server status at the host terminal and it is running.
Anyone knows what's the problem for such intermitent connection? Network prob? SQL registration at the client side? ODBC setting?
Someone suggests that it may be because the host and the client are sitting on different domain in the network. Is this true?
Hi,
Due the problem situation, can you run a ping comand to the SQL Server
machine (Ping <ipaddress of sql server>). If a request time out occurs
contact the "Network admin".
THis type of errors will come only if there is a slow network.
Thanks
Hari
MCDBA
"Tay" <Tay@.discussions.microsoft.com> wrote in message
news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> I am running SQL2000 on windows2003. I often connect to the database
through the SQL Server Enterprise Manager on a client machine to administer
it. The strange thing is sometimes I can connect to this database but other
times cannot. THe error message is "SQL does not exist or not known to be
running". I have checked the server status at the host terminal and it is
running.
> Anyone knows what's the problem for such intermitent connection? Network
prob? SQL registration at the client side? ODBC setting?
> Someone suggests that it may be because the host and the client are
sitting on different domain in the network. Is this true?
|||Hi, Hari.
I can ping the sql server machine from the client terminal. But the client and host are in different domain. Is this the problem?
Thanks.
Tay
"Hari Prasad" wrote:

> Hi,
> Due the problem situation, can you run a ping comand to the SQL Server
> machine (Ping <ipaddress of sql server>). If a request time out occurs
> contact the "Network admin".
> THis type of errors will come only if there is a slow network.
>
> --
> Thanks
> Hari
> MCDBA
>
> "Tay" <Tay@.discussions.microsoft.com> wrote in message
> news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> through the SQL Server Enterprise Manager on a client machine to administer
> it. The strange thing is sometimes I can connect to this database but other
> times cannot. THe error message is "SQL does not exist or not known to be
> running". I have checked the server status at the host terminal and it is
> running.
> prob? SQL registration at the client side? ODBC setting?
> sitting on different domain in the network. Is this true?
>
>
|||HI,
If you have a Trust relation ship between both domains, then it should not
be a issue at all.
Thanks
Hari
MCDBA
"Tay" <Tay@.discussions.microsoft.com> wrote in message
news:90E8545E-343F-4061-8DA6-E5AACB0F385F@.microsoft.com...
> Hi, Hari.
> I can ping the sql server machine from the client terminal. But the client
and host are in different domain. Is this the problem?[vbcol=seagreen]
> Thanks.
> Tay
> "Hari Prasad" wrote:
administer[vbcol=seagreen]
other[vbcol=seagreen]
be[vbcol=seagreen]
is[vbcol=seagreen]
Network[vbcol=seagreen]
|||hi
I just found out that both the host and the client did not join any domain but in the same workgroup. I can successfully search for the sqlserver after I login to a shared folder from my client terminal. It seems like the client need to establish the netw
ork path to the host before it can find the sqlserver.
Any way to avoid logging in yet still can locate the sqlserver?
Thanks.
"Hari Prasad" wrote:

> HI,
> If you have a Trust relation ship between both domains, then it should not
> be a issue at all.
> --
> Thanks
> Hari
> MCDBA
> "Tay" <Tay@.discussions.microsoft.com> wrote in message
> news:90E8545E-343F-4061-8DA6-E5AACB0F385F@.microsoft.com...
> and host are in different domain. Is this the problem?
> administer
> other
> be
> is
> Network
>
>

Intermittent connection

I am running SQL2000 on windows2003. I often connect to the database through the SQL Server Enterprise Manager on a client machine to administer it. The strange thing is sometimes I can connect to this database but other times cannot. THe error message is "SQL does not exist or not known to be running". I have checked the server status at the host terminal and it is running.
Anyone knows what's the problem for such intermitent connection? Network prob? SQL registration at the client side? ODBC setting?
Someone suggests that it may be because the host and the client are sitting on different domain in the network. Is this true?Hi,
Due the problem situation, can you run a ping comand to the SQL Server
machine (Ping <ipaddress of sql server>). If a request time out occurs
contact the "Network admin".
THis type of errors will come only if there is a slow network.
Thanks
Hari
MCDBA
"Tay" <Tay@.discussions.microsoft.com> wrote in message
news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> I am running SQL2000 on windows2003. I often connect to the database
through the SQL Server Enterprise Manager on a client machine to administer
it. The strange thing is sometimes I can connect to this database but other
times cannot. THe error message is "SQL does not exist or not known to be
running". I have checked the server status at the host terminal and it is
running.
> Anyone knows what's the problem for such intermitent connection? Network
prob? SQL registration at the client side? ODBC setting?
> Someone suggests that it may be because the host and the client are
sitting on different domain in the network. Is this true?|||Hi, Hari.
I can ping the sql server machine from the client terminal. But the client and host are in different domain. Is this the problem?
Thanks.
Tay
"Hari Prasad" wrote:
> Hi,
> Due the problem situation, can you run a ping comand to the SQL Server
> machine (Ping <ipaddress of sql server>). If a request time out occurs
> contact the "Network admin".
> THis type of errors will come only if there is a slow network.
>
> --
> Thanks
> Hari
> MCDBA
>
> "Tay" <Tay@.discussions.microsoft.com> wrote in message
> news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> > I am running SQL2000 on windows2003. I often connect to the database
> through the SQL Server Enterprise Manager on a client machine to administer
> it. The strange thing is sometimes I can connect to this database but other
> times cannot. THe error message is "SQL does not exist or not known to be
> running". I have checked the server status at the host terminal and it is
> running.
> >
> > Anyone knows what's the problem for such intermitent connection? Network
> prob? SQL registration at the client side? ODBC setting?
> >
> > Someone suggests that it may be because the host and the client are
> sitting on different domain in the network. Is this true?
>
>|||HI,
If you have a Trust relation ship between both domains, then it should not
be a issue at all.
--
Thanks
Hari
MCDBA
"Tay" <Tay@.discussions.microsoft.com> wrote in message
news:90E8545E-343F-4061-8DA6-E5AACB0F385F@.microsoft.com...
> Hi, Hari.
> I can ping the sql server machine from the client terminal. But the client
and host are in different domain. Is this the problem?
> Thanks.
> Tay
> "Hari Prasad" wrote:
> > Hi,
> >
> > Due the problem situation, can you run a ping comand to the SQL Server
> > machine (Ping <ipaddress of sql server>). If a request time out occurs
> > contact the "Network admin".
> >
> > THis type of errors will come only if there is a slow network.
> >
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > "Tay" <Tay@.discussions.microsoft.com> wrote in message
> > news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> > > I am running SQL2000 on windows2003. I often connect to the database
> > through the SQL Server Enterprise Manager on a client machine to
administer
> > it. The strange thing is sometimes I can connect to this database but
other
> > times cannot. THe error message is "SQL does not exist or not known to
be
> > running". I have checked the server status at the host terminal and it
is
> > running.
> > >
> > > Anyone knows what's the problem for such intermitent connection?
Network
> > prob? SQL registration at the client side? ODBC setting?
> > >
> > > Someone suggests that it may be because the host and the client are
> > sitting on different domain in the network. Is this true?
> >
> >
> >|||hi
I just found out that both the host and the client did not join any domain but in the same workgroup. I can successfully search for the sqlserver after I login to a shared folder from my client terminal. It seems like the client need to establish the network path to the host before it can find the sqlserver.
Any way to avoid logging in yet still can locate the sqlserver?
Thanks.
"Hari Prasad" wrote:
> HI,
> If you have a Trust relation ship between both domains, then it should not
> be a issue at all.
> --
> Thanks
> Hari
> MCDBA
> "Tay" <Tay@.discussions.microsoft.com> wrote in message
> news:90E8545E-343F-4061-8DA6-E5AACB0F385F@.microsoft.com...
> > Hi, Hari.
> >
> > I can ping the sql server machine from the client terminal. But the client
> and host are in different domain. Is this the problem?
> >
> > Thanks.
> > Tay
> >
> > "Hari Prasad" wrote:
> >
> > > Hi,
> > >
> > > Due the problem situation, can you run a ping comand to the SQL Server
> > > machine (Ping <ipaddress of sql server>). If a request time out occurs
> > > contact the "Network admin".
> > >
> > > THis type of errors will come only if there is a slow network.
> > >
> > >
> > > --
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > > "Tay" <Tay@.discussions.microsoft.com> wrote in message
> > > news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> > > > I am running SQL2000 on windows2003. I often connect to the database
> > > through the SQL Server Enterprise Manager on a client machine to
> administer
> > > it. The strange thing is sometimes I can connect to this database but
> other
> > > times cannot. THe error message is "SQL does not exist or not known to
> be
> > > running". I have checked the server status at the host terminal and it
> is
> > > running.
> > > >
> > > > Anyone knows what's the problem for such intermitent connection?
> Network
> > > prob? SQL registration at the client side? ODBC setting?
> > > >
> > > > Someone suggests that it may be because the host and the client are
> > > sitting on different domain in the network. Is this true?
> > >
> > >
> > >
>
>

Intermittent connection

I am running SQL2000 on windows2003. I often connect to the database through
the SQL Server Enterprise Manager on a client machine to administer it. The
strange thing is sometimes I can connect to this database but other times c
annot. THe error message is
"SQL does not exist or not known to be running". I have checked the server s
tatus at the host terminal and it is running.
Anyone knows what's the problem for such intermitent connection? Network pro
b? SQL registration at the client side? ODBC setting?
Someone suggests that it may be because the host and the client are sitting
on different domain in the network. Is this true?Hi,
Due the problem situation, can you run a ping comand to the SQL Server
machine (Ping <ipaddress of sql server> ). If a request time out occurs
contact the "Network admin".
THis type of errors will come only if there is a slow network.
Thanks
Hari
MCDBA
"Tay" <Tay@.discussions.microsoft.com> wrote in message
news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> I am running SQL2000 on windows2003. I often connect to the database
through the SQL Server Enterprise Manager on a client machine to administer
it. The strange thing is sometimes I can connect to this database but other
times cannot. THe error message is "SQL does not exist or not known to be
running". I have checked the server status at the host terminal and it is
running.
> Anyone knows what's the problem for such intermitent connection? Network
prob? SQL registration at the client side? ODBC setting?
> Someone suggests that it may be because the host and the client are
sitting on different domain in the network. Is this true?|||Hi, Hari.
I can ping the sql server machine from the client terminal. But the client a
nd host are in different domain. Is this the problem?
Thanks.
Tay
"Hari Prasad" wrote:

> Hi,
> Due the problem situation, can you run a ping comand to the SQL Server
> machine (Ping <ipaddress of sql server> ). If a request time out occurs
> contact the "Network admin".
> THis type of errors will come only if there is a slow network.
>
> --
> Thanks
> Hari
> MCDBA
>
> "Tay" <Tay@.discussions.microsoft.com> wrote in message
> news:FD12B805-602B-447F-BB0F-181E032CA39E@.microsoft.com...
> through the SQL Server Enterprise Manager on a client machine to administe
r
> it. The strange thing is sometimes I can connect to this database but othe
r
> times cannot. THe error message is "SQL does not exist or not known to be
> running". I have checked the server status at the host terminal and it is
> running.
> prob? SQL registration at the client side? ODBC setting?
> sitting on different domain in the network. Is this true?
>
>|||HI,
If you have a Trust relation ship between both domains, then it should not
be a issue at all.
Thanks
Hari
MCDBA
"Tay" <Tay@.discussions.microsoft.com> wrote in message
news:90E8545E-343F-4061-8DA6-E5AACB0F385F@.microsoft.com...
> Hi, Hari.
> I can ping the sql server machine from the client terminal. But the client
and host are in different domain. Is this the problem?[vbcol=seagreen]
> Thanks.
> Tay
> "Hari Prasad" wrote:
>
administer[vbcol=seagreen]
other[vbcol=seagreen]
be[vbcol=seagreen]
is[vbcol=seagreen]
Network[vbcol=seagreen]|||hi
I just found out that both the host and the client did not join any domain b
ut in the same workgroup. I can successfully search for the sqlserver after
I login to a shared folder from my client terminal. It seems like the client
need to establish the netw
ork path to the host before it can find the sqlserver.
Any way to avoid logging in yet still can locate the sqlserver?
Thanks.
"Hari Prasad" wrote:

> HI,
> If you have a Trust relation ship between both domains, then it should not
> be a issue at all.
> --
> Thanks
> Hari
> MCDBA
> "Tay" <Tay@.discussions.microsoft.com> wrote in message
> news:90E8545E-343F-4061-8DA6-E5AACB0F385F@.microsoft.com...
> and host are in different domain. Is this the problem?
> administer
> other
> be
> is
> Network
>
>sql

interesting sql login issue

Hi all,
I have a SQL 2005 server running on SERVERA. I have the management studio
installed on my desktop (Windows XP Pro SP2) and connect to the sql server
using my regular domain login account (windows auth). This worked fine until
I had to change my domain user account's password as it had expired (we have
to change it every 90 days). Now, ever since then, I have been unable to
connect to the sql server in management studio. I get:-
Login Failed for user ''. Error: 18452
If I remote into the server and try to connect to sql it works fine (I am
member of local admin group on the server)
Any ideas'http://support.microsoft.com/kb/555332
Is it possible that you have your Windows auth password hardcoded somewhere?
When worse comes to worse, you may have to drop and re-create your login on
a
SQL Server.
"param@.community.nospam" wrote:

> Hi all,
> I have a SQL 2005 server running on SERVERA. I have the management studio
> installed on my desktop (Windows XP Pro SP2) and connect to the sql server
> using my regular domain login account (windows auth). This worked fine unt
il
> I had to change my domain user account's password as it had expired (we ha
ve
> to change it every 90 days). Now, ever since then, I have been unable to
> connect to the sql server in management studio. I get:-
> Login Failed for user ''. Error: 18452
> If I remote into the server and try to connect to sql it works fine (I am
> member of local admin group on the server)
> Any ideas'
>
>|||Can you connect from the command line with sqlcmd?
It may also be worth a try of deleting the registration and
reregistering the server.
-Sue
On Wed, 21 Mar 2007 14:50:49 -0500, <param@.community.nospam>
wrote:

>Hi all,
>I have a SQL 2005 server running on SERVERA. I have the management studio
>installed on my desktop (Windows XP Pro SP2) and connect to the sql server
>using my regular domain login account (windows auth). This worked fine unti
l
>I had to change my domain user account's password as it had expired (we hav
e
>to change it every 90 days). Now, ever since then, I have been unable to
>connect to the sql server in management studio. I get:-
>Login Failed for user ''. Error: 18452
>If I remote into the server and try to connect to sql it works fine (I am
>member of local admin group on the server)
>Any ideas'
>|||What do you mean by hardcoded? I just login to my workstation using my
windows domain login and password?
I have tried dropping and re-creating the login in SQL Server.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:D1639F71-646C-4286-983F-87AF7C25360A@.microsoft.com...[vbcol=seagreen]
> http://support.microsoft.com/kb/555332
> Is it possible that you have your Windows auth password hardcoded
> somewhere?
> When worse comes to worse, you may have to drop and re-create your login
> on a
> SQL Server.
> "param@.community.nospam" wrote:
>|||If you notice the username is empty in the error message below. Could it be
a bug where management studio is not passing credentials to the server?
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:D1639F71-646C-4286-983F-87AF7C25360A@.microsoft.com...[vbcol=seagreen]
> http://support.microsoft.com/kb/555332
> Is it possible that you have your Windows auth password hardcoded
> somewhere?
> When worse comes to worse, you may have to drop and re-create your login
> on a
> SQL Server.
> "param@.community.nospam" wrote:
>

Interesting isNumeric result

Does anyone else get the following result when running this query?

Select isnumeric('4D7')

--

1

(1 row(s) affected)

Does anyone know why this would return true for numeric?

Thanks,

Ray

That is another representation of 4.0E+7 -or 40000000.0, and is, therefore, a valid numeric.

You may find this article useful.

isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390

|||Yeah, IsNumeric is pretty horrible. Vote here for a change https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=177308

Interactions between subset and join filters

I'm running into problems with a merge publication where I am trying to use
both subset (dynamic) filters and join filters. I was hoping that the join
filters operated on the slice of data carved out by the subset filters, but
that doesn't appear to be happening. Instead, the union of the results of
the subset filter on the entire database and the dynamic filter on the
entire database is what is synchronized.
For example, assume I have the following three tables with the listed
columns:
- User (Id, Name)
- Entry (Id, Date, UserId, SignatureId)
- Signature (Id, Date, Data)
The goals of the filters are as follows:
- Only retrieve the entry rows that are less than 30 days old.
- Only retrieve the signature rows that are less than 30 days old.
- Only retrieve the entry rows for that particular user.
I setup subset filters for the following tables:
Entry: ... WHERE Date > (GETDATE() - 30)
Signature: ... WHERE Date > (GETDATE() - 30)
I also setup a join filter to only get a particular user's Entries:
... INNER JOIN [dbo].[Entry] ON Entry.UserId = User.Id and
User.Name = HOST_NAME()
(Assume I pass in the User's name as the HOST_NAME.)
When I synchronize with SQL CE, I find that I do indeed get only the Entry
rows associated with that user, but I get ALL of that user's Entry rows,
regardless of the Date.
In addition, I only get Signatures that are less than 30 days old. The
problem is the old Entry rows point to Signature rows that do not exist as
they were filtered out, and I receive an error stating that "The row update
or insert cannot be reapplied due to an integrity violation" (Error 28549)
My question boils down to this:
In what order and on what subsets of data do filters get applied?
More specifically, how can I set up filters with this schema to avoid the
integrity violation problem?
Thanks for your time.
- Eli
Hi Eli,
i think you should read my post "Push referenced records" since we're facing
a similar problem. The solution for us was to eliminate the foreign key
constraints (all of them), because you can not ensure the order of
insertation when the snapshot arrives to the subscriber. In addition we tried
to extend the foreign key definitions with the "NOT FOR REPLICATION" option -
because we didn't want to drop the constraints - but this option is not
supported in CE. ... we also had to eliminate the triggers - fortunately "NOT
FOR REPLICATION" is supported for triggers.
When creating the replication you have to specify the function that is
filtering the records per subscribers (e.g. SUSER_SNAME(), HOST_NAME()). I
think you should put the "User.Name = HOST_NAME()" clause to the rowfilter.
Filtering by date is still a problem to solve. We have the same situation...
regards
pierre
|||Thanks for the reply, Pierre.
I solved the problem by eliminating the subset filters on tables that
already have join filters, and moved the filtering logic into the join
filter. For example, I added the last line to my join filter:
... INNER JOIN [dbo].[Entry] ON Entry.UserId = User.Id and
User.Name = HOST_NAME()
AND Entry.Dt > (GETDATE() - 30)
Thanks again,
- Eli
"Pierre Packard" <Pierre Packard@.discussions.microsoft.com> wrote in message
news:4A13E8A8-B3F0-415B-9560-2C20AB78B6F6@.microsoft.com...
> Hi Eli,
> i think you should read my post "Push referenced records" since we're
facing
> a similar problem. The solution for us was to eliminate the foreign key
> constraints (all of them), because you can not ensure the order of
> insertation when the snapshot arrives to the subscriber. In addition we
tried
> to extend the foreign key definitions with the "NOT FOR REPLICATION"
option -
> because we didn't want to drop the constraints - but this option is not
> supported in CE. ... we also had to eliminate the triggers - fortunately
"NOT
> FOR REPLICATION" is supported for triggers.
> When creating the replication you have to specify the function that is
> filtering the records per subscribers (e.g. SUSER_SNAME(), HOST_NAME()). I
> think you should put the "User.Name = HOST_NAME()" clause to the
rowfilter.
> Filtering by date is still a problem to solve. We have the same
situation...
> regards
> pierre
>

Interaction between SQL2k, SQL2k5, and SQLExpress

I've got both SQL2000 and SQL2005 running side-by-side and now I want to
test a ClickOnce deployment of my app with SQLExpress. Anyone been down this
road and know whether I will step on the toes of the installed servers?Hi Earl
I have not tried this but I believe you should be ok, although it is worth
trying it first in as many different scenarios as you can get!
John
"Earl" wrote:
> I've got both SQL2000 and SQL2005 running side-by-side and now I want to
> test a ClickOnce deployment of my app with SQLExpress. Anyone been down this
> road and know whether I will step on the toes of the installed servers?
>
>

Interaction between SQL2k, SQL2k5, and SQLExpress

I've got both SQL2000 and SQL2005 running side-by-side and now I want to
test a ClickOnce deployment of my app with SQLExpress. Anyone been down this
road and know whether I will step on the toes of the installed servers?Hi Earl
I have not tried this but I believe you should be ok, although it is worth
trying it first in as many different scenarios as you can get!
John
"Earl" wrote:

> I've got both SQL2000 and SQL2005 running side-by-side and now I want to
> test a ClickOnce deployment of my app with SQLExpress. Anyone been down th
is
> road and know whether I will step on the toes of the installed servers?
>
>

Integrity Checks job failing

Hi,

SQl Server 7

I have Daily User DB Integrity Checks job running daily
From past 2 days i am getting below error.

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast).
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please suggest..

Thanks in Advance
AdilHi,

SQl Server 7

I have Daily User DB Integrity Checks job running daily
From past 2 days i am getting below error.

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast).
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Please suggest..

Thanks in Advance
Adil

1. Contact your DBA with the stated information and ask for his help or
2. Fix the table and database corruption ... either by running checkdb with the repair option stated or restore from a previous backup. Checkdb is telling you that you will probably lose data by allowing it to repair the corruption ... be prepared.

Integrity Checks Failed?

We have 3 maintenance plans, each running against a different subset of
databases on the same server. Each maintenance plan runs integrity checks
once a week. According to "Job History", one of the three jobs fails every
week. Looking at the sql logs every DBCC check shows "found 0 errors and
repaired 0 errors." In the server's Application Event Log we see this vague
error message:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/2/2005
Time: 12:00:27 AM
User: N/A
Computer: FBENSQLP1
Description:
SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status: Faile
d
- Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job was
invoked by Schedule 19 (Schedule 1). The last step to run was step 1 (Step
1).
Is it possible that this is some sort of a false alert? The other two
weekly integrity checks always show successful completions.
What might cause this job to "Fail" every week?Have you looked at the report that is generated from your maintenance plan?
Possibly this report will help you determine what is wrong. If you are not
generating the report, then review your options under the "Reporting" tab
while editing your maintenance plan.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"AnthonyMunoz" <AnthonyMunoz@.discussions.microsoft.com> wrote in message
news:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...
> We have 3 maintenance plans, each running against a different subset of
> databases on the same server. Each maintenance plan runs integrity checks
> once a week. According to "Job History", one of the three jobs fails
every
> week. Looking at the sql logs every DBCC check shows "found 0 errors and
> repaired 0 errors." In the server's Application Event Log we see this
vague
> error message:
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 1/2/2005
> Time: 12:00:27 AM
> User: N/A
> Computer: FBENSQLP1
> Description:
> SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
> 'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status:
Failed
> - Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job was
> invoked by Schedule 19 (Schedule 1). The last step to run was step 1
(Step
> 1).
> Is it possible that this is some sort of a false alert? The other two
> weekly integrity checks always show successful completions.
> What might cause this job to "Fail" every week?
>|||No, something indeed happened; the problem is finding out more details as to
why. There are several logging options available to you.
1. In the maintenance plan, Reporting Tab, choose to retain information
within the MSDB database but also create Job Run report to a text file.
Oftentimes, this information will provide you with the necessary detail.
2. On the job, make sure it is configured to output additional step info on
the advanced tab of the step settings.
3. You've already discovered the SQL Server error logs and the Windows
Application event logs, also take a look at the SQL Agent error logs.
To view the maintenance history, in SQL EM, right-click one of the
maintenance plans and choose to view the history. This log will tell you
each activity, and each database, whether successfull or failure. If there
were database connectivity issues, then this information will be missing and
the text log files your created above will be the only source of information
outside of the error logs you have already discovered.
Hope this helps.
Sincerely,
Anthony Thomas
"AnthonyMunoz" <AnthonyMunoz@.discussions.microsoft.com> wrote in message
news:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...
We have 3 maintenance plans, each running against a different subset of
databases on the same server. Each maintenance plan runs integrity checks
once a week. According to "Job History", one of the three jobs fails every
week. Looking at the sql logs every DBCC check shows "found 0 errors and
repaired 0 errors." In the server's Application Event Log we see this vague
error message:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/2/2005
Time: 12:00:27 AM
User: N/A
Computer: FBENSQLP1
Description:
SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status:
Failed
- Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job was
invoked by Schedule 19 (Schedule 1). The last step to run was step 1 (Step
1).
Is it possible that this is some sort of a false alert? The other two
weekly integrity checks always show successful completions.
What might cause this job to "Fail" every week?|||You need to uncheck the checkbox in the MP wizard that states to "Fix Minor
Repairs". Then it won't attempt to put the db into single user mode and it
will operate properly. If there is a problem the job will fail and then you
can handle the issue the correct way as is best suited for your environment
.
--
Andrew J. Kelly SQL MVP
"Anna Emegard" <Anna.Emegard@.harryda.se> wrote in message news:ufX7Xb$HFHA.1
172@.TK2MSFTNGP12.phx.gbl...
Hi!
We have the exact same error. We run the integrity check once a week on this
one database and it has run smoothly until three weeks ago. In the log file
it says "Database state cannot be changed while other users are using the d
atabase". There are other jobs running before and after this job and there a
re no problems with them. I can't figure out why this particular job won't r
un.
Sincerely,
Anna
[vbcol=seagreen]
No, something indeed happened; the problem is finding out more details as to
why. There are several logging options available to you.
1. In the maintenance plan, Reporting Tab, choose to retain information
within the MSDB database but also create Job Run report to a text file.
Oftentimes, this information will provide you with the necessary detail.
2. On the job, make sure it is configured to output additional step info on
the advanced tab of the step settings.
3. You've already discovered the SQL Server error logs and the Windows
Application event logs, also take a look at the SQL Agent error logs.
To view the maintenance history, in SQL EM, right-click one of the
maintenance plans and choose to view the history. This log will tell you
each activity, and each database, whether successfull or failure. If there
were database connectivity issues, then this information will be missing and
the text log files your created above will be the only source of information
outside of the error logs you have already discovered.
Hope this helps.
Sincerely,
Anthony Thomas
--
"AnthonyMunoz" <AnthonyMunoz@.discussions.microsoft.com> wrote in message
news:6D182012-CFDD-4A1F-86FD-19297F036C6F@.microsoft.com...
We have 3 maintenance plans, each running against a different subset of
databases on the same server. Each maintenance plan runs integrity checks
once a week. According to "Job History", one of the three jobs fails every
week. Looking at the sql logs every DBCC check shows "found 0 errors and
repaired 0 errors." In the server's Application Event Log we see this vague
error message:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 1/2/2005
Time: 12:00:27 AM
User: N/A
Computer: FBENSQLP1
Description:
SQL Server Scheduled Job 'Integrity Checks Job for DB Maintenance Plan
'Internal Maintenance'' (0x860A641AB3C0FD4CA22C0369E63523E4) - Status:
Failed
- Invoked on: 2005-01-02 00:00:00 - Message: The job failed. The Job was
invoked by Schedule 19 (Schedule 1). The last step to run was step 1 (Step
1).
Is it possible that this is some sort of a false alert? The other two
weekly integrity checks always show successful completions.
What might cause this job to "Fail" every week?