@@Error query

Hi,
This is sort-of a follow up to a question I posted yesterday about turning
SQL Server error messages off.
After some more looking around I have found reference that if you use
@.@.ERROR to get the last error, this should stop the error being reported bac
k
to the client. However when I try this the error still gets reported in, fo
r
example, query analyser, should this be the case?
My test SP is listed below.
Thanks,
Steve
CREATE PROCEDURE [dbo].[divzero]
AS
DECLARE @.ErrNum int
SELECT @.ErrNum = 100/0
SELECT @.ErrNum = @.@.ERROR
SELECT 'DivZero'
GOYou cannot supress an error in T-SQL. Of course, you can check the @.@.ERROR
and take an action based on the @.@.ERROR value, but the client will still
receive the error message.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
Hi,
This is sort-of a follow up to a question I posted yesterday about turning
SQL Server error messages off.
After some more looking around I have found reference that if you use
@.@.ERROR to get the last error, this should stop the error being reported
back
to the client. However when I try this the error still gets reported in,
for
example, query analyser, should this be the case?
My test SP is listed below.
Thanks,
Steve
CREATE PROCEDURE [dbo].[divzero]
AS
DECLARE @.ErrNum int
SELECT @.ErrNum = 100/0
SELECT @.ErrNum = @.@.ERROR
SELECT 'DivZero'
GO|||> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
> back
> to the client.
No, using @.@.ERROR will not stop errors from being raised on the client.
Even if you handle errors in Transact-SQL, your client app needs to be aware
that errors will be raised.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> Hi,
> This is sort-of a follow up to a question I posted yesterday about turning
> SQL Server error messages off.
> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
> back
> to the client. However when I try this the error still gets reported in,
> for
> example, query analyser, should this be the case?
> My test SP is listed below.
> Thanks,
> Steve
> CREATE PROCEDURE [dbo].[divzero]
> AS
> DECLARE @.ErrNum int
> SELECT @.ErrNum = 100/0
> SELECT @.ErrNum = @.@.ERROR
> SELECT 'DivZero'
> GO
>|||Still it's reported.. with an annoying side-effect : If you are running
some code lauched by the SQL Agent, the job will finished after the first
error is raised, no matter the code you may have written to handle the
error... not that nice so to speak.
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> Hi,
> This is sort-of a follow up to a question I posted yesterday about turning
> SQL Server error messages off.
> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
back
> to the client. However when I try this the error still gets reported in,
for
> example, query analyser, should this be the case?
> My test SP is listed below.
> Thanks,
> Steve
> CREATE PROCEDURE [dbo].[divzero]
> AS
> DECLARE @.ErrNum int
> SELECT @.ErrNum = 100/0
> SELECT @.ErrNum = @.@.ERROR
> SELECT 'DivZero'
> GO
>|||So, is there no way of stopping SQL Server from reporting this divide-by-zer
o
error? The problem is that I am running the SP from ASP.NET, and even thoug
h
I handle the error in the SP, the ASP.NET code gets an Exception and errors
before I get the data back.
Any ideas of how to get round this (there is no way of stopping the divide
by zero error in the SP, I have to run SQL from a field in a table which may
or may not cause it)
Thanks,
Steve
"Chris V." wrote:

