ODBC Isolation level: running

Showing posts with label running. Show all posts

Wednesday, March 28, 2012

ok to install sql2005/2000,vs2005/2003 on same machine

Hi just wondering if anyone has run into any problems installing and running
vs2005 and sql 2005 prof with a machine that already has sql2000 and vs2003,
or if it will effect sql 2000 or vs2003 in any way, thanks.
--
Paul G
Software engineer.I run this configuration. It does work. I installed a certificate on my web
server and suddenly I couldn't connect to SQL 2000 anymore. Other than that
it was working well.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A323C6DA-764B-4298-9CBB-3E751F55DD0F@.microsoft.com...
> Hi just wondering if anyone has run into any problems installing and
> running
> vs2005 and sql 2005 prof with a machine that already has sql2000 and
> vs2003,
> or if it will effect sql 2000 or vs2003 in any way, thanks.
> --
> Paul G
> Software engineer.|||thanks for the response. I think I am having problems with the installation.
For components to install it lists SQL Server Database Services, Analysis
Services, Reporting Services, Integration Services, Workstations Components,
Books online and development tools. It seems to install without error but I
do not see SQL server management studio from the programs list.
--
Paul G
Software engineer.
"Hilary Cotter" wrote:
> I run this configuration. It does work. I installed a certificate on my web
> server and suddenly I couldn't connect to SQL 2000 anymore. Other than that
> it was working well.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A323C6DA-764B-4298-9CBB-3E751F55DD0F@.microsoft.com...
> > Hi just wondering if anyone has run into any problems installing and
> > running
> > vs2005 and sql 2005 prof with a machine that already has sql2000 and
> > vs2003,
> > or if it will effect sql 2000 or vs2003 in any way, thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Chances are this is because you installed SQL Express with Visual Studio
before you installed SQL Server 2005. This leaves you with the SQL Express
client utilities which don't include Management Studio. Try uninstalling
the client utilities and then reinstalling them from the full SQL Server
2005 release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:CB9FDA44-C306-4A28-B054-D3CA726A5936@.microsoft.com...
> thanks for the response. I think I am having problems with the
> installation.
> For components to install it lists SQL Server Database Services, Analysis
> Services, Reporting Services, Integration Services, Workstations
> Components,
> Books online and development tools. It seems to install without error but
> I
> do not see SQL server management studio from the programs list.
> --
> Paul G
> Software engineer.
>
> "Hilary Cotter" wrote:
>> I run this configuration. It does work. I installed a certificate on my
>> web
>> server and suddenly I couldn't connect to SQL 2000 anymore. Other than
>> that
>> it was working well.
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:A323C6DA-764B-4298-9CBB-3E751F55DD0F@.microsoft.com...
>> > Hi just wondering if anyone has run into any problems installing and
>> > running
>> > vs2005 and sql 2005 prof with a machine that already has sql2000 and
>> > vs2003,
>> > or if it will effect sql 2000 or vs2003 in any way, thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>|||ok thanks that seemed to work!
--
Paul G
Software engineer.
"Roger Wolter[MSFT]" wrote:
> Chances are this is because you installed SQL Express with Visual Studio
> before you installed SQL Server 2005. This leaves you with the SQL Express
> client utilities which don't include Management Studio. Try uninstalling
> the client utilities and then reinstalling them from the full SQL Server
> 2005 release.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:CB9FDA44-C306-4A28-B054-D3CA726A5936@.microsoft.com...
> > thanks for the response. I think I am having problems with the
> > installation.
> > For components to install it lists SQL Server Database Services, Analysis
> > Services, Reporting Services, Integration Services, Workstations
> > Components,
> > Books online and development tools. It seems to install without error but
> > I
> > do not see SQL server management studio from the programs list.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Hilary Cotter" wrote:
> >
> >> I run this configuration. It does work. I installed a certificate on my
> >> web
> >> server and suddenly I couldn't connect to SQL 2000 anymore. Other than
> >> that
> >> it was working well.
> >>
> >> --
> >> Hilary Cotter
> >> Looking for a SQL Server replication book?
> >> http://www.nwsu.com/0974973602.html
> >>
> >> Looking for a FAQ on Indexing Services/SQL FTS
> >> http://www.indexserverfaq.com
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:A323C6DA-764B-4298-9CBB-3E751F55DD0F@.microsoft.com...
> >> > Hi just wondering if anyone has run into any problems installing and
> >> > running
> >> > vs2005 and sql 2005 prof with a machine that already has sql2000 and
> >> > vs2003,
> >> > or if it will effect sql 2000 or vs2003 in any way, thanks.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>

