Reporting Service - Administrator accounts - Database Discussion Boards

SQL Server 2008 -Configuration -Remote Access ?

Paramu19737-Aug-09 22:56

Paramu1973

7-Aug-09 22:56

I learned here, we have to issue " netstat -an " to identify the port-1433 is configured or not..
I also checked and got the following screen,

Proto Local Address Foreign Address State
....
TCP 0.0.0.0:1433 0.0.0.0 Blush | :O

Listening
TCP MY SQL SERVER INSTALLED
MACHINE IP : 139 0.0.0.0 Blush | :O

Listening
...
and then for UDP.

And Iam not getting like the below,
TCP MY SQL SERVER INSTALLED
MACHINE IP : 1433 0.0.0.0 Blush | :O

Listening

So what I have to do? From where I have to troubleshoot? Also getting confusion, my domain only have static ip, this Sql server installed machine ip is not a static ip. Confused | :confused:

Any ideas please? Mad | :mad:

Thanks

Sql Joines

sumit70347-Aug-09 19:58

sumit7034

7-Aug-09 19:58

hi I want to learn sql joines can anyone provide me link for the best article that would help me.

Thanks

Re: Sql Joines

Abhijit Jana7-Aug-09 21:31

Abhijit Jana

7-Aug-09 21:31

Here is one of the Best Join article link from Code Project, This is totally visual representation of Join in SQL.
Visual Representation of SQL Joins[^]

This will clear your doubts on joins Big Grin | :-D

Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
View My Latest Article

Hiring home based workers (3171)

texzeb7-Aug-09 4:18

texzeb

7-Aug-09 4:18

A Real Work at Home Opportunity as an Independent Advertising Executive. We offer home workers the opportunity to earn extra money from the comfort of their own home. Visit Us at http://www.clicknearn.net/idevaffiliate/idevaffiliate.php?id=3171_9

Re: Hiring home based workers (3171)

Ashfield7-Aug-09 9:36

Ashfield

7-Aug-09 9:36

If you want to advertise pay for it, don't spam forums - and the jobs are a con anyway

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

ora-12528 tns listener all appropriate instances are blocking new connections

vikash_singh7-Aug-09 2:35

vikash_singh

7-Aug-09 2:35

hi friends please help,
while logging into database iam getting following error:-

ora-12528 tns listener all appropriate instances are blocking new connections

i tried every thing that i found in forums
problem still continues.....

Re: ora-12528 tns listener all appropriate instances are blocking new connections

Vimalsoft(Pty) Ltd7-Aug-09 3:32

Vimalsoft(Pty) Ltd

7-Aug-09 3:32

You want to tell me that you habe tried all the Google Solution

http://www.google.co.za/search?q=ora-12528+tns+listener+all+appropriate+instances+are+blocking+new+connections&hl=en&start=10&sa=N[^]

?

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Reporting Service - Administrator accounts

devvvy7-Aug-09 1:37

devvvy

7-Aug-09 1:37

hello

About to install a Reporting Service - and I want to tighten security by not assigning to Reporting Service an Administrator (OS level) or sa (SQL level) account.

Any recommendation? An URL would be nice.

Thanks

dev

Debugging Postgresql plpgsql [modified]

Jacobus016-Aug-09 23:38

Jacobus01

6-Aug-09 23:38

What a nightmare...
I'm currently writing pl sql for postgresql. I'm not a qualified DBA and pl sql is quite new to me. I've always rather used plain sql/linq and ADO.NET but for this specific problem it is better(I think) to write a stored procedure. The thing is that I can't seem to get debugging to work in postgresql. I'm running postgres v 8.4.(something). Is there a opensource ide that I can use to compile and debug postgresql stored procedures (Currently running dreamcoder and pgadmin III). I need help and google isn't giving me helpful answers. I have a logical error in my procedure and I can't step throught the code. I'm also using unidirect as my dataprovider in VS 2008 but guess what: No design time support for unidirect. Big up to corelab and my idiot boss for deciding on this so everything has to happen @ runtime(So much for RAD). VS 2008 would have allowed me to generate the SP if this worked...Please help me fellow programming mates. What is there to do

Regards
.Net developer with DB issues

modified on Friday, August 7, 2009 5:44 AM

Help with optimization for searching a data table for a logical 'AND'

Spacix One6-Aug-09 18:03

Spacix One

6-Aug-09 18:03

