Showing posts with label link. Show all posts
Showing posts with label link. Show all posts

Wednesday, March 28, 2012

need help - Database has been moved to SQL server 2000 from SQL servere 6.5

Hi there,

I am not sure if this is the right place to seek help for the problem i have but as i don't see any other link to discuss the situation i have i am just posting it here. To explain a little bit about the project i am on.... Originally the appication(developed in asp.net and vb) i am assigned to was developed by a different team and most of the databases were on SQL server 6.5 servers. So they have used oledb connections where ever they had to connect SQL server 6.5 data sources. Lately the client has moved all of the sql server 6.5 data bases to SQL server 2000 and now the application is kinda not working as it is suppose to as it was before. So i am hired to fix the problem.

So as a first step what i am doing is finding out all of the .net oledb proivder part of the database connections code to SQL server 6.5 data sources and changing them to .NET provider for SQL server.

Say for example in the below GetConnection function i am commenting out OledbConnection and defining a SqlConnection instead.

PrivateFunction GetConnection()As IDbConnection

'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection

Dim connAs IDbConnection =New System.Data.SqlClient.SqlConnection

conn.ConnectionString = Helpers.DBHelpers.GetConnectionString(Helpers.DBHelpers.COMMON)

Return conn

EndFunction

and also commenting out OledbDataAdapter line of code and defining a SqlDataAdapter instead. You can see it below

'Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(CType(cmd, OleDb.OleDbCommand))

Dim adapterAs SqlClient.SqlDataAdapter =New SqlClient.SqlDataAdapter(CType(cmd, SqlClient.SqlCommand))

And connection strings are defined in web.config file and also i am changing those as well . See below.

<!--<add key="Common" value="User ID=Test;pwd=*****;Data Source=ESMALLDB2K;Initial Catalog=cj_common;Auto Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";" />-->

<addkey="Common"value="User ID=Test;pwd=*****;Data Source=ESMALLDB2K;Initial Catalog=cj_common;"/>

So i hope i am in the right direction as far as the first step.But please throw in any kind of suggestions on this.

One more thing. I have a search screen and T-sql query thats built for this purpose searches 4,5 tables and brings the data back.

When i make a search from the web browser it doesn't return the data for the first couple of times but it brings the data 3rd time but even its taking as long as 60 seconds to bring the data back. when i close the browser and debug and paste the SQL query in the query analyzer it returns the data in the query analyzer and when i complete the remaining part of debugging and bind the data to the gird i also see the data on the broswer for the first time itself.

Question : Why i don't get the data for the first time when i search it from the front(web browser)?

But like i said the executing time to the query in the query analyzer itself takes considerably long time( i would say around 60 seconds just to return 3,4 records)) in the query analyzer. When i talked to the database guys why sql queries are a little slow they say they have a lot of datat out there around 180 thousand records in it and thats why its taking that much time to search agains all of the rows.

Question - Do you think it could be some thing to do with dropping and recreating the indexes should solve our problem? May be its some thing to do with the indexes but i am sure they have not dropped out the indexes of all of the table objects and recreated yet after the databases are moved to SQL servere 2000.

Hope i am able to explaing what i am looking for and what i am doing.

Please help me understand in solving these problems. Thanks in advance

-D

I can write 200 pages and I have not finished the differences between SQL Server 6.5 and 2000, SQL6.5 is linked list based 2000 is on disk, page in 6.5 is 256k per page, 2000 8000k,6.5 no DRI(declarative referential integrity), 2000 DRI. Try the link below and download Neil Pike's FAQ. My advice get a copy of the database from your client and manually clean all the files to SQL Server 2000 as you could, then backup and restore about three increaments note all the changes so you don't loose any data and then change your data provider. BTW there is no direct upgrade from 6.5 to 2000 that may be your problem. Hope this helps.

http://www.mssqlserver.com/faq

Monday, March 12, 2012

need advise on sql error 999 and 10054, only happens when you walk

SQL Error 999 SQLSTATE=08501
[Microsoft][ODBC SQL Server Driver]Communication link failure
SQL Error 10054 SQLSTATE=01000
[Microsoft][ODBC SQL Server] [DBNETZIB]ConnectionWrite(Send())
hello,
We are getting the above errors from sql 2000 SP3.
it only happens when people return to their workstations and the screen
saver has turned on.
thank you,
OskarHi Oskar
Go to Control Panel / Power Options and set System Standby to Never.
The screen saver will continue to appear, but the network card should
not be turned off anymore.
Razvan

