connection level NOLOCK

Hi!

when WITH (NOLOCK) statement is used in a select statement, it allows to retrieved data from a set of data locked by another transaction. Is it possible to enforce this at connection level?

I mean to open a connection using sqlclient and enforce NOLOCK so that all select statements post to database using that connection are not locked by other transactions.

thanks in advance

Ok. I found it!

I have to begin a transaction with isolation level 'readuncommited' and do all select statements inside that transaction.

connection err

hi

i use sql 2005 and i create view from linked server

when i select from the view it's o.k

but in the app. i get thes messge :

42000 [Microsoft][SQL Native Client][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

how can i resolve this problam

thenk's

You could try doing:

set ANSI_NULLS on

go

set ANSI_WARNINGS on

go

<select from your view>

instead of just:

<select from your view>

hope that helps,

John

|||

hi

i do this

but no

when i run the proc. or select on the view it is o.k

i get the err when i use the app. (uniface)

it is look like the odbc

thenks

|||

When you execute the QUERY from the application, SET the ANSI settings appropriately, something like this:

SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; SELECT Col1, Col2, etc FROM MyTable WHERE {criteria}

Try the entire line above as the command string. (Or put it into a stored procedure and just call the stored procedure.)

|||

---

This is sometimes based on the problem that the views are not created with the right ANSI settings, so use

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO

CREATE VIEW SOMEVIEW....

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

Connection Broken when executing select statement

Hi,
I have written a query that gives the following message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForD
ata
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I have seen several posts with this problem but none seem to match mine.
here is the query
select distinct
case
when 'ct' = 'CT' then 'CTM'
when 'ct' = 'SD' then 'SDM'
when 'ct' = 'TFVT' then 'VTM'
end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
Locality, Location,
Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
Charge_Key,
Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null, GIS_Tile,
Note_Text, Asset_Region, Asset_Ranking
from asset
where category = 'ct'
and asset_Id not in (60325, 60377,60274, 60397)
and ((len(local_id) < 6 and local_id = 'AV-A196')
or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) =
'AV-A196'))
I have tested this on a SQL Server 2000 machine by creating a new database,
importing the asset table of 66000 records. Then created new indexes on
Asset_id (clustered primary key)
Local_id (unique index)
Category (index)
If I remove any of the conditions in the where statement it runs fine.
However with them all in their it breaks.
I am running 8.00.859 (sp3)
Any Suggestions
BenBenJamin wrote:
> Hi,
> I have written a query that gives the following message
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckFo
rData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> I have seen several posts with this problem but none seem to match
> mine. here is the query
> select distinct
> case
> when 'ct' = 'CT' then 'CTM'
> when 'ct' = 'SD' then 'SDM'
> when 'ct' = 'TFVT' then 'VTM'
> end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> Locality, Location,
> Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> Charge_Key,
> Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> from asset
> where category = 'ct'
> and asset_Id not in (60325, 60377,60274, 60397)
> and ((len(local_id) < 6 and local_id = 'AV-A196')
> or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) =
> 'AV-A196'))
> I have tested this on a SQL Server 2000 machine by creating a new
> database, importing the asset table of 66000 records. Then created
> new indexes on Asset_id (clustered primary key)
> Local_id (unique index)
> Category (index)
> If I remove any of the conditions in the where statement it runs fine.
> However with them all in their it breaks.
> I am running 8.00.859 (sp3)
> Any Suggestions
> Ben
Maybe I'm reading the SQL statement incorrectly, but how would this
statement ever be true:
(len(local_id) < 6 and local_id = 'AV-A196')
That would seem to imply the lengh of the column must be 5 or less _and_
it must be equal to a 7 character string, at the same time.
Just curious.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:

> BenJamin wrote:
> Maybe I'm reading the SQL statement incorrectly, but how would this
> statement ever be true:
> (len(local_id) < 6 and local_id = 'AV-A196')
> That would seem to imply the lengh of the column must be 5 or less _and_
> it must be equal to a 7 character string, at the same time.
> Just curious.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Fair Call,
That part is just there as a means to stop the statement trying to process
Local_ID's less than 6 characters long with the second part of the condition
statement. It is meant to always be false.
I was gettting incorrect length to the substring function. I tried to get it
to process the conditions in the order that they are written to exclude the
unwanted rows. Maybe there is a way, I don't know.
But thanks for the quick response though...|||Hi
Check your SQL Server log. You may find that your process was terminated at
a server level. If it was, try to go to 8.00.0878 and if that does not solve
it, open a call with PSS.
Regards
Mike
"BenJamin" wrote:

>
> "David Gugick" wrote:
>
> Fair Call,
> That part is just there as a means to stop the statement trying to process
> Local_ID's less than 6 characters long with the second part of the conditi
on
> statement. It is meant to always be false.
> I was gettting incorrect length to the substring function. I tried to get
it
> to process the conditions in the order that they are written to exclude th
e
> unwanted rows. Maybe there is a way, I don't know.
> But thanks for the quick response though...|||Ben
Add option (loop join) in your select statement
"BenJamin" <BenJamin@.discussions.microsoft.com> wrote in message
news:81A67F85-4D1E-4109-8090-6AFFE437DA63@.microsoft.com...
>
> "David Gugick" wrote:
>
> Fair Call,
> That part is just there as a means to stop the statement trying to process
> Local_ID's less than 6 characters long with the second part of the
condition
> statement. It is meant to always be false.
> I was gettting incorrect length to the substring function. I tried to get
it
> to process the conditions in the order that they are written to exclude
the
> unwanted rows. Maybe there is a way, I don't know.
> But thanks for the quick response though...|||Check the traffic on your network, the query maybe
overloading it.
Peter
"A politician needs the ability to foretell what is going
to happen tomorrow, next week, next month, and next year.
And to have the ability afterwards to explain why it
didn't happen."
Winston Churchill

>--Original Message--
>
>"David Gugick" wrote:
>
message[vbcol=seagreen]
ConnectionCheckForData[vbcol=seagreen]
documentation.[vbcol=seagreen]
seem to match[vbcol=seagreen]
null, Service_Area,[vbcol=seagreen]
Condition_Status,[vbcol=seagreen]
Data_Changed, null,[vbcol=seagreen]
(local_id)-6) =[vbcol=seagreen]
creating a new[vbcol=seagreen]
Then created[vbcol=seagreen]
statement it runs fine.[vbcol=seagreen]
how would this[vbcol=seagreen]
be 5 or less _and_[vbcol=seagreen]
time.[vbcol=seagreen]
>Fair Call,
>That part is just there as a means to stop the statement
trying to process
>Local_ID's less than 6 characters long with the second
part of the condition
>statement. It is meant to always be false.
>I was gettting incorrect length to the substring
function. I tried to get it
>to process the conditions in the order that they are
written to exclude the
>unwanted rows. Maybe there is a way, I don't know.
>But thanks for the quick response though...
>.
>

Connection Broken when executing select statement

Hi,
I have written a query that gives the following message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I have seen several posts with this problem but none seem to match mine.
here is the query
select distinct
case
when 'ct' = 'CT' then 'CTM'
when 'ct' = 'SD' then 'SDM'
when 'ct' = 'TFVT' then 'VTM'
end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
Locality, Location,
Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
Charge_Key,
Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null, GIS_Tile,
Note_Text, Asset_Region, Asset_Ranking
from asset
where category = 'ct'
and asset_Id not in (60325, 60377,60274, 60397)
and ((len(local_id) < 6 and local_id = 'AV-A196')
or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) =
'AV-A196'))
I have tested this on a SQL Server 2000 machine by creating a new database,
importing the asset table of 66000 records. Then created new indexes on
Asset_id (clustered primary key)
Local_id (unique index)
Category (index)
If I remove any of the conditions in the where statement it runs fine.
However with them all in their it breaks.
I am running 8.00.859 (sp3)
Any Suggestions
Ben
BenJamin wrote:
> Hi,
> I have written a query that gives the following message
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> I have seen several posts with this problem but none seem to match
> mine. here is the query
> select distinct
> case
> when 'ct' = 'CT' then 'CTM'
> when 'ct' = 'SD' then 'SDM'
> when 'ct' = 'TFVT' then 'VTM'
> end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> Locality, Location,
> Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> Charge_Key,
> Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> from asset
> where category = 'ct'
> and asset_Id not in (60325, 60377,60274, 60397)
> and ((len(local_id) < 6 and local_id = 'AV-A196')
> or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) =
> 'AV-A196'))
> I have tested this on a SQL Server 2000 machine by creating a new
> database, importing the asset table of 66000 records. Then created
> new indexes on Asset_id (clustered primary key)
> Local_id (unique index)
> Category (index)
> If I remove any of the conditions in the where statement it runs fine.
> However with them all in their it breaks.
> I am running 8.00.859 (sp3)
> Any Suggestions
> Ben
Maybe I'm reading the SQL statement incorrectly, but how would this
statement ever be true:
(len(local_id) < 6 and local_id = 'AV-A196')
That would seem to imply the lengh of the column must be 5 or less _and_
it must be equal to a 7 character string, at the same time.
Just curious.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" wrote:

> BenJamin wrote:
> Maybe I'm reading the SQL statement incorrectly, but how would this
> statement ever be true:
> (len(local_id) < 6 and local_id = 'AV-A196')
> That would seem to imply the lengh of the column must be 5 or less _and_
> it must be equal to a 7 character string, at the same time.
> Just curious.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Fair Call,
That part is just there as a means to stop the statement trying to process
Local_ID's less than 6 characters long with the second part of the condition
statement. It is meant to always be false.
I was gettting incorrect length to the substring function. I tried to get it
to process the conditions in the order that they are written to exclude the
unwanted rows. Maybe there is a way, I don't know.
But thanks for the quick response though...
|||Hi
Check your SQL Server log. You may find that your process was terminated at
a server level. If it was, try to go to 8.00.0878 and if that does not solve
it, open a call with PSS.
Regards
Mike
"BenJamin" wrote:

>
> "David Gugick" wrote:
> Fair Call,
> That part is just there as a means to stop the statement trying to process
> Local_ID's less than 6 characters long with the second part of the condition
> statement. It is meant to always be false.
> I was gettting incorrect length to the substring function. I tried to get it
> to process the conditions in the order that they are written to exclude the
> unwanted rows. Maybe there is a way, I don't know.
> But thanks for the quick response though...
|||Ben
Add option (loop join) in your select statement
"BenJamin" <BenJamin@.discussions.microsoft.com> wrote in message
news:81A67F85-4D1E-4109-8090-6AFFE437DA63@.microsoft.com...
>
> "David Gugick" wrote:
> Fair Call,
> That part is just there as a means to stop the statement trying to process
> Local_ID's less than 6 characters long with the second part of the
condition
> statement. It is meant to always be false.
> I was gettting incorrect length to the substring function. I tried to get
it
> to process the conditions in the order that they are written to exclude
the
> unwanted rows. Maybe there is a way, I don't know.
> But thanks for the quick response though...
|||Check the traffic on your network, the query maybe
overloading it.
Peter
"A politician needs the ability to foretell what is going
to happen tomorrow, next week, next month, and next year.
And to have the ability afterwards to explain why it
didn't happen."
Winston Churchill
[vbcol=seagreen]
>--Original Message--
>
>"David Gugick" wrote:
message[vbcol=seagreen]
ConnectionCheckForData[vbcol=seagreen]
documentation.[vbcol=seagreen]
seem to match[vbcol=seagreen]
null, Service_Area,[vbcol=seagreen]
Condition_Status,[vbcol=seagreen]
Data_Changed, null,[vbcol=seagreen]
(local_id)-6) =[vbcol=seagreen]
creating a new[vbcol=seagreen]
Then created[vbcol=seagreen]
statement it runs fine.[vbcol=seagreen]
how would this[vbcol=seagreen]
be 5 or less _and_[vbcol=seagreen]
time.
>Fair Call,
>That part is just there as a means to stop the statement
trying to process
>Local_ID's less than 6 characters long with the second
part of the condition
>statement. It is meant to always be false.
>I was gettting incorrect length to the substring
function. I tried to get it
>to process the conditions in the order that they are
written to exclude the
>unwanted rows. Maybe there is a way, I don't know.
>But thanks for the quick response though...
>.
>

Connection Broken when executing select statement