I'm trying to search some data in a table I don't have control over and I need to figure out how to do an logical and between two specified search items.

I need to find all rows in a table that has the same value in collumn A and column B is equal to X or Y.

The is similar to the following, but it contains more than 600,000 records.

Tablename: data_table
id	record_id	parm_type	value
0	0	1001	10
1	0	1002	20
2	0	1003	30
3	0	1004	40
4	1	1001	15
5	1	1002	25
6	1	1003	35
7	1	1004	45
8	2	1005	20
9	2	1002	30
10	2	1003	40
11	2	1008	50
12	3	1007	20
13	3	1002	30
14	3	1003	40
15	3	1007	50
16	4	1001	20
17	4	1002	30
18	4	1007	40
19	4	1008	50

I want to get every value with the same record_id, that has a parm_type type of 1002 or 1003 and the value is greater than 20 for type 1002 and less than 40 for 1003.

Here is what I tried but it was taking very long and gets execution timeouts

SELECT id,record_id,parm_type,value FROM data_table
WHERE record_id IN
(
  SELECT record_id FROM data_table
  WHERE parm_type=1002 AND value > 20
)
AND record_id IN
(
  SELECT record_id FROM data_table
  WHERE parm_type=1003 AND value > 40
);

I know there have to be others ways to accomplish this, but I'm no DBA...

-Spacix
All your skynet questions[^] belong to solved

Re: Help with optimization for searching a data table for a logical 'AND'

N a v a n e e t h6-Aug-09 19:07

N a v a n e e t h

6-Aug-09 19:07

Couple of ideas,

1 - Create a temporary table. Execute each query separately and insert to temporary table. Finally, select the temporary table. Here is it

CREATE TABLE #temp
(
   /* your code */
);

insert into #temp
SELECT record_id FROM data_table  WHERE parm_type = 1002 AND value > 20

insert into #temp
SELECT record_id FROM data_table WHERE parm_type=1003 AND value > 40

SELECT * FROM #temp

2 - Use UNION[^]

SELECT record_id FROM data_table  WHERE parm_type = 1002 AND value &gt; 20
UNION
SELECT record_id FROM data_table WHERE parm_type=1003 AND value &gt; 40

Navaneeth

How to use google | Ask smart questions

Re: Help with optimization for searching a data table for a logical 'AND'

Spacix One7-Aug-09 2:32

Spacix One

7-Aug-09 2:32

Navaneeth thank you for your reply.

I think a UNION statment would merge record_id that would match either of the parm_type requirements.
Please correct me if I am wrong!

-Spacix
All your skynet questions[^] belong to solved

Re: Help with optimization for searching a data table for a logical 'AND'

i.j.russell6-Aug-09 21:33

i.j.russell

6-Aug-09 21:33

SELECT id,record_id,parm_type,value
FROM data_table
WHERE (parm_type=1002 AND value > 20)
OR (parm_type=1003 AND value > 40);

Re: Help with optimization for searching a data table for a logical 'AND'

Spacix One7-Aug-09 2:36

Spacix One

7-Aug-09 2:36

Thank you for the reply i.j.russell

This is acually what I have currently, but this is a logical OR where I'm needing to do a logical AND. I want to a one or more record_id values that contains a "parm_type=1002 AND value > 20" and a "parm_type=1004 AND value > 40."