Wednesday, March 7, 2012

Need a kick in the right direction w/this query

Here is a link to the DDL since it would take up a lot of room here.
http://damageinc.org/DDL.html
After you use that to generate the table and some sample data, here is the
problem I need help with.
You will see the following data, if you use this query...
SELECT DISTINCT Apps.AppName, TestCases.TestCase , Tests.Attributes,
Reports.Result, Reports.ReportDate
FROM
Reports
LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.AppID
AppName TestCase Attribute
Result ReportDate
Test App 1 Run for 30 minutes Attribute 1 Fail
2006-06-25 19:58:31.800
Test App 1 Run for 30 minutes Attribute 1 Pass
2006-06-25 19:58:29.800
Test App 1 Run for 30 minutes Attribute 1 Pass
2006-06-25 19:58:30.800
Test App 1 Run for 45 minutes Attribute 2 Fail
2006-06-25 19:58:33.800
Test App 1 Run for 45 minutes Attribute 2 Fail
2006-06-25 19:58:34.800
Test App 1 Run for 45 minutes Attribute 2 Pass
2006-06-25 19:58:32.800
Test App 1 Run for 60 minutes Attribute 3 Fail
2006-06-25 19:58:36.863
Test App 1 Run for 60 minutes Attribute 3 Pass
2006-06-25 19:58:35.863
Test App 1 Run for 60 minutes Attribute 3 Pass
2006-06-25 19:58:37.863
What I need to get is the count most recent result per TestCase regardless
of attribute.
So I was trying to do it visually by using this query..
SELECT tc.TestCase, tc.mostRecent, tc.Result
FROM Reports rp
RIGHT JOIN
(
SELECT TestCases.TestCase ,Reports.Result, MAX ( Reports.ReportDate ) AS
mostRecent
FROM Reports
LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
WHERE ( (Tests.Attributes = 'Attribute 1') OR (Tests.Attributes =
'Attribute 2') OR (Tests.Attributes = 'Attribute 3') )
GROUP BY TestCases.TestCase, Result) tc ON (tc.mostRecent =
rp.ReportDate ) ORDER BY TestCAse
Which will give you this data...
TestCase mostRecent Result
Run for 30 minutes 2006-06-25 19:58:31.800 Fail
Run for 30 minutes 2006-06-25 19:58:30.800 Pass
Run for 45 minutes 2006-06-25 19:58:34.800 Fail
Run for 45 minutes 2006-06-25 19:58:32.800 Pass
Run for 60 minutes 2006-06-25 19:58:36.863 Fail
Run for 60 minutes 2006-06-25 19:58:37.863 Pass
Which as you can tell is giving me both the most recent pass & fail result,
where I want the most recent result regardless of Pass/Fail.
Which in turn, makes my count query wrong as well...
SELECT COUNT(DISTINCT tc.TestCase) As Count,
COUNT(DISTINCT CASE rp.Result WHEN 'Pass' THEN rp.ReportID ELSE NULL END) as
Pass,
COUNT(DISTINCT CASE rp.Result WHEN 'Fail' THEN rp.ReportID ELSE NULL END) as
Fail
FROM Reports rp
RIGHT JOIN
(
SELECT TestCases.TestCase ,Reports.Result, MAX ( Reports.ReportDate ) AS
mostRecent
FROM Reports
LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
WHERE ( (Tests.Attributes = 'Attribute 1') OR (Tests.Attributes =
'Attribute 2') OR (Tests.Attributes = 'Attribute 3') )
GROUP BY TestCases.TestCase, Result) tc ON (tc.mostRecent = rp.ReportDate )
Which will give me
Count Pass Fail
3 3 3
Instead of the result I am looking for of..
Count Pass Fail
3 1 2Select Count(*) As 'Count',
Sum(Case When Result = 'Pass' Then 1 Else 0 End) As 'Pass',
Sum(Case When Result = 'Fail' Then 1 Else 0 End) As 'Fail'
From Reports r
Where r.ReportDate In
(Select Max(r.ReportDate)
From Reports r
Inner Join TestCases tc On r.TestID = tc.TestCaseID
Inner Join Tests t On tc.TestCaseID = t.TestID
Inner Join Apps a On tc.AppID = a.AppID
Where t.Attributes In ('Attribute 1', 'Attribute 2', 'Attribute 3')
Group By a.AppName, tc.TestCase)
The above query assumes that there are no duplicates in Reports.ReportDate.
I changed your Left Outer Joins to Inner Joins since if for a Report row,
you don't have a TestCases row and a Tests row, then the Attributes column
will nave NULL in it, so the where condition would be false. So your Left
Outer Join gives the same result as an Inner Join, but Inner Joins are ofter
faster.
BTW, your DDL is not too long to post to this group (IMHO) and you will have
better luck if you just include it the post rather than a web site since
some people are reluctant to go to an unknown site.
Tom
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:uJYRRBNmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> Here is a link to the DDL since it would take up a lot of room here.
> http://damageinc.org/DDL.html
> After you use that to generate the table and some sample data, here is the
> problem I need help with.
> You will see the following data, if you use this query...
>
> SELECT DISTINCT Apps.AppName, TestCases.TestCase , Tests.Attributes,
> Reports.Result, Reports.ReportDate
> FROM
> Reports
> LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
> LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
> LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.AppID
> AppName TestCase Attribute Result
> ReportDate
> Test App 1 Run for 30 minutes Attribute 1 Fail
> 2006-06-25 19:58:31.800
> Test App 1 Run for 30 minutes Attribute 1 Pass
> 2006-06-25 19:58:29.800
> Test App 1 Run for 30 minutes Attribute 1 Pass
> 2006-06-25 19:58:30.800
> Test App 1 Run for 45 minutes Attribute 2 Fail
> 2006-06-25 19:58:33.800
> Test App 1 Run for 45 minutes Attribute 2 Fail
> 2006-06-25 19:58:34.800
> Test App 1 Run for 45 minutes Attribute 2 Pass
> 2006-06-25 19:58:32.800
> Test App 1 Run for 60 minutes Attribute 3 Fail
> 2006-06-25 19:58:36.863
> Test App 1 Run for 60 minutes Attribute 3 Pass
> 2006-06-25 19:58:35.863
> Test App 1 Run for 60 minutes Attribute 3 Pass
> 2006-06-25 19:58:37.863
>
> What I need to get is the count most recent result per TestCase regardless
> of attribute.
> So I was trying to do it visually by using this query..
> SELECT tc.TestCase, tc.mostRecent, tc.Result
> FROM Reports rp
> RIGHT JOIN
> (
> SELECT TestCases.TestCase ,Reports.Result, MAX ( Reports.ReportDate ) AS
> mostRecent
> FROM Reports
> LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
> LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
> WHERE ( (Tests.Attributes = 'Attribute 1') OR (Tests.Attributes =
> 'Attribute 2') OR (Tests.Attributes = 'Attribute 3') )
> GROUP BY TestCases.TestCase, Result) tc ON (tc.mostRecent =
> rp.ReportDate ) ORDER BY TestCAse
>
> Which will give you this data...
> TestCase mostRecent Result
> Run for 30 minutes 2006-06-25 19:58:31.800 Fail
> Run for 30 minutes 2006-06-25 19:58:30.800 Pass
> Run for 45 minutes 2006-06-25 19:58:34.800 Fail
> Run for 45 minutes 2006-06-25 19:58:32.800 Pass
> Run for 60 minutes 2006-06-25 19:58:36.863 Fail
> Run for 60 minutes 2006-06-25 19:58:37.863 Pass
> Which as you can tell is giving me both the most recent pass & fail
> result, where I want the most recent result regardless of Pass/Fail.
> Which in turn, makes my count query wrong as well...
> SELECT COUNT(DISTINCT tc.TestCase) As Count,
> COUNT(DISTINCT CASE rp.Result WHEN 'Pass' THEN rp.ReportID ELSE NULL END)
> as Pass,
> COUNT(DISTINCT CASE rp.Result WHEN 'Fail' THEN rp.ReportID ELSE NULL END)
> as Fail
> FROM Reports rp
> RIGHT JOIN
> (
> SELECT TestCases.TestCase ,Reports.Result, MAX ( Reports.ReportDate ) AS
> mostRecent
> FROM Reports
> LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
> LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
> WHERE ( (Tests.Attributes = 'Attribute 1') OR (Tests.Attributes =
> 'Attribute 2') OR (Tests.Attributes = 'Attribute 3') )
> GROUP BY TestCases.TestCase, Result) tc ON (tc.mostRecent =
> rp.ReportDate )
> Which will give me
>
> Count Pass Fail
> 3 3 3
>
> Instead of the result I am looking for of..
> Count Pass Fail
> 3 1 2
>
>|||Lucas
Thanks fro posting DDL
See if this helps you
SELECT TestCase,COUNT(CASE WHEN Result='Fail' THEN ReportDate END) AS
'Fail',
COUNT(CASE WHEN Result='Pass' THEN ReportDate END) AS 'Pass'
FROM
(
SELECT Apps.AppName, TestCases.TestCase , Tests.Attributes,
Reports.Result, Reports.ReportDate
FROM
Reports
LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.AppID
) AS Der GROUP BY TestCase
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:uJYRRBNmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> Here is a link to the DDL since it would take up a lot of room here.
> http://damageinc.org/DDL.html
> After you use that to generate the table and some sample data, here is the
> problem I need help with.
> You will see the following data, if you use this query...
>
> SELECT DISTINCT Apps.AppName, TestCases.TestCase , Tests.Attributes,
> Reports.Result, Reports.ReportDate
> FROM
> Reports
> LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
> LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
> LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.AppID
> AppName TestCase Attribute Result
> ReportDate
> Test App 1 Run for 30 minutes Attribute 1 Fail
> 2006-06-25 19:58:31.800
> Test App 1 Run for 30 minutes Attribute 1 Pass
> 2006-06-25 19:58:29.800
> Test App 1 Run for 30 minutes Attribute 1 Pass
> 2006-06-25 19:58:30.800
> Test App 1 Run for 45 minutes Attribute 2 Fail
> 2006-06-25 19:58:33.800
> Test App 1 Run for 45 minutes Attribute 2 Fail
> 2006-06-25 19:58:34.800
> Test App 1 Run for 45 minutes Attribute 2 Pass
> 2006-06-25 19:58:32.800
> Test App 1 Run for 60 minutes Attribute 3 Fail
> 2006-06-25 19:58:36.863
> Test App 1 Run for 60 minutes Attribute 3 Pass
> 2006-06-25 19:58:35.863
> Test App 1 Run for 60 minutes Attribute 3 Pass
> 2006-06-25 19:58:37.863
>
> What I need to get is the count most recent result per TestCase regardless
> of attribute.
> So I was trying to do it visually by using this query..
> SELECT tc.TestCase, tc.mostRecent, tc.Result
> FROM Reports rp
> RIGHT JOIN
> (
> SELECT TestCases.TestCase ,Reports.Result, MAX ( Reports.ReportDate ) AS
> mostRecent
> FROM Reports
> LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
> LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
> WHERE ( (Tests.Attributes = 'Attribute 1') OR (Tests.Attributes =
> 'Attribute 2') OR (Tests.Attributes = 'Attribute 3') )
> GROUP BY TestCases.TestCase, Result) tc ON (tc.mostRecent =
> rp.ReportDate ) ORDER BY TestCAse
>
> Which will give you this data...
> TestCase mostRecent Result
> Run for 30 minutes 2006-06-25 19:58:31.800 Fail
> Run for 30 minutes 2006-06-25 19:58:30.800 Pass
> Run for 45 minutes 2006-06-25 19:58:34.800 Fail
> Run for 45 minutes 2006-06-25 19:58:32.800 Pass
> Run for 60 minutes 2006-06-25 19:58:36.863 Fail
> Run for 60 minutes 2006-06-25 19:58:37.863 Pass
> Which as you can tell is giving me both the most recent pass & fail
> result, where I want the most recent result regardless of Pass/Fail.
> Which in turn, makes my count query wrong as well...
> SELECT COUNT(DISTINCT tc.TestCase) As Count,
> COUNT(DISTINCT CASE rp.Result WHEN 'Pass' THEN rp.ReportID ELSE NULL END)
> as Pass,
> COUNT(DISTINCT CASE rp.Result WHEN 'Fail' THEN rp.ReportID ELSE NULL END)
> as Fail
> FROM Reports rp
> RIGHT JOIN
> (
> SELECT TestCases.TestCase ,Reports.Result, MAX ( Reports.ReportDate ) AS
> mostRecent
> FROM Reports
> LEFT OUTER JOIN TestCases ON TestCases.TestCaseID = Reports.TestID
> LEFT OUTER JOIN Tests ON TestCases.TestCaseID = Tests.TestID
> WHERE ( (Tests.Attributes = 'Attribute 1') OR (Tests.Attributes =
> 'Attribute 2') OR (Tests.Attributes = 'Attribute 3') )
> GROUP BY TestCases.TestCase, Result) tc ON (tc.mostRecent =
> rp.ReportDate )
> Which will give me
>
> Count Pass Fail
> 3 3 3
>
> Instead of the result I am looking for of..
> Count Pass Fail
> 3 1 2
>
>|||Hi guys, thanks for the help so far.
I am really close now. One remaining problem, say there is no report s for
say testcase 3 (delete any reports w/the TestID of 3 in it).
Running the query will give me
Count Pass Fail
2 0 2
What i need is to show the total test cases even if there is no result(s)
for some, and the count of the ones where there are results for like so..
Count Pass Fail
3 0 2|||Select Count(*)
+ (Select Count(*) From
TestCases tc
Where Not Exists (Select 1 From Reports r Where r.TestID =
tc.TestCaseID)) As 'Count',
Sum(Case When Result = 'Pass' Then 1 Else 0 End) As 'Pass',
Sum(Case When Result = 'Fail' Then 1 Else 0 End) As 'Fail'
From Reports r
Where r.ReportDate In
(Select Max(r.ReportDate)
From Reports r
Inner Join TestCases tc On r.TestID = tc.TestCaseID
Inner Join Tests t On tc.TestCaseID = t.TestID
Inner Join Apps a On tc.AppID = a.AppID
Where t.Attributes In ('Attribute 1', 'Attribute 2', 'Attribute 3')
Group By a.AppName, tc.TestCase)
Tom
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:uEX$yZVmGHA.492@.TK2MSFTNGP05.phx.gbl...
> Hi guys, thanks for the help so far.
> I am really close now. One remaining problem, say there is no report s
> for say testcase 3 (delete any reports w/the TestID of 3 in it).
> Running the query will give me
> Count Pass Fail
> 2 0 2
>
> What i need is to show the total test cases even if there is no result(s)
> for some, and the count of the ones where there are results for like so..
> Count Pass Fail
> 3 0 2
>
>|||Thanks Tom "the master" Cooper!
I appreciate everyone's help as well, every time I post a question I get
another nugget of i nformation to store away.
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:wvKdnRf8W_cuoT3ZnZ2dnUVZ_oKdnZ2d@.co
mcast.com...
> Select Count(*)
> + (Select Count(*) From
> TestCases tc
> Where Not Exists (Select 1 From Reports r Where r.TestID =
> tc.TestCaseID)) As 'Count',
> Sum(Case When Result = 'Pass' Then 1 Else 0 End) As 'Pass',
> Sum(Case When Result = 'Fail' Then 1 Else 0 End) As 'Fail'
> From Reports r
> Where r.ReportDate In
> (Select Max(r.ReportDate)
> From Reports r
> Inner Join TestCases tc On r.TestID = tc.TestCaseID
> Inner Join Tests t On tc.TestCaseID = t.TestID
> Inner Join Apps a On tc.AppID = a.AppID
> Where t.Attributes In ('Attribute 1', 'Attribute 2', 'Attribute 3')
> Group By a.AppName, tc.TestCase)
> Tom
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:uEX$yZVmGHA.492@.TK2MSFTNGP05.phx.gbl...
>|||Tom, maybe I could pick your brain one more time :)
For whatever reason on my actual tables I still can't get what I need :(
I have updated the DDL with the actual tables and a sample of table data of
what I am actually using.
http://damageinc.org/DDL.html (The DDL is too large to post, the message
gets kicked back to me)
AFter you enter that DDL
If you run the query..
SELECT Apps.AppName, Tests.TestCase, TestCases.Type
FROM
TestCases
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
LEFT OUTER JOIN Tests ON TestCases.TestID = Tests.ID
WHERE
(
(
(Proj1 = '87' OR Proj2 = '87' OR Proj3 = '87' OR Proj4 = '87' OR Proj5 =
'87')
OR
(Proj1 = '88' OR Proj2 = '88' OR Proj3 = '88' OR Proj4 = '88' OR Proj5 =
'88')
)
AND (TestCases.Card = 'G71_D' OR TestCases.Card = 'G70_D' OR TestCases.Card
= 'G72_D' OR TestCases.Card = 'G73_D')
AND TestCases.OS = 'Windows Vista'
)
GROUP BY AppName, TestCase,TestCases.Type
You will see
AppName TestCase
Type
3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso D3D Benchmarks
3D Mark 2003 Benchmark: 1600x1200x32 4xAA 8x Aniso Games
3D Mark 2003 Benchmark: Default 2x AA 4x Aniso D3D
Benchmarks
3D Mark 2003 Benchmark: Default 2x AA 4x Aniso G ames
.
.
.
.
There are 97 tests there.
What I need is that 97 for the count, then the count of the most recent
pass/fail result(s) (if there is a result) for each of those tests.
Using
Select Count(*) As 'Count',
Sum(Case When Result = 'Pass' Then 1 Else 0 End) As 'Pass',
Sum(Case When Result = 'Fail' Then 1 Else 0 End) As 'Fail'
From Reports r
Where r.ReportDate In
(Select Max(r.ReportDate)
From Reports r
Inner Join TestCases tc On r.TestCaseID = tc.ID
Inner Join Tests t On tc.TestID = t.ID
Inner Join Apps a On tc.AppID = a.ID
WHERE
(
(
(tc.Proj1 = '87' OR tc.Proj2 = '87' OR tc.Proj3 = '87' OR tc.Proj4 = '87'
OR tc.Proj5 = '87')
OR
(tc.Proj1 = '88' OR tc.Proj2 = '88' OR tc.Proj3 = '88' OR tc.Proj4 = '88'
OR tc.Proj5 = '88')
)
AND (tc.Card = 'G71_D' OR tc.Card = 'G70_D' OR tc.Card = 'G72_D' OR tc.Card
= 'G73_D')
AND tc.OS = 'Windows Vista'
)
Group By a.AppName, t.TestCase)
Gives me
Count Pass Fail
25 2 23
Which is obviously wrong, since the Count should be 97.
I have to be overthinking something here...
========================================
====================================
========