Monday, March 26, 2012

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
RegardsJohn wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>sql

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/We...CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
Regards
John wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/We...CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/We...CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/We...CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
Regards
John wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/We...CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/We...CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>

Friday, March 23, 2012

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/W...&CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
RegardsJohn wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/W...&CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/W...&CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>

Odd: Inserts and updates happen before running code in debugger

Hi,

Before stepping into ado.net code to perform an insert or update, the insert / update has already taken place, just on starting the debugger. I use VS 2005 on SQL Server 2000. This did not happen with VS 2003 and SQL Server 2000.

Anyone else encountered this?The VS 2005 Development Server caused the problem.

On changing to IIS 5 for execution, when debugging, the ado.net code only executed when I had stepped through it. Therefore if I stopped debugging before I stepped through the ado.net code, the database was not updated. However, in using VS 2005 Development Server, the database was already updated, even before I had stepped through the first line of code!

Can someone try this finding out?|||On some apps, I continue to see database changes when no ado.net code has been run in the debugger. This is using IIS or the built in server.

On others, no database changes take place when no ado.net code has been run in the debugger. There must be a bug.

Wednesday, March 21, 2012

Odd syntax error with shape queries SQL2000/Win2003

Hi,

I am running SQL Server 2000 SP3 on Windows Server 2003 and since recently
have a strange problem executing shape queries from COM+ components using
ADO.

Until 4 days ago, they worked, then from one moment to the next (I must have
changed something, but I have no clue what other than restoring a 1.2 GB
database) they started failing with this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation

I have no problem executing non-shape queries, it is just the shape queries
that fail.

Any clues what may have gone wrong? Or how I can fix it?

Cheers,
Rsa Myh> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Syntax error or access violation

And apparently this had nothing to do with Win2003 or SQL Server or any
software at all... Just with a mistake in a setting of my own software...
The datasource string for the shape data source was invalid....

I'm hanging my head in shame..

Odd query result in SQL server 2000