> Still it's reported.. with an annoying side-effect : If you are running
> some code lauched by the SQL Agent, the job will finished after the first
> error is raised, no matter the code you may have written to handle the
> error... not that nice so to speak.
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
> "Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
> news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> back
> for
>
>|||Well, so far, the way I used is to have a SP calling the SP..
SP_lancher call the SP_active with OSQL, if SP_Active raises an error, it
raises it to OSQL which discards it, hidding it for the client.
I'm not pretending it's an elegant solution..but it's working :)
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> So, is there no way of stopping SQL Server from reporting this
divide-by-zero
> error? The problem is that I am running the SP from ASP.NET, and even
though
> I handle the error in the SP, the ASP.NET code gets an Exception and
errors
> before I get the data back.
> Any ideas of how to get round this (there is no way of stopping the divide
> by zero error in the SP, I have to run SQL from a field in a table which
may
> or may not cause it)
> Thanks,
> Steve
> "Chris V." wrote:
>
first
turning
reported
in,|||The only thing I can think of is if you use ADO.NET in which case you can
trap the error there.
"Steve Norman" wrote:
> So, is there no way of stopping SQL Server from reporting this divide-by-z
ero
> error? The problem is that I am running the SP from ASP.NET, and even tho
ugh
> I handle the error in the SP, the ASP.NET code gets an Exception and error
s
> before I get the data back.
> Any ideas of how to get round this (there is no way of stopping the divide
> by zero error in the SP, I have to run SQL from a field in a table which m
ay
> or may not cause it)
> Thanks,
> Steve
> "Chris V." wrote:
>|||"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> So, is there no way of stopping SQL Server from reporting this
divide-by-zero
> error? The problem is that I am running the SP from ASP.NET, and even
though
Sure there is -- make sure you don't have a divide-by-zero error to
begin with.
CASE denominator
WHEN 0.0 THEN 0.0
ELSE numerator / denominator
END
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Apologies about the brain dead reply.
Silly question time...
Why can you have a check on the amount that your dividing by i.e
if @.Amount > 0
Set @.Division = @.Division / @.Amount.
Formally Known as 'Peter The Spate'
"Never ascribe to malice, that which can be explained by incompetence."
Napoleon
"Peter" wrote:
> The only thing I can think of is if you use ADO.NET in which case you can
> trap the error there.
> "Steve Norman" wrote:
>|||I don't think in my case I would be able to do that. Reason being that a
user will enter a formula in an ASP.NET page which can be any equation they
want based upon any number of data columns they choose, my SP will then do
they calculation and return the result.
So unless I do some pretty complicated string manipulation on the formula to
add in case statements, I cannot stop the divide by zero error. However I d
o
handle the error and the SP will continue on and correct the data itself, bu
t
the calling ASP.NET page only reports the error that gets raised by SQL
Server, so I need a way of stopping this error getting back to the ASP.NET
page (SQLClient object).
I'm beginning to think I cannot do it?
Steve
"Adam Machanic" wrote:

> "Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
> news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> divide-by-zero
> though
> Sure there is -- make sure you don't have a divide-by-zero error to
> begin with.
> CASE denominator
> WHEN 0.0 THEN 0.0
> ELSE numerator / denominator
> END
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

@ local variable makes query slower?

Hi all,

I have a query that scans huge table consists of 8 or more millions
records. The funny thing is that if I use the query with local
variable, the query takes more than 1 minutes, whereas if I hard code
the value into the query, it takes about 1 second. Here are the
queries:

WITH VARIABLE:
------

DECLARE @.i_StartDate DATETIME
DECLARE @.i_EndDate DATETIME
SET @.i_StartDate = '2004-04-26'
SET @.i_EndDate = '2004-04-28'

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)

...And some other conditions

-------

WITHOUT VARIABLE:

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')

...And some other conditions

-------

The later one runs significantly faster than the first one. I've
isolated the problem at the local variable @.i_StartDate and
@.i_EndDate. Can somebody help me out, Please...

Thank you,
Michelle."Michelle" <michelletran@.harmonyremote.com> wrote in message
news:56c5b7ab.0404260656.281cfc40@.posting.google.c om...
> Hi all,
> I have a query that scans huge table consists of 8 or more millions
> records. The funny thing is that if I use the query with local
> variable, the query takes more than 1 minutes, whereas if I hard code
> the value into the query, it takes about 1 second. Here are the
> queries:
> WITH VARIABLE:
> ------
> DECLARE @.i_StartDate DATETIME
> DECLARE @.i_EndDate DATETIME
> SET @.i_StartDate = '2004-04-26'
> SET @.i_EndDate = '2004-04-28'
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)
> ...And some other conditions
> -------
> WITHOUT VARIABLE:
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')
> ...And some other conditions
> -------
> The later one runs significantly faster than the first one. I've
> isolated the problem at the local variable @.i_StartDate and
> @.i_EndDate. Can somebody help me out, Please...
> Thank you,
> Michelle.