Monday, February 20, 2012

NB: query troubles

I'm making a movie site.

I have 3 tables: actors, movies, mov_act
mov_act is the link tables bacause of the many-to-many relationship.

i need to find the actors that aren't in a specified movie(id=2 for instance).

simple for you guys, hard for a newbie.

any help?

thanks.

btw: not subqueries usage please, because can't upgrade my isp's SQL version ;-)ah yes, good old mysql, no subqueries before version 4.1

select actors.name
from actors
left outer
join mov_act
on actors.id = mov_act.actorid
left outer
join movies
on mov_act.movieid = movies.id
and movies.name = 'ben hur'
group
by actors.name
having count(movies.name) = 0|||thanks you this worked

select mvs_actors.fname
from mvs_actors
left outer
join mvs_mov_act
on mvs_actors.id = mvs_mov_act.act_id
left outer
join mvs_movies
on mvs_mov_act.mov_id = mvs_movies.id
and mvs_movies.id = 1
group
by mvs_actors.fname
having count(mvs_movies.id) = 0

i now have all actors that aren't in movie with id=1

stupid question, but how do i get the once that are in that movie with id=1??

thank you very much|||how do i get the ones that are in that movie with id=1??

easy -- change the left outer joins to inner, and drop the GROUP BY and HAVING clauses