Hi,
I am running SQL Server 2000, SP4.
I have stored procedure with a query like this:
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (...list of IDs)
AND PE.EDU_ID=1
By running the stored procedure this query gives the result 221, -1
This result is wrong.
I run EXACTLY the same query using query analyzer and that gives the
result 199, -1
Which is correct
How is this possible? I am running the both the SP and the query under
the same account.
I have tried to drop the SP and re-create it. I have tried to re-
compile the SP. Nothing seems to help.
Any ideas?
Thanks.
Best regards,
EirikUse the Show Actual Query Plan feature to see what both queries are doing.
Also, is the list of IDs a parameter in the sproc?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Eiriken" <eirik@.oslo.online.no> wrote in message
news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
> Hi,
> I am running SQL Server 2000, SP4.
> I have stored procedure with a query like this:
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (...list of IDs)
> AND PE.EDU_ID=1
> By running the stored procedure this query gives the result 221, -1
> This result is wrong.
> I run EXACTLY the same query using query analyzer and that gives the
> result 199, -1
> Which is correct
> How is this possible? I am running the both the SP and the query under
> the same account.
> I have tried to drop the SP and re-create it. I have tried to re-
> compile the SP. Nothing seems to help.
> Any ideas?
> Thanks.
> Best regards,
> Eirik|||Thank you for answering
The list of IDs is from a temporary table created in the same SP.
Looking at the execution plan shows that indeed there is a difference
between running the queries in the SP and in the Query Analyzer. The
"messages" tab also shows a difference. By running the queries in
query analyzer shows "23 rows affected and 1 rows affected". Running
the queries in a SP executed in query analyzer shows "23 rows
affected, 5 rows affected and 1 rows affected". Where the 5 rows come
from I don't know.
I have stripped down the queries and I am pasting the query here.
After stripping the result is the still wrong as earlier.
CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
NumberFullTime int, NumberPartTime int, NumberTotal int)
INSERT INTO #Tmp_Countries
SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
(5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
CountryId>=0)
AND PE.EDU_ID=1
DROP TABLE #Tmp_Countries
On 24 Nov, 17:39, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Use the Show Actual Query Plan feature to see what both queries are doing.
> Also, is the list of IDs a parameter in the sproc?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Eiriken" <ei...@.oslo.online.no> wrote in message
> news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
> > Hi,
> > I am running SQL Server 2000, SP4.
> > I have stored procedure with a query like this:
> > SELECT COUNT(*), -1
> > FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> > PM.PER_ID=PU.PER_ID
> > LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> > WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> > AND PU.CNT_ID NOT IN (...list of IDs)
> > AND PE.EDU_ID=1
> > By running the stored procedure this query gives the result 221, -1
> > This result is wrong.
> > I run EXACTLY the same query using query analyzer and that gives the
> > result 199, -1
> > Which is correct
> > How is this possible? I am running the both the SP and the query under
> > the same account.
> > I have tried to drop the SP and re-create it. I have tried to re-
> > compile the SP. Nothing seems to help.
> > Any ideas?
> > Thanks.
> > Best regards,
> > Eirik|||Eiriken <eirik@.oslo.online.no> wrote in
news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
> Thank you for answering
> The list of IDs is from a temporary table created in the same SP.
> Looking at the execution plan shows that indeed there is a difference
> between running the queries in the SP and in the Query Analyzer. The
> "messages" tab also shows a difference. By running the queries in
> query analyzer shows "23 rows affected and 1 rows affected". Running
> the queries in a SP executed in query analyzer shows "23 rows
> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
> from I don't know.
> I have stripped down the queries and I am pasting the query here.
> After stripping the result is the still wrong as earlier.
> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
> NumberFullTime int, NumberPartTime int, NumberTotal int)
> INSERT INTO #Tmp_Countries
> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
> CountryId>=0)
> AND PE.EDU_ID=1
> DROP TABLE #Tmp_Countries
Possibly the problem is with one or more PU/PE columns in the WHERE clause
being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the whole
of the WHERE clause will evaluate to NULL.
Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
HTH|||NULLs are my first guess for this too. They could cause problems because
settings are different between direct execution and the sproc settings'
execution context.
Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
it's owner/schema. Not only is this more efficient - there are scenarios
where it can lead to the wrong results too.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns99F2D9D92765DChrisCheneytesconet@.80.5.182.99...
> Eiriken <eirik@.oslo.online.no> wrote in
> news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
>> Thank you for answering
>> The list of IDs is from a temporary table created in the same SP.
>> Looking at the execution plan shows that indeed there is a difference
>> between running the queries in the SP and in the Query Analyzer. The
>> "messages" tab also shows a difference. By running the queries in
>> query analyzer shows "23 rows affected and 1 rows affected". Running
>> the queries in a SP executed in query analyzer shows "23 rows
>> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
>> from I don't know.
>> I have stripped down the queries and I am pasting the query here.
>> After stripping the result is the still wrong as earlier.
>> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
>> NumberFullTime int, NumberPartTime int, NumberTotal int)
>> INSERT INTO #Tmp_Countries
>> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
>> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
>> SELECT COUNT(*), -1
>> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
>> PM.PER_ID=PU.PER_ID
>> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
>> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
>> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
>> CountryId>=0)
>> AND PE.EDU_ID=1
>> DROP TABLE #Tmp_Countries
> Possibly the problem is with one or more PU/PE columns in the WHERE clause
> being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the
> whole
> of the WHERE clause will evaluate to NULL.
> Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
> HTH|||I started investigating the case more carefully and compared the
result to see what was really the difference.
The answer was exactly as you proposed. The settings were indeed
different and the NULLs were the reason for the different result.
Thank you Kevin and Chris.
On 25 Nov, 00:05, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> NULLs are my first guess for this too. They could cause problems because
> settings are different between direct execution and the sproc settings'
> execution context.
> Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
> it's owner/schema. Not only is this more efficient - there are scenarios
> where it can lead to the wrong results too.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Chris.Cheney" <Chris.CheneyXXNOSPA...@.tesco.net> wrote in message
> news:Xns99F2D9D92765DChrisCheneytesconet@.80.5.182.99...
> > Eiriken <ei...@.oslo.online.no> wrote in
> >news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
> >> Thank you for answering
> >> The list of IDs is from a temporary table created in the same SP.
> >> Looking at the execution plan shows that indeed there is a difference
> >> between running the queries in the SP and in the Query Analyzer. The
> >> "messages" tab also shows a difference. By running the queries in
> >> query analyzer shows "23 rows affected and 1 rows affected". Running
> >> the queries in a SP executed in query analyzer shows "23 rows
> >> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
> >> from I don't know.
> >> I have stripped down the queries and I am pasting the query here.
> >> After stripping the result is the still wrong as earlier.
> >> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
> >> NumberFullTime int, NumberPartTime int, NumberTotal int)
> >> INSERT INTO #Tmp_Countries
> >> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
> >> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,85,65,71, 73,86,87,1,68,190)
> >> SELECT COUNT(*), -1
> >> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> >> PM.PER_ID=PU.PER_ID
> >> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> >> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> >> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
> >> CountryId>=0)
> >> AND PE.EDU_ID=1
> >> DROP TABLE #Tmp_Countries
> > Possibly the problem is with one or more PU/PE columns in the WHERE clause
> > being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the
> > whole
> > of the WHERE clause will evaluate to NULL.
> > Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
> > HTH