Hi,
I have written a query that gives the following message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I have seen several posts with this problem but none seem to match mine.
here is the query
select distinct
case
when 'ct' = 'CT' then 'CTM'
when 'ct' = 'SD' then 'SDM'
when 'ct' = 'TFVT' then 'VTM'
end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
Locality, Location,
Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
Charge_Key,
Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null, GIS_Tile,
Note_Text, Asset_Region, Asset_Ranking
from asset
where category = 'ct'
and asset_Id not in (60325, 60377,60274, 60397)
and ((len(local_id) < 6 and local_id = 'AV-A196')
or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) = 'AV-A196'))
I have tested this on a SQL Server 2000 machine by creating a new database,
importing the asset table of 66000 records. Then created new indexes on
Asset_id (clustered primary key)
Local_id (unique index)
Category (index)
If I remove any of the conditions in the where statement it runs fine.
However with them all in their it breaks.
I am running 8.00.859 (sp3)
Any Suggestions
BenBenJamin wrote:
> Hi,
> I have written a query that gives the following message
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> I have seen several posts with this problem but none seem to match
> mine. here is the query
> select distinct
> case
> when 'ct' = 'CT' then 'CTM'
> when 'ct' = 'SD' then 'SDM'
> when 'ct' = 'TFVT' then 'VTM'
> end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> Locality, Location,
> Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> Charge_Key,
> Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> from asset
> where category = 'ct'
> and asset_Id not in (60325, 60377,60274, 60397)
> and ((len(local_id) < 6 and local_id = 'AV-A196')
> or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) => 'AV-A196'))
> I have tested this on a SQL Server 2000 machine by creating a new
> database, importing the asset table of 66000 records. Then created
> new indexes on Asset_id (clustered primary key)
> Local_id (unique index)
> Category (index)
> If I remove any of the conditions in the where statement it runs fine.
> However with them all in their it breaks.
> I am running 8.00.859 (sp3)
> Any Suggestions
> Ben
Maybe I'm reading the SQL statement incorrectly, but how would this
statement ever be true:
(len(local_id) < 6 and local_id = 'AV-A196')
That would seem to imply the lengh of the column must be 5 or less _and_
it must be equal to a 7 character string, at the same time.
Just curious.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:
> BenJamin wrote:
> > Hi,
> >
> > I have written a query that gives the following message
> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> > (CheckforData()).
> > Server: Msg 11, Level 16, State 1, Line 0
> > General network error. Check your network documentation.
> >
> > Connection Broken
> >
> > I have seen several posts with this problem but none seem to match
> > mine. here is the query
> > select distinct
> > case
> > when 'ct' = 'CT' then 'CTM'
> > when 'ct' = 'SD' then 'SDM'
> > when 'ct' = 'TFVT' then 'VTM'
> > end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> > Locality, Location,
> > Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> > Charge_Key,
> > Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> > GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> > from asset
> > where category = 'ct'
> > and asset_Id not in (60325, 60377,60274, 60397)
> > and ((len(local_id) < 6 and local_id = 'AV-A196')
> > or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) => > 'AV-A196'))
> >
> > I have tested this on a SQL Server 2000 machine by creating a new
> > database, importing the asset table of 66000 records. Then created
> > new indexes on Asset_id (clustered primary key)
> > Local_id (unique index)
> > Category (index)
> >
> > If I remove any of the conditions in the where statement it runs fine.
> > However with them all in their it breaks.
> >
> > I am running 8.00.859 (sp3)
> > Any Suggestions
> > Ben
> Maybe I'm reading the SQL statement incorrectly, but how would this
> statement ever be true:
> (len(local_id) < 6 and local_id = 'AV-A196')
> That would seem to imply the lengh of the column must be 5 or less _and_
> it must be equal to a 7 character string, at the same time.
> Just curious.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Fair Call,
That part is just there as a means to stop the statement trying to process
Local_ID's less than 6 characters long with the second part of the condition
statement. It is meant to always be false.
I was gettting incorrect length to the substring function. I tried to get it
to process the conditions in the order that they are written to exclude the
unwanted rows. Maybe there is a way, I don't know.
But thanks for the quick response though...|||Hi
Check your SQL Server log. You may find that your process was terminated at
a server level. If it was, try to go to 8.00.0878 and if that does not solve
it, open a call with PSS.
Regards
Mike
"BenJamin" wrote:
>
> "David Gugick" wrote:
> > BenJamin wrote:
> > > Hi,
> > >
> > > I have written a query that gives the following message
> > > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> > > (CheckforData()).
> > > Server: Msg 11, Level 16, State 1, Line 0
> > > General network error. Check your network documentation.
> > >
> > > Connection Broken
> > >
> > > I have seen several posts with this problem but none seem to match
> > > mine. here is the query
> > > select distinct
> > > case
> > > when 'ct' = 'CT' then 'CTM'
> > > when 'ct' = 'SD' then 'SDM'
> > > when 'ct' = 'TFVT' then 'VTM'
> > > end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> > > Locality, Location,
> > > Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> > > Charge_Key,
> > > Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> > > GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> > > from asset
> > > where category = 'ct'
> > > and asset_Id not in (60325, 60377,60274, 60397)
> > > and ((len(local_id) < 6 and local_id = 'AV-A196')
> > > or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) => > > 'AV-A196'))
> > >
> > > I have tested this on a SQL Server 2000 machine by creating a new
> > > database, importing the asset table of 66000 records. Then created
> > > new indexes on Asset_id (clustered primary key)
> > > Local_id (unique index)
> > > Category (index)
> > >
> > > If I remove any of the conditions in the where statement it runs fine.
> > > However with them all in their it breaks.
> > >
> > > I am running 8.00.859 (sp3)
> > > Any Suggestions
> > > Ben
> >
> > Maybe I'm reading the SQL statement incorrectly, but how would this
> > statement ever be true:
> >
> > (len(local_id) < 6 and local_id = 'AV-A196')
> >
> > That would seem to imply the lengh of the column must be 5 or less _and_
> > it must be equal to a 7 character string, at the same time.
> >
> > Just curious.
> >
> >
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
> Fair Call,
> That part is just there as a means to stop the statement trying to process
> Local_ID's less than 6 characters long with the second part of the condition
> statement. It is meant to always be false.
> I was gettting incorrect length to the substring function. I tried to get it
> to process the conditions in the order that they are written to exclude the
> unwanted rows. Maybe there is a way, I don't know.
> But thanks for the quick response though...|||Ben
Add option (loop join) in your select statement
"BenJamin" <BenJamin@.discussions.microsoft.com> wrote in message
news:81A67F85-4D1E-4109-8090-6AFFE437DA63@.microsoft.com...
>
> "David Gugick" wrote:
> > BenJamin wrote:
> > > Hi,
> > >
> > > I have written a query that gives the following message
> > > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> > > (CheckforData()).
> > > Server: Msg 11, Level 16, State 1, Line 0
> > > General network error. Check your network documentation.
> > >
> > > Connection Broken
> > >
> > > I have seen several posts with this problem but none seem to match
> > > mine. here is the query
> > > select distinct
> > > case
> > > when 'ct' = 'CT' then 'CTM'
> > > when 'ct' = 'SD' then 'SDM'
> > > when 'ct' = 'TFVT' then 'VTM'
> > > end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> > > Locality, Location,
> > > Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> > > Charge_Key,
> > > Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> > > GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> > > from asset
> > > where category = 'ct'
> > > and asset_Id not in (60325, 60377,60274, 60397)
> > > and ((len(local_id) < 6 and local_id = 'AV-A196')
> > > or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) => > > 'AV-A196'))
> > >
> > > I have tested this on a SQL Server 2000 machine by creating a new
> > > database, importing the asset table of 66000 records. Then created
> > > new indexes on Asset_id (clustered primary key)
> > > Local_id (unique index)
> > > Category (index)
> > >
> > > If I remove any of the conditions in the where statement it runs fine.
> > > However with them all in their it breaks.
> > >
> > > I am running 8.00.859 (sp3)
> > > Any Suggestions
> > > Ben
> >
> > Maybe I'm reading the SQL statement incorrectly, but how would this
> > statement ever be true:
> >
> > (len(local_id) < 6 and local_id = 'AV-A196')
> >
> > That would seem to imply the lengh of the column must be 5 or less _and_
> > it must be equal to a 7 character string, at the same time.
> >
> > Just curious.
> >
> >
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
> Fair Call,
> That part is just there as a means to stop the statement trying to process
> Local_ID's less than 6 characters long with the second part of the
condition
> statement. It is meant to always be false.
> I was gettting incorrect length to the substring function. I tried to get
it
> to process the conditions in the order that they are written to exclude
the
> unwanted rows. Maybe there is a way, I don't know.
> But thanks for the quick response though...|||Check the traffic on your network, the query maybe
overloading it.
Peter
"A politician needs the ability to foretell what is going
to happen tomorrow, next week, next month, and next year.
And to have the ability afterwards to explain why it
didn't happen."
Winston Churchill
>--Original Message--
>
>"David Gugick" wrote:
>> BenJamin wrote:
>> > Hi,
>> >
>> > I have written a query that gives the following
message
>> > [Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionCheckForData
>> > (CheckforData()).
>> > Server: Msg 11, Level 16, State 1, Line 0
>> > General network error. Check your network
documentation.
>> >
>> > Connection Broken
>> >
>> > I have seen several posts with this problem but none
seem to match
>> > mine. here is the query
>> > select distinct
>> > case
>> > when 'ct' = 'CT' then 'CTM'
>> > when 'ct' = 'SD' then 'SDM'
>> > when 'ct' = 'TFVT' then 'VTM'
>> > end, null, 'L', 'AV-A196', 'AV-A196', null,
null, Service_Area,
>> > Locality, Location,
>> > Segment_Ref, Vicinity, Map_Ref, Service_Status,
Condition_Status,
>> > Charge_Key,
>> > Asset_Owner, Resp_Area, Resp_Position,
Data_Changed, null,
>> > GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
>> > from asset
>> > where category = 'ct'
>> > and asset_Id not in (60325, 60377,60274, 60397)
>> > and ((len(local_id) < 6 and local_id = 'AV-A196')
>> > or (len(local_id) >= 6 and substring(local_id, 1, len
(local_id)-6) =>> > 'AV-A196'))
>> >
>> > I have tested this on a SQL Server 2000 machine by
creating a new
>> > database, importing the asset table of 66000 records.
Then created
>> > new indexes on Asset_id (clustered primary key)
>> > Local_id (unique index)
>> > Category (index)
>> >
>> > If I remove any of the conditions in the where
statement it runs fine.
>> > However with them all in their it breaks.
>> >
>> > I am running 8.00.859 (sp3)
>> > Any Suggestions
>> > Ben
>> Maybe I'm reading the SQL statement incorrectly, but
how would this
>> statement ever be true:
>> (len(local_id) < 6 and local_id = 'AV-A196')
>> That would seem to imply the lengh of the column must
be 5 or less _and_
>> it must be equal to a 7 character string, at the same
time.
>> Just curious.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>>
>Fair Call,
>That part is just there as a means to stop the statement
trying to process
>Local_ID's less than 6 characters long with the second
part of the condition
>statement. It is meant to always be false.
>I was gettting incorrect length to the substring
function. I tried to get it
>to process the conditions in the order that they are
written to exclude the
>unwanted rows. Maybe there is a way, I don't know.
>But thanks for the quick response though...
>.
>

connection

how to overcome this error "SELECT permission denied on object 'UserDetails', database 'LOGIN', schema 'dbo'."

you have to give your user, or all users (public) rights to select from this table

USE LOGIN;GRANT SELECT ON OBJECT::dbo.USERDETAILS TO PUBLIC;GO

|||

for this question

"(how to overcome this error "SELECT permission denied on object 'UserDetails', database 'LOGIN', schema 'dbo'.")"

u have given answer the answer as

you have to give your user, or all users (public) rights to select from this table

USE LOGIN;
GRANT SELECT ON OBJECT::dbo.USERDETAILS TO PUBLIC;
GO

Where I have to write the code given by u

my requirement is asp.net2.0 using sqlserver.

I have created a Login database in sqlserver2005 and trying to access the connection but it is giving the error as

"SELECT permission denied on object 'UserDetails', database 'LOGIN', schema 'dbo'."

plz say how to set permission in sqlserver to access the data"LOGIN" ,schema 'dbo'"

connection

Hi All,
I have two DB's with same store procedures but different data on the tables,
and base on a condition I want to be able to select a different DB.
Can I change database connection in dynamic way from Reporting Services for
sql2000?
How?
Tks in advance...
JFByou can use this...
exec server_name.db_name.dbo<owner>.proc_name pram1,pram2
JFB wrote:
> Hi All,
> I have two DB's with same store procedures but different data on the tables,
> and base on a condition I want to be able to select a different DB.
> Can I change database connection in dynamic way from Reporting Services for
> sql2000?
> How?
> Tks in advance...
> JFB