This may be an example of parameter sniffing - see this post, for example,
which describes an almost identical case:

http://groups.google.com/groups?hl=...ftngp13.phx.gbl

Simon|||Thanks Simon.
Michelle.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Queries in While Condition

When using a query in the condition of a while loop, is that query being performed each time?
EX:
While (@.X <= (Select count(*) from SOME_TABLE))I was going to say no...but...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E'
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < (SELECT COUNT(*) FROM myTable99)
BEGIN
SELECT @.x = @.x + 1
END
SELECT @.x

SELECT @.x = 1
WHILE @.x < (SELECT COUNT(*) FROM myTable99)
BEGIN
IF @.x IN (1,3,5) INSERT INTO myTable99(Col2) SELECT 'x'
SELECT @.x = @.x + 1
END
SELECT @.x
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

HLEP..Sql Server UPDATE INNER JOIN QUERY ..

Im using an ADP to connect to a SQL Sqever DB.
In access it was really easy to say

Inner join on table1 and table2 and update columnA from table1 with
columnC from table2 where table1.key = table2.key and table2 columnB =
1 and table2 columnD = 4

I have tried all manner of beasts to get this thing to work..

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))

This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0' part which is really important

Any Help would be great...This way looks like it should work also but I get an error 'ADO Error:
HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
can only show one table in an update qurey?

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0))
WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)|||SAME ADO ERROR WHEN I USE THIS ???

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
FROM GIS_EVENTS_TEMP G INNER JOIN
HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
WHERE (H.UNITID = 1 AND H.LISTORDER = 0)|||(meyvn77@.yahoo.com) writes:
> Im using an ADP to connect to a SQL Sqever DB.
> In access it was really easy to say
> Inner join on table1 and table2 and update columnA from table1 with
> columnC from table2 where table1.key = table2.key and table2 columnB =
> 1 and table2 columnD = 4
>
> I have tried all manner of beasts to get this thing to work..
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
> AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))
> This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0' part which is really important
> Any Help would be great...

Unfortunately, it's not very easy to help if we don't know what
tables you have. The standard recommendation for this type of
problem is to post:

o CREATE TABLE statements of the tables nvolved. (Preferrably
cut down to the columns relevant to the problem.(
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the business problem.

The two first points makes it easy to copy and paste into Query Analyzer,
so a tested solution can be developed. The third point makes it possible
to verify that the solution is correct. And the fourth point gives some
extra information which helps to understand the general problem.

> This way looks like it should work also but I get an error 'ADO Error:
> HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
> can only show one table in an update qurey?
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0))
> WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)

No, but because you are referring to HARMFULEVENT outside the subquery.

> AME ADO ERROR WHEN I USE THIS ???
>
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
> FROM GIS_EVENTS_TEMP G INNER JOIN
> HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
> WHERE (H.UNITID = 1 AND H.LISTORDER = 0)

Here you are mixing use of aliases and table name. Once you have
introduced an alias, you can not refer to the full table name in
the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I don't think that you can write something like this:
SELECT HARMFULEVENT FROM HARMFULEVENT

.Net passing bad formatted value to stored procedure

Hi, that's the problem:

I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields.

When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler:

exec sel_despesa_procura @.codigo=NULL,@.fornecedor=NULL,@.descricao=NULL,@.vencto_ini=''2005-10-10 00:00:00:000'',@.vencto_fim=''2005-10-20
00:00:00:000'',@.pagto_ini=NULL,@.pagto_fim=NULL,@.valor=NULL,@.valor_pago=NULL,
@.centro_custo=NULL,@.pago=N'0,1'

The fields are defined as follows:

<SelectParameters>
...
<asp:ControlParameterControlID="txtFiltroVencIni"Name="vencto_ini"PropertyName="Text"Type="DateTime"/>
<asp:ControlParameterControlID="txtFiltroVencFim"Name="vencto_fim"PropertyName="Text"Type="DateTime"/>
...
</SelectParameters>

The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '2005'.

I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.

Thanks in advance,

Anderson