This makes it a bit harder to search Frown | :(

-Spacix
All your skynet questions[^] belong to solved

Re: Help with optimization for searching a data table for a logical 'AND'

Spacix One7-Aug-09 2:24

Spacix One

7-Aug-09 2:24

Maybe I need more clairification (and a better example)

Ok so I want to find everything with the same record ID, that has atleast one match for every parm_type and value spesified.

What has be suggested so far is a "logical" OR and meaning any of the parm_type and value criteria match.

A better example would be one that actually returns data, I apologize for my previous bad example.

SELECT id,record_id,parm_type,value FROM data_table
WHERE record_id IN
(
  SELECT record_id FROM data_table
  WHERE parm_type=1002 AND value > 20
)
AND record_id IN
(
  SELECT record_id FROM data_table
  WHERE parm_type=1003 AND value >= 40
);

If I were to run this with the data_table in my previous post I'd expect to be returned would be the following result table:

Result from: data_table
id	record_id	parm_type	value
8	2	1005	20
9	2	1002	30
10	2	1003	40
11	2	1008	50
12	3	1007	20
13	3	1002	30
14	3	1003	40
15	3	1007	50

I hope this could clear things up.

-Spacix
All your skynet questions[^] belong to solved

Re: Help with optimization for searching a data table for a logical 'AND'

David Skelly7-Aug-09 2:37

David Skelly

7-Aug-09 2:37

I'm a bit unclear what you're looking for here. What do you mean by "every value with the same record_id"?

The solutions posted by the others will give you a list of record_ids that have entries in the table for either (type = 1002 and value > 20) OR (type = 1003 and value > 40).

The solution that you posted would give you a list of record_ids that have entries in the table for both (type = 1002 and value > 20) AND (type = 1003 and value > 40).

These are not the same thing, so which one are you looking for?

In terms of your original question, this is probably no help since you do not have control over the table, but one obvious question is: is there an index over parm_type? Without an index over this column, the DBMS is going to end up doing a full table scan which will take a long time on a big table (i.e. it will simply look at every row in the table in turn to see if it matches the query).

Re: Help with optimization for searching a data table for a logical 'AND'

Spacix One7-Aug-09 2:49

Spacix One

7-Aug-09 2:49

David Skelly wrote:
The solution that you posted would give you a list of record_ids that have entries in the table for both (type = 1002 and value > 20) AND (type = 1003 and value > 40).

Is exactly what I'm trying todo, but need a faster method.

I don't really have control over the table, but I can force an index on that column since I can run SQL against the whole table. Maybe it'll speed up if I toss an alter table to add an index to the result_id...

Yes, yes that just might work! David Skelly you're an evil genius.

-Spacix
All your skynet questions[^] belong to solved

Re: Help with optimization for searching a data table for a logical 'AND'

Spacix One7-Aug-09 3:07

Spacix One

7-Aug-09 3:07

I added an index to the record_id indicator with an alter table and now my original query returns in 0.58 seconds (db time) instead of ~400 seconds (db time)

Guess this just goes shows me even more how crappy the self proclaimed coder/dba that designed this was, not that I didn't already know he was full of himself by looking at his code Wink | ;)

-Spacix
All your skynet questions[^] belong to solved

Query a DB for a field value on SQL 2005 STD SP2

Hulicat6-Aug-09 7:43

Hulicat

6-Aug-09 7:43

This does not seem to work; I dont know which table the value is in.

USE DB
Go
SELECT *
FROM sys.Tables where fieldid = '%'
GO

I presume I cannot use a conditional clause in that statement?

It will parse but errors out

Regards,
Hulicat

Re: Query a DB for a field value on SQL 2005 STD SP2

David Mujica6-Aug-09 8:06

David Mujica

6-Aug-09 8:06

This is what I would use ...

select SO.NAME AS TABLENAME, SC.NAME AS COLNAME 
from sysobjects so, syscolumns sc 
where so.id = sc.id and sc.name = 'DESCRIPTION'

This query will give you a listing of every table that has a field that is named, 'DESCRIPTION'.

Good luck,
david

Re: Query a DB for a field value on SQL 2005 STD SP2

Hulicat6-Aug-09 8:22

Hulicat

6-Aug-09 8:22

Thanks!

Regards,
Hulicat

problem with creating view?

shankbond6-Aug-09 2:45

shankbond

6-Aug-09 2:45

Hi, I need help with gridview?

actually the gridview is supposed to show a view(aka join) consisting of two tables .

In my case I have (userid) , (money deposited) as fields in table 1; userid can also be left as null.

I have another table I have first_name, last_name,phone numbers( fields) in table2

I want to display first_ name ,money deposited.
in the gridview.

It has to show all the enteries made in table 1 either it is null or has some userid; if it becomes null , then a space should appear instead of displaying null.

can anyone please guide me how to do this?

thanks

Re: problem with creating view?

Ashfield6-Aug-09 8:57

Ashfield

6-Aug-09 8:57

try something like this - I have to assume that userid appears in table3 also, therwise this is no way to join them

select isnull(first_name,' '), [money deposited]
from table1 t1
left outer join table2 t2 on t2.userid = t1.userid

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

how to check " is not null condition" in a field of single select query

anushh6-Aug-09 0:45

anushh

6-Aug-09 0:45

how to check "is not null condition" in a field of single select query

Re: how to check " is not null condition" in a field of single select query

WoutL6-Aug-09 1:21

WoutL

6-Aug-09 1:21

select *
from table
where column is not null

Wout Louwers

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.