Odd query result in SQL server 2000

Odd problem with Profiler

I wonder if anyone here knows what the problem is.
On one server that I administer, when running a Profiler trace (either
through the GUI or directly using the stored procs) the â'Readsâ' column always
returns 0 no matter what event is being monitored.
The server runs SQL 2000 Enterprise Edition sp3 on Win 2000 sp4.
Thanks in advance,
AntoIrishAnto wrote:
> I wonder if anyone here knows what the problem is.
> On one server that I administer, when running a Profiler trace (either
> through the GUI or directly using the stored procs) the â'Readsâ'
> column always returns 0 no matter what event is being monitored.
> The server runs SQL 2000 Enterprise Edition sp3 on Win 2000 sp4.
> Thanks in advance,
> Anto
I saw this reported once on a customer who used EMC disks and broke a
cluster to make two stand-alone installs of SQL Server. That's when the
read statistics were no longer were captured. I can't say I remember
what the solution was.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" wrote:
> IrishAnto wrote:
> > I wonder if anyone here knows what the problem is.
> >
> > On one server that I administer, when running a Profiler trace (either
> > through the GUI or directly using the stored procs) the â'Readsâ'
> > column always returns 0 no matter what event is being monitored.
> >
> > The server runs SQL 2000 Enterprise Edition sp3 on Win 2000 sp4.
> >
> > Thanks in advance,
> >
> > Anto
> I saw this reported once on a customer who used EMC disks and broke a
> cluster to make two stand-alone installs of SQL Server. That's when the
> read statistics were no longer were captured. I can't say I remember
> what the solution was.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
The server in question was never part of a cluster. As far as I'm aware
profiler used to return values in the reads column, but no longer.
I've seen other message boards with the same problem posted, but with no
answers. I was hoping someone at Microsoft might know of a solution.
Anto|||IrishAnto wrote:
> The server in question was never part of a cluster. As far as I'm
> aware profiler used to return values in the reads column, but no
> longer. I've seen other message boards with the same problem posted,
> but with no answers. I was hoping someone at Microsoft might know of
> a solution.
>
This is likely not a Profiler issue, but a SQL Trace API one on the
server. I assume the problem occurs from all clients, including the
server itself.
I might consider opening a support case with Microsoft... and then
sharing the solution with the rest of us :-)
In any case, I assume you are running SP3a or SP4 on the server. Is that
correct?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" wrote:
> IrishAnto wrote:
> > The server in question was never part of a cluster. As far as I'm
> > aware profiler used to return values in the reads column, but no
> > longer. I've seen other message boards with the same problem posted,
> > but with no answers. I was hoping someone at Microsoft might know of
> > a solution.
> >
> This is likely not a Profiler issue, but a SQL Trace API one on the
> server. I assume the problem occurs from all clients, including the
> server itself.
> I might consider opening a support case with Microsoft... and then
> sharing the solution with the rest of us :-)
> In any case, I assume you are running SP3a or SP4 on the server. Is that
> correct?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
David,
Indeed weâ're running Sp3a on a Win 2000 Sp4 box.
It would seem that the Trace API is no longer functioning correctly, so the
only solution maybe a call to Microsoft.
Thanks for your reply anyway.
Anto