That is not it's normal behavior. Just for laughs, set the pages culture and uiculture to "en-US", and run the page again and see if the problem disappears. If it does, then it's a bug with your particular culture settings.

*Complex* Grouping in View - help needed

Hello,

I am having difficulty to find the right SQL query to create a View as i illustrate below.

Senario:

Criteria Table

Idn

Key1

Key2

Key3

TagId

1

A

C

B

100

2

A

NULL

B

200

3

B

D

NULL

300

Data Table

DataId

Key1

1

A

2

B

SubData Table

SubDataId

DataId

Key2

Key3

1

1

C

B

2

1

Z

B

3

1

X

B

4

2

D

Z

And below is my expected View:

TagId

Key1

Key2

Key3

100

A

C

B

200

A

Z

B

200

A

X

B

300

B

D

Z

I managed to get query that will be able to get above result, however it is teribbly slow. it took 1 minutes to query 1000 records from the view that i have created. My records are roughly around 80K++.

I would really appreciate if anyone could help me to make if faster or point me where i did wrong.

Below is sample T-SQL that which i can illustrate my situation:

set nocount on
create table #Criteria
(
idn int
,Key1 char(1)
,Key2 char(1)
,Key3 char(1)
,TagId int
)

create table #Data
(
DataId int
,Key1 char(1)
)

create table #SubData
(
SubDataId int
,DataId int
,Key2 char(1)
,Key3 char(1)
)

insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', 'C', 'B', 100)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', NULL, 'B', 200)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'B', 'D', NULL, 300)

insert #Data(DataId, Key1)
values (1, 'A')
insert #Data(DataId, Key1)
values (2, 'B')