:cool:|||by the way, if you already (somehow) know what the id of the desired movie is, there's no need to join from the mvs_mov_act to the mvs_movies table

actors in the movie id=1 --

select mvs_actors.fname
from mvs_actors
inner
join mvs_mov_act
on mvs_actors.id = mvs_mov_act.act_id
and mvs_mov_act.mov_id = 1|||Thanks very much.

These are the two Queries that work.
The first one seems a bit long(in comparison to the second), but it works ;)

$query = "select mvs_actors.fname,mvs_actors.lname,mvs_actors.id ";
$query .= "from mvs_actors ";
$query .= "left outer ";
$query .= "join mvs_mov_act ";
$query .= "on mvs_actors.id = mvs_mov_act.act_id ";
$query .= "left outer ";
$query .= "join mvs_movies ";
$query .= "on mvs_mov_act.mov_id = mvs_movies.id ";
$query .= "and mvs_movies.id = ".$recordID." ";
$query .= "group by mvs_actors.fname ";
$query .= "having count(mvs_movies.id) = 0 ";

$query = "select mvs_actors.fname,mvs_actors.lname,mvs_actors.id ";
$query .= "from mvs_actors ";
$query .= "inner ";
$query .= "join mvs_mov_act ";
$query .= "on mvs_actors.id = mvs_mov_act.act_id ";
$query .= "and mvs_mov_act.mov_id = ".$recordID." ";