Odd problem with Profiler

I wonder if anyone here knows what the problem is.
On one server that I administer, when running a Profiler trace (either
through the GUI or directly using the stored procs) the “Reads” column always
returns 0 no matter what event is being monitored.
The server runs SQL 2000 Enterprise Edition sp3 on Win 2000 sp4.
Thanks in advance,
Anto
IrishAnto wrote:
> I wonder if anyone here knows what the problem is.
> On one server that I administer, when running a Profiler trace (either
> through the GUI or directly using the stored procs) the “Reads”
> column always returns 0 no matter what event is being monitored.
> The server runs SQL 2000 Enterprise Edition sp3 on Win 2000 sp4.
> Thanks in advance,
> Anto
I saw this reported once on a customer who used EMC disks and broke a
cluster to make two stand-alone installs of SQL Server. That's when the
read statistics were no longer were captured. I can't say I remember
what the solution was.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"David Gugick" wrote:

> IrishAnto wrote:
> I saw this reported once on a customer who used EMC disks and broke a
> cluster to make two stand-alone installs of SQL Server. That's when the
> read statistics were no longer were captured. I can't say I remember
> what the solution was.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
The server in question was never part of a cluster. As far as I'm aware
profiler used to return values in the reads column, but no longer.
I've seen other message boards with the same problem posted, but with no
answers. I was hoping someone at Microsoft might know of a solution.
Anto
|||IrishAnto wrote:
> The server in question was never part of a cluster. As far as I'm
> aware profiler used to return values in the reads column, but no
> longer. I've seen other message boards with the same problem posted,
> but with no answers. I was hoping someone at Microsoft might know of
> a solution.
>
This is likely not a Profiler issue, but a SQL Trace API one on the
server. I assume the problem occurs from all clients, including the
server itself.
I might consider opening a support case with Microsoft... and then
sharing the solution with the rest of us :-)
In any case, I assume you are running SP3a or SP4 on the server. Is that
correct?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"David Gugick" wrote:

> IrishAnto wrote:
> This is likely not a Profiler issue, but a SQL Trace API one on the
> server. I assume the problem occurs from all clients, including the
> server itself.
> I might consider opening a support case with Microsoft... and then
> sharing the solution with the rest of us :-)
> In any case, I assume you are running SP3a or SP4 on the server. Is that
> correct?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
David,
Indeed we’re running Sp3a on a Win 2000 Sp4 box.
It would seem that the Trace API is no longer functioning correctly, so the
only solution maybe a call to Microsoft.
Thanks for your reply anyway.
Anto
sql

Odd problem with Profiler

I wonder if anyone here knows what the problem is.
On one server that I administer, when running a Profiler trace (either
through the GUI or directly using the stored procs) the “Reads” column a
lways
returns 0 no matter what event is being monitored.
The server runs SQL 2000 Enterprise Edition sp3 on Win 2000 sp4.
Thanks in advance,
AntoIrishAnto wrote:
> I wonder if anyone here knows what the problem is.
> On one server that I administer, when running a Profiler trace (either
> through the GUI or directly using the stored procs) the “Reads”
> column always returns 0 no matter what event is being monitored.
> The server runs SQL 2000 Enterprise Edition sp3 on Win 2000 sp4.
> Thanks in advance,
> Anto
I saw this reported once on a customer who used EMC disks and broke a
cluster to make two stand-alone installs of SQL Server. That's when the
read statistics were no longer were captured. I can't say I remember
what the solution was.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" wrote:

> IrishAnto wrote:
> I saw this reported once on a customer who used EMC disks and broke a
> cluster to make two stand-alone installs of SQL Server. That's when the
> read statistics were no longer were captured. I can't say I remember
> what the solution was.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
The server in question was never part of a cluster. As far as I'm aware
profiler used to return values in the reads column, but no longer.
I've seen other message boards with the same problem posted, but with no
answers. I was hoping someone at Microsoft might know of a solution.
Anto|||IrishAnto wrote:
> The server in question was never part of a cluster. As far as I'm
> aware profiler used to return values in the reads column, but no
> longer. I've seen other message boards with the same problem posted,
> but with no answers. I was hoping someone at Microsoft might know of
> a solution.
>
This is likely not a Profiler issue, but a SQL Trace API one on the
server. I assume the problem occurs from all clients, including the
server itself.
I might consider opening a support case with Microsoft... and then
sharing the solution with the rest of us :-)
In any case, I assume you are running SP3a or SP4 on the server. Is that
correct?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" wrote:

> IrishAnto wrote:
> This is likely not a Profiler issue, but a SQL Trace API one on the
> server. I assume the problem occurs from all clients, including the
> server itself.
> I might consider opening a support case with Microsoft... and then
> sharing the solution with the rest of us :-)
> In any case, I assume you are running SP3a or SP4 on the server. Is that
> correct?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
David,
Indeed we’re running Sp3a on a Win 2000 Sp4 box.
It would seem that the Trace API is no longer functioning correctly, so the
only solution maybe a call to Microsoft.
Thanks for your reply anyway.
Anto

Tuesday, March 20, 2012

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

Odd error message in Microsoft Query

I am using an online ODBC Database.

The SQL statement I'm using is this.

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

odd deadlocking behaviour