insert #SubData(SubDataId, DataId, Key2, Key3)
values (1, 1, 'C', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (2, 1, 'Z', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (3, 1, 'X', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (4, 2, 'D', 'Z')

select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId

/** here is the query logic i used in the view **/
select min(#Criteria.TagId)
,ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3
from #Criteria
left join ( select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId ) as ConsolidatedData
on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL
and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL
and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL
group by ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3

drop table #Criteria, #Data, #SubData

P/s: i urgently need your feedback on this one.

Thank You!!!

sibikos@.hotmail.com

You posted a query but depending on you explanation maybe there's a completely different approach...

May you please explain which is the expected result ? something like "I would get a table that shows the key1 for each ...|||

Sorry for the confusion.. i always have trouble on explaining thing well :P

Basically, From the Criteria, Data and SubData tables that i've listed above, i would like to create a view as below

TagId

Key1

Key2

Key3

100

A

C

B

200

A

Z

B

200

A

X

B

300

B

D

Z

Currently i am using the query below query (well, not exactly the same but the logic is there) to get the view that i wanted but it is too slow for me. Just wondering if you guys have better solution.

select min(#Criteria.TagId)
,ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3
from #Criteria
left join ( select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId ) as ConsolidatedData
on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL
and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL
and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL
group by ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3

Thanks

|||You had already posted those info... from the result you posted I see that you would like to obtain key1, 2 & 3 for each tagid in the criteria table, but is not clear to me why do you used the min(..) function... so I guess I haven't correctly understood what you'd like to accomplish... please explain with your own words which is the goal of the view, not data... words :)

*= Left Outer Join

I have three related tables A,B and C. In sql server 2000 I can run next query:
select * from
A left outer join B on A.fk=B.i
left outer join C on B.fk=C.i
but below query give me an "invalid outer combination" error:
select * from
A,B,
where
A.fk*=B.id an
B.fk*=C.i
It seems outer combinations configured in where clause are limited to two tables, although I have seen some examples of more than two tables in the web (but unfortunately author didnâ't inform about sql version). Has changed the behaviour of *= changed in the 2000 versionWhy do you want to use deprecated syntax that will not be supported in a
future version?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David Palomar" <anonymous@.discussions.microsoft.com> wrote in message
news:5F0EAF81-A9CD-4E0C-A57D-DC5CC8CDB47F@.microsoft.com...
> I have three related tables A,B and C. In sql server 2000 I can run next
query:
> select * from
> A left outer join B on A.fk=B.id
> left outer join C on B.fk=C.id
> but below query give me an "invalid outer combination" error:
> select * from
> A,B,C
> where
> A.fk*=B.id and
> B.fk*=C.id
> It seems outer combinations configured in where clause are limited to two
tables, although I have seen some examples of more than two tables in the
web (but unfortunately author didnâ?Tt inform about sql version). Has
changed the behaviour of *= changed in the 2000 version?
>|||Thatâ's very true Aaron, but I',m using an automatic persistence tool which generates this kind of joins and I can't do anything about. So, if possible, I would like to know why it doesn't works in sql server 2000 (in order to complain) and if there would be a way to circumvent the problem in the mean time.
-- Aaron Bertrand - MVP wrote: --
Why do you want to use deprecated syntax that will not be supported in
future version
--
Aaron Bertran
SQL Server MV
http://www.aspfaq.com
"David Palomar" <anonymous@.discussions.microsoft.com> wrote in messag
news:5F0EAF81-A9CD-4E0C-A57D-DC5CC8CDB47F@.microsoft.com..
> I have three related tables A,B and C. In sql server 2000 I can run nex
query
>> select * fro
> A left outer join B on A.fk=B.i
> left outer join C on B.fk=C.i
>> but below query give me an "invalid outer combination" error
>> select * fro
> A,B,
> wher
> A.fk*=B.id an
> B.fk*=C.i
>> It seems outer combinations configured in where clause are limited to tw
tables, although I have seen some examples of more than two tables in th
web (but unfortunately author didnâ?Tt inform about sql version). Ha
changed the behaviour of *= changed in the 2000 version
>|||There are restriction for old-style joins. I don't know them all by heart
(you should be able to find them in Books Online), but I believe that one is
that you can't do more than one OJ in a query. You really need to get a
version of your tool that supports the new proper way to specify an outer
join.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"David Palomar" <anonymous@.discussions.microsoft.com> wrote in message
news:3DF4DD3C-DF25-42C6-B8C4-5B504F1E5A04@.microsoft.com...
> That's very true Aaron, but I',m using an automatic persistence tool which
generates this kind of joins and I can't do anything about. So, if
possible, I would like to know why it doesn't works in sql server 2000 (in
order to complain) and if there would be a way to circumvent the problem in
the mean time.
> -- Aaron Bertrand - MVP wrote: --
> Why do you want to use deprecated syntax that will not be supported
in a
> future version?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "David Palomar" <anonymous@.discussions.microsoft.com> wrote in
message
> news:5F0EAF81-A9CD-4E0C-A57D-DC5CC8CDB47F@.microsoft.com...
> > I have three related tables A,B and C. In sql server 2000 I can run
next
> query:
> >> select * from
> > A left outer join B on A.fk=B.id
> > left outer join C on B.fk=C.id
> >> but below query give me an "invalid outer combination" error:
> >> select * from
> > A,B,C
> > where
> > A.fk*=B.id and
> > B.fk*=C.id
> >> It seems outer combinations configured in where clause are limited
to two
> tables, although I have seen some examples of more than two tables in
the
> web (but unfortunately author didnâ?Tt inform about sql version). Has
> changed the behaviour of *= changed in the 2000 version?
> >

**find connected application**

Hi
I'm working with SQL 2000 and I want to know how can I avoid my users
connecting via other app such as:SQL query analyzer,MS Access,... in order
to modify data, when they are connecting through VB in their interfaces?
in other word, I want to limit the ways of connecting to DBs for a
particular login name (for example: login1)?
Any help would be thankful.With SQL 2000, that is not easy to accomplish. A user can use his/her login
credentials to access SQL Server and whatever objects he/she has permissions
for using any client side tool they choose.
You may wish to investigate using an Application Role, providing access and
permissions to that Application Role only, and not providing access and
permissions to any other login credentials for users. You would want to be
sure to assign explicit permissions, use stored procedures for data access,
and deny direct table access.
Here are some articles to start your research:
Security -Best Practices
http://vyaskn.tripod.com/sql_server...t_practices.htm
Security -Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tjl2pwupn9ig5y@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000 and I want to know how can I avoid my users
> connecting via other app such as:SQL query analyzer,MS Access,... in order
> to modify data, when they are connecting through VB in their interfaces?
> in other word, I want to limit the ways of connecting to DBs for a
> particular login name (for example: login1)?
> Any help would be thankful.|||Hi M,
Please drop me a note if the following description sounds interesting to
you.
Best regards
Adrian
Here we go:
Corrupt users and compromised user-accounts, cf Phishing, account for the
majority of attacks in the commercial world. We present a two-stage
anti-corruption system (ACS) for database servers that, in the first stage,
makes it very hard to gain access to a database with an unauthorised client
application or from an unauthorised client PC and, in the second stage,
provides
early precise hints on users performing suspicious activities. Our first
implementation of the ACS is for the MS SQL Server 2000. It uses only
documented
functions and relies completely on mechanisms and data already provided by
the
database server. Its smooth and efficient operation for several months in a
middle-sized decentralised company proves it a valuable addition to the pool
of
security measures.
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tjl2pwupn9ig5y@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000 and I want to know how can I avoid my users
> connecting via other app such as:SQL query analyzer,MS Access,... in order
> to modify data, when they are connecting through VB in their interfaces?
> in other word, I want to limit the ways of connecting to DBs for a
> particular login name (for example: login1)?
> Any help would be thankful.

*********How to settle this...***********

I have a big database to test my report, the report is made by reporting
service.
But when the query for the report get too many data such as more than
100,000 rows for the dataset, My computer is down because of lacking memory.
What can I do ?If you are on SQL Server, you can add TOP 20 or so to the query while you test. Like:
SELECT TOP 20 col1, col2
FROM ...
...
But it might be better to tune the query, look at the execution plan, perhaps create indexes to
support the query etc. This depends on where the bottleneck is. Perhaps it is the RD environment
which becomes sluggish with that many rows, and tweaking in SQL server wouldn't help in that case.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cao tt" <Cao tt@.discussions.microsoft.com> wrote in message
news:4F14BC17-A381-4219-B124-EC2D6D5B3256@.microsoft.com...
> I have a big database to test my report, the report is made by reporting
> service.
> But when the query for the report get too many data such as more than
> 100,000 rows for the dataset, My computer is down because of lacking memory.
> What can I do ?|||Are you wanting the report to have 100,000 row result set? If so, you are
going to find that there will be problems with RS. It does certain actions
in memory and that large a resultset is a problem. Note that it is the
number of rows returned that matters. Not the size of the table on the
backend. I have 20 million row tables I am going against. The main issue
when going against large tables is do not use filters. Filters return all
the data and then filters it. Use query parameters and have only the data
you need downloaded. Or if you have aggregates, have that occur via the
query as well. Another useful technique is to use drill through instead of
drill down. With drill down you can end up with huge datasets. By using
drill through instead you can limit the data.
This issue (100,000 row resultset) is not really specific to RS. It is
always best to limit the data going to the client. No human is going to look
at 100,000 rows of data.
Bruce L-C
"Cao tt" <Cao tt@.discussions.microsoft.com> wrote in message
news:4F14BC17-A381-4219-B124-EC2D6D5B3256@.microsoft.com...
> I have a big database to test my report, the report is made by reporting
> service.
> But when the query for the report get too many data such as more than
> 100,000 rows for the dataset, My computer is down because of lacking
memory.
> What can I do ?

(SSAS 2K5) MDX and Translations : how does it work ?

Hi,

When you run a MDX query against an SSAS 2K5 cube that has been created with translations, which of the languages can be used for dimension names, measures, attributes etc... ?

Is it possible to use a language which is not the default language ? and how ?

thx&rgds,
Francois

Translations do NOT affect the names of objects (dimensions, measures, etc.), just their captions. MDX queries use object names so they are not affected by translations.

(Select All) Checkbox As Default

All,

When creating a dynamic parameter list from a query, Reporting Services graciously provides a "(Select All)" checkbox. How can I select this checkbox by default so that when a user generates the report, all of the values are selected?

Thanks

The "select all" item is just a UI concept at this point. You could setup a default value for the report parameter that contains all values of the valid values list - but in that case all the checkboxes for individual items would be pre-selected, but the "select all" would still not be pre-selected.

-- Robert

|||Thanks for the reply. I actually tried your approach before posting my question. The problem that I am facing when using this approach is that the pre-selection is always static so this will not work when my checkbox list is populated from a query. Since the results of the query are dynamic, I can't setup a default list with any degree of accuracy.|||

Robert,

Is there any way to tell when the Select All option is selected?

R

|||

I don't know if this works

But if my dropdown source is Query, and with "Multi-Value items" checkbox checked

Under Default Values, If I choose "Query", pick the Dataset, put in the value field -> it will select all rows in the query by default

When I go to Preview, all items from the query ARE checked in the dropdown. Even the "ALL" option I created myself

(now the problem is, how do you uncheck them all with ease...)

I hope that's what you're looking for, I wish there's easy way to post pictures

|||

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

(Select All) Checkbox As Default

All,

When creating a dynamic parameter list from a query, Reporting Services graciously provides a "(Select All)" checkbox. How can I select this checkbox by default so that when a user generates the report, all of the values are selected?

Thanks

The "select all" item is just a UI concept at this point. You could setup a default value for the report parameter that contains all values of the valid values list - but in that case all the checkboxes for individual items would be pre-selected, but the "select all" would still not be pre-selected.

-- Robert

|||Thanks for the reply. I actually tried your approach before posting my question. The problem that I am facing when using this approach is that the pre-selection is always static so this will not work when my checkbox list is populated from a query. Since the results of the query are dynamic, I can't setup a default list with any degree of accuracy.|||

Robert,

Is there any way to tell when the Select All option is selected?

R

|||

I don't know if this works

But if my dropdown source is Query, and with "Multi-Value items" checkbox checked

Under Default Values, If I choose "Query", pick the Dataset, put in the value field -> it will select all rows in the query by default

When I go to Preview, all items from the query ARE checked in the dropdown. Even the "ALL" option I created myself

(now the problem is, how do you uncheck them all with ease...)

I hope that's what you're looking for, I wish there's easy way to post pictures

|||

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

(Select All) Checkbox As Default

All,

When creating a dynamic parameter list from a query, Reporting Services graciously provides a "(Select All)" checkbox. How can I select this checkbox by default so that when a user generates the report, all of the values are selected?

Thanks

The "select all" item is just a UI concept at this point. You could setup a default value for the report parameter that contains all values of the valid values list - but in that case all the checkboxes for individual items would be pre-selected, but the "select all" would still not be pre-selected.

-- Robert

|||Thanks for the reply. I actually tried your approach before posting my question. The problem that I am facing when using this approach is that the pre-selection is always static so this will not work when my checkbox list is populated from a query. Since the results of the query are dynamic, I can't setup a default list with any degree of accuracy.|||

Robert,

Is there any way to tell when the Select All option is selected?

R

|||

I don't know if this works

But if my dropdown source is Query, and with "Multi-Value items" checkbox checked

Under Default Values, If I choose "Query", pick the Dataset, put in the value field -> it will select all rows in the query by default

When I go to Preview, all items from the query ARE checked in the dropdown. Even the "ALL" option I created myself

(now the problem is, how do you uncheck them all with ease...)

I hope that's what you're looking for, I wish there's easy way to post pictures

|||

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

(seemingly) simple query question

I have a table that looks like this:

x t
- --
1 0
2 0
1 1
2 1
3 1

How do I query it to get this:

t0 t1
-- --
1 1
2 2
NULL 3

the formatting is messed up, but there are 2 fields: type0 with records 1,2,NULL and type1 with records 1,2,3

Please help if you can, this is urgent.
Thanks In Advance, Yury.I don't know which tables contain which data, but an outer join should do what you need.

SELECT table1.type0
, table2.type1
FROM table1 RIGHT JOIN table2
ON table1.field = table2.field

Hope this helps.