Navigation: Jump to URL

Hello,
I have been trying for a while to get one field in my report to link to
a page on my local server. I have looked in the older post as well as on
the internet and have found so examples that just don't work for me. I
am running SRS SP2 and using an MSCRM database as my source. The code I
am using is as follows: = "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={' &
Fields!leadid.Value.ToString & '}'))". I am incredibly new to javascript
and sort of know what this code does but I don't know if I am using the
function correctly and if this function will even work. When I use the
hyperlinked field, my screen flickers and nothing happens. So any help
you can offer will be greatly appreciated.
Regards,
Jeff JonesYou are assembling a string here. Change it to this:
= "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={'" &
Fields!leadid.Value.ToString & "'}'))".
You need the additional double quotes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeffrey Jones" <jeffjones176@.kc.rr.com> wrote in message
news:eTc1s1PpFHA.1048@.tk2msftngp13.phx.gbl...
> Hello,
> I have been trying for a while to get one field in my report to link to a
> page on my local server. I have looked in the older post as well as on the
> internet and have found so examples that just don't work for me. I am
> running SRS SP2 and using an MSCRM database as my source. The code I am
> using is as follows: => "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={' &
> Fields!leadid.Value.ToString & '}'))". I am incredibly new to javascript
> and sort of know what this code does but I don't know if I am using the
> function correctly and if this function will even work. When I use the
> hyperlinked field, my screen flickers and nothing happens. So any help you
> can offer will be greatly appreciated.
> Regards,
> Jeff Jones|||Bruce L-C [MVP] wrote:
> You are assembling a string here. Change it to this:
> = "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={'" &
> Fields!leadid.Value.ToString & "'}'))".
> You need the additional double quotes.
>
Thank you for pointing that out. Though, after I change my syntax, I
still can't get the code to work. Could this be an explorer setting? I
have tried this same code but with google.com replaced with my crm url.
My screen flashes and windows makes a 'ding' sound and then nothing.
Looked in the event logs. Nothing. Looked at the task manager as I was
firing the link. Nothing. I don't really know what is happening with
this. Any other suggestions would be helpful.
Thanks,
Jeff|||Here is a working one for me. Take the following line and cut and paste it
into the jump to url.
= "javascript:void(window.open('http://www.google.com','_blank'))"
Note that you have to have SP1 or SP2 installed for this to work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeffrey Jones" <jeffjones176@.kc.rr.com> wrote in message
news:eMg$$o0pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Bruce L-C [MVP] wrote:
>> You are assembling a string here. Change it to this:
>> = "javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={'" &
>> Fields!leadid.Value.ToString & "'}'))".
>> You need the additional double quotes.
>>
> Thank you for pointing that out. Though, after I change my syntax, I still
> can't get the code to work. Could this be an explorer setting? I have
> tried this same code but with google.com replaced with my crm url. My
> screen flashes and windows makes a 'ding' sound and then nothing. Looked
> in the event logs. Nothing. Looked at the task manager as I was firing the
> link. Nothing. I don't really know what is happening with this. Any other
> suggestions would be helpful.
> Thanks,
> Jeff|||Bruce L-C [MVP] wrote:
> Here is a working one for me. Take the following line and cut and paste it
> into the jump to url.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> Note that you have to have SP1 or SP2 installed for this to work.
>
I have learned that spacing matters with this particular section of the
program. The syntax of the day is: ="javascript:void(window.open('http://crm/SFA/leads/edit.aspx?id={"&
Fields!leadid.Value &"}'))". There is a space between the amperstands
and the Fields name. Any thing less than this exact syntax will not
work. Also the link will not work in the VS dev. env. The report needs
to be deployed before it will work worth a darn. Since leadid is a GUID
and SRS does not play nice with GUIDs, you need to use <CAST(Lead.LeadId
as nvarchar(80)) as leadid> when creating your dataset. Follow all of
this and you will have a nice day.
Regards,
Jeff

Navigation from the header


Hello

I have a link on the header to jump to another report. It was working fine on test server but when deployed to production, is giving the followin error. Any suggestions
Thanks

Inder

  • The path of the item "(null)" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath)

  • Hi Innder,

    Check out the following posts they should help you.

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

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

    Hope this helps.

    |||

    Make sure to install SP2 of SSRS 2005 on the server (http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx) and drillthrough links in page header/footer should work.

    -- Robert

  •