I am running SQL Server 2000 SP3a on a single processor computer and:
I have a table with the following columns
CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
[JOB_NO] [int] NOT NULL ,
[OVERRIDDEN_PERIOD_START] [datetime] NULL ,
[OVERRIDDEN_PERIOD_END] [datetime] NULL
) ON [PRIMARY]
GO
That has the following data:
1,06/01/2003 17:00:00,NULL
2,NULL,13/01/2003 08:00:00
2,13/01/2003 17:00:00,NULL
3,NULL,20/01/2003 08:00:00
3,20/01/2003 17:00:00,NULL
4,NULL, 27/01/2003 08:00:00
4,27/01/2003 17:00:00,NULL
If I now try deleting these rows from two database sessions in the order as
seen below I end up with a deadlock:
Firstly on SPID 56:
set implicit_transactions on
delete from SP148_JOB_AHO_PERIODS where job_no = 2
Secondly on SPID 57:
set implicit_transactions on
delete from SP148_JOB_AHO_PERIODS where job_no = 1
... This then blocks ? Which I am not sure about.
Thirdly back on SPID 56:
delete from SP148_JOB_AHO_PERIODS where job_no = 6
Creates a deadlock victimising SPID 57.
I accept that this table does not have a primary key but when I add one it
still deadlocks.
Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
Why does a deadlock occur when SPID 56 subsequently deletes the row where
job_no is 6 i.e. no rows?
When I looked at sp_lock output it appeared that both sessions were waiting
on the same RID, does this mean that a RID is not necessarily 1 database
table row?
Thanks- What kind of lock triggered the deadlock? row, index, page, extent, table,
database
- Do you have an index by "job_no"?
AMB
"Tony Jones" wrote:
> I am running SQL Server 2000 SP3a on a single processor computer and:
> I have a table with the following columns
> CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
> [JOB_NO] [int] NOT NULL ,
> [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
> [OVERRIDDEN_PERIOD_END] [datetime] NULL
> ) ON [PRIMARY]
> GO
> That has the following data:
> 1,06/01/2003 17:00:00,NULL
> 2,NULL,13/01/2003 08:00:00
> 2,13/01/2003 17:00:00,NULL
> 3,NULL,20/01/2003 08:00:00
> 3,20/01/2003 17:00:00,NULL
> 4,NULL, 27/01/2003 08:00:00
> 4,27/01/2003 17:00:00,NULL
> If I now try deleting these rows from two database sessions in the order as
> seen below I end up with a deadlock:
> Firstly on SPID 56:
> set implicit_transactions on
> delete from SP148_JOB_AHO_PERIODS where job_no = 2
> Secondly on SPID 57:
> set implicit_transactions on
> delete from SP148_JOB_AHO_PERIODS where job_no = 1
> ... This then blocks ? Which I am not sure about.
> Thirdly back on SPID 56:
> delete from SP148_JOB_AHO_PERIODS where job_no = 6
> Creates a deadlock victimising SPID 57.
> I accept that this table does not have a primary key but when I add one it
> still deadlocks.
> Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
> Why does a deadlock occur when SPID 56 subsequently deletes the row where
> job_no is 6 i.e. no rows?
> When I looked at sp_lock output it appeared that both sessions were waiting
> on the same RID, does this mean that a RID is not necessarily 1 database
> table row?
> Thanks|||Turn on trace flag 1204 and -1. Then your answers will be in the errorlog.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5E921D65-22E3-40D0-AE4C-BEBF76DF8FC7@.microsoft.com...
>- What kind of lock triggered the deadlock? row, index, page, extent,
>table,
> database
> - Do you have an index by "job_no"?
> AMB
> "Tony Jones" wrote:
>> I am running SQL Server 2000 SP3a on a single processor computer and:
>> I have a table with the following columns
>> CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
>> [JOB_NO] [int] NOT NULL ,
>> [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
>> [OVERRIDDEN_PERIOD_END] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>> That has the following data:
>> 1,06/01/2003 17:00:00,NULL
>> 2,NULL,13/01/2003 08:00:00
>> 2,13/01/2003 17:00:00,NULL
>> 3,NULL,20/01/2003 08:00:00
>> 3,20/01/2003 17:00:00,NULL
>> 4,NULL, 27/01/2003 08:00:00
>> 4,27/01/2003 17:00:00,NULL
>> If I now try deleting these rows from two database sessions in the order
>> as
>> seen below I end up with a deadlock:
>> Firstly on SPID 56:
>> set implicit_transactions on
>> delete from SP148_JOB_AHO_PERIODS where job_no = 2
>> Secondly on SPID 57:
>> set implicit_transactions on
>> delete from SP148_JOB_AHO_PERIODS where job_no = 1
>> ... This then blocks ? Which I am not sure about.
>> Thirdly back on SPID 56:
>> delete from SP148_JOB_AHO_PERIODS where job_no = 6
>> Creates a deadlock victimising SPID 57.
>> I accept that this table does not have a primary key but when I add one
>> it
>> still deadlocks.
>> Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
>> Why does a deadlock occur when SPID 56 subsequently deletes the row where
>> job_no is 6 i.e. no rows?
>> When I looked at sp_lock output it appeared that both sessions were
>> waiting
>> on the same RID, does this mean that a RID is not necessarily 1 database
>> table row?
>> Thanks|||The blocked SPID 57 is waiting on a RID (so a row lock) both SPIDs are
waiting on the same rid i.e. the same fileid:pageid:slot, hence the question
is the RID locking more than one row?
No I have not had a index on job_no I have tried a primary kry on a new
column defined to be the Identity column. This produced the same deadlock
behaviour.
"Alejandro Mesa" wrote:
> - What kind of lock triggered the deadlock? row, index, page, extent, table,
> database
> - Do you have an index by "job_no"?
> AMB
> "Tony Jones" wrote:
> > I am running SQL Server 2000 SP3a on a single processor computer and:
> >
> > I have a table with the following columns
> > CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
> > [JOB_NO] [int] NOT NULL ,
> > [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
> > [OVERRIDDEN_PERIOD_END] [datetime] NULL
> > ) ON [PRIMARY]
> > GO
> >
> > That has the following data:
> >
> > 1,06/01/2003 17:00:00,NULL
> > 2,NULL,13/01/2003 08:00:00
> > 2,13/01/2003 17:00:00,NULL
> > 3,NULL,20/01/2003 08:00:00
> > 3,20/01/2003 17:00:00,NULL
> > 4,NULL, 27/01/2003 08:00:00
> > 4,27/01/2003 17:00:00,NULL
> >
> > If I now try deleting these rows from two database sessions in the order as
> > seen below I end up with a deadlock:
> >
> > Firstly on SPID 56:
> > set implicit_transactions on
> > delete from SP148_JOB_AHO_PERIODS where job_no = 2
> >
> > Secondly on SPID 57:
> > set implicit_transactions on
> > delete from SP148_JOB_AHO_PERIODS where job_no = 1
> > ... This then blocks ? Which I am not sure about.
> >
> > Thirdly back on SPID 56:
> > delete from SP148_JOB_AHO_PERIODS where job_no = 6
> >
> > Creates a deadlock victimising SPID 57.
> >
> > I accept that this table does not have a primary key but when I add one it
> > still deadlocks.
> >
> > Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
> > Why does a deadlock occur when SPID 56 subsequently deletes the row where
> > job_no is 6 i.e. no rows?
> >
> > When I looked at sp_lock output it appeared that both sessions were waiting
> > on the same RID, does this mean that a RID is not necessarily 1 database
> > table row?
> >
> > Thanks

Odd connection problem

I (running SQL 7 client only) cannot connect to the
server via my machine, but CAN connect (after installing
client) to the server from ANY other machine on the LAN.
My SysAdmin and I have been workin on this for days but
have come up empty, any ideas?
Could it be some other software I'm running?
Specs:
Sql7 client attempting to connect to server fails via
client tools and isql command line...
client = XP Pro connected via LAN to server. Can ping
server... have re-installed client software multiple
times.
rcause,
What error message do you get when connecting from osql.exe? Do you have an
aliases set up in your client configuration utility?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
|||Mark,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server
does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionOpen (Connect()).
That's the osql error message.
Do not have alias set up?
Thanks
>--Original Message--
>rcause,
>What error message do you get when connecting from
osql.exe? Do you have an[vbcol=seagreen]
>aliases set up in your client configuration utility?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rcause" <rcausey@.med.unc.edu> wrote in message
>news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
installing[vbcol=seagreen]
LAN.
>
>.
>
|||Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004
|||Hi ricky
just goto the services in your client mechine.and find out the sql srevre services.
then if they are stop then start.
if they are start then go to the properits.
and go to the logon and check the authentication to local system
biswajit das
-- Ricky Artigas wrote: --
Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004

Odd connection problem

I (running SQL 7 client only) cannot connect to the
server via my machine, but CAN connect (after installing
client) to the server from ANY other machine on the LAN.
My SysAdmin and I have been workin on this for days but
have come up empty, any ideas?
Could it be some other software I'm running?
Specs:
Sql7 client attempting to connect to server fails via
client tools and isql command line...
client = XP Pro connected via LAN to server. Can ping
server... have re-installed client software multiple
times.rcause,
What error message do you get when connecting from osql.exe? Do you have an
aliases set up in your client configuration utility?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.|||Mark,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server
does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionOpen (Connect()).
That's the osql error message.
Do not have alias set up?
Thanks
>--Original Message--
>rcause,
>What error message do you get when connecting from
osql.exe? Do you have an
>aliases set up in your client configuration utility?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rcause" <rcausey@.med.unc.edu> wrote in message
>news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
installing[vbcol=seagreen]
LAN.[vbcol=seagreen]
>
>.
>|||Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004|||Hi ricky
just goto the services in your client mechine.and find out the sql srevre se
rvices.
then if they are stop then start.
if they are start then go to the properits.
and go to the logon and check the authentication to local system
biswajit das
-- Ricky Artigas wrote: --
Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004

Wednesday, March 28, 2012

ok to install sql2005/2000,vs2005/2003 on same machine

Monday, March 26, 2012

Offline Client

Offline Client

Offline Client

Friday, March 23, 2012

Offline Client

Odd: Inserts and updates happen before running code in debugger

Wednesday, March 21, 2012

Odd syntax error with shape queries SQL2000/Win2003

Odd query result in SQL server 2000

Odd query result in SQL server 2000

Odd query result in SQL server 2000

Odd problem with Profiler

Odd problem with Profiler

Odd problem with Profiler

Tuesday, March 20, 2012

Odd error message in Microsoft Query

Odd error message in Microsoft Query

odd deadlocking behaviour

odd deadlocking behaviour

odd deadlocking behaviour

Odd connection problem

Odd connection problem

ODBC Isolation level

Blog Archive

About Me