Database Discussion Boards

Re: Read Lock a Table in SQL Server?

P_pk8-Jun-07 23:47

P_pk

8-Jun-07 23:47

you can create trigger for dml staements such as select,update ,delete..So create a trigger while selecting the table.

PPK

mysql

WhiteGirl235-Jun-07 22:08

WhiteGirl23

5-Jun-07 22:08

Hi,
I work on a project which is connected to mysql.
But when I connect to mysql I connect with "localhost",
bu I can't connect with my IP.
Please help.
Thanks.

Re: mysql

ScottM16-Jun-07 4:54

ScottM1

6-Jun-07 4:54

Can you connect using 127.0.0.1?

There are 10 types of people in the world, those who understand binary and those who dont.

Re: mysql

WhiteGirl236-Jun-07 19:38

WhiteGirl23

6-Jun-07 19:38

Hi,
Thanks for your reply but I can't connect with 127.0.0.1.

Re: mysql

ScottM16-Jun-07 19:55

ScottM1

6-Jun-07 19:55

Hi, I don't really know MySQL but try:
Turning off any firewalls that might be running.
Making sure the service is started properly on your computer.
If thats not the problem perhaps try reinstalling MySQL.
Wink | ;)

There are 10 types of people in the world, those who understand binary and those who dont.

Set the values of a field

villaa5-Jun-07 21:53

villaa

5-Jun-07 21:53

how to programmatically add the values of a apecific field in a .mdb file.(only that field is blank, the other fields are filled)
thanks

Re: Set the values of a field

Colin Angus Mackay5-Jun-07 22:21

Colin Angus Mackay

5-Jun-07 22:21

Send the appropriate UPDATE SQL Command to the database.

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Speed consideration

amitcoder835-Jun-07 21:20

amitcoder83

5-Jun-07 21:20

Between Stored procedure and user defined function which is faster and why?

Re: Speed consideration

Colin Angus Mackay5-Jun-07 22:20

Colin Angus Mackay

5-Jun-07 22:20

They solve different problems. A speed comparison is invalid.

Re: Speed consideration

Puneet Sri5-Jun-07 23:23

Puneet Sri

5-Jun-07 23:23

Stored procedure is faster then user defined function because stored procedure is precompiled and user define function is compiled at the of call
So it should take little more time to respond.

I hope this will help you

Puneet Srivastava

Re: Speed consideration

Mike Dimmick6-Jun-07 0:53

Mike Dimmick

6-Jun-07 0:53

Not true in SQL Server. SQL Server compiles the query plan of any query, including a stored procedure, the first time it is used. It then caches that query plan, giving a higher weighting to the plan for a stored procedure over an ad-hoc query with parameters, over an auto-parameterized ad-hoc query, over a query that couldn't be auto-parameterized.

If you want to force a stored procedure to be recompiled, use WITH RECOMPILE on the EXECUTE statement. To force a recompile every time, use WITH RECOMPILE on the CREATE PROCEDURE statement.

I believe the text of a user-defined function is effectively incorporated into the text of the query that called it, it does not get a separate query plan of its own. This may depend on whether it's a scalar function, an in-line table-valued function or a multi-statement table-valued function.

Stability. What an interesting concept. -- Chris Maunder

Re: Speed consideration

Arun.Immanuel6-Jun-07 3:55

Arun.Immanuel

6-Jun-07 3:55

http://qa.techinterviews.com/q/20060804232021AA6mjm4[^]

Regards,
Arun Kumar.A

find duplicated rows

rkherath5-Jun-07 18:56

rkherath

5-Jun-07 18:56

hi,

i have an sql table which has no primary key. i want to find out wether the same row is repeated. how can i see it.

regards
Ruwandi

rkherath

Re: find duplicated rows

ganesamoorthidhayalan5-Jun-07 21:18

ganesamoorthidhayalan

5-Jun-07 21:18

select field_name count(*) from tablename where group by field_name order by 1;

This query to find out the number of duplicate value.

Re: find duplicated rows

ganesamoorthidhayalan5-Jun-07 21:29

ganesamoorthidhayalan

5-Jun-07 21:29

its also the best query

SELECT *
FROM BB1
WHERE (DATE_STAMP,DB_NAME,TABLE_NAME) IN (SELECT DATE_STAMP,
DB_NAME,
TABLE_NAME
FROM BB1
GROUP BY DATE_STAMP,DB_NAME,TABLE_NAME
HAVING COUNT(* ) > 1);

Re: find duplicated rows

rkherath6-Jun-07 0:18

rkherath

6-Jun-07 0:18

hi ganesamoorthidhayalan,

it works. thanks a lot.

Regards
Ruwandi

rkherath

HELP...SQL problem

xuyuan5-Jun-07 17:48

xuyuan

5-Jun-07 17:48

I'm using SQL to output some data from the 3 table below (Sales, Product,& Staff).
How if i want to display a table as this...Have any ideas??

ProductId TotalSale Sale_of_team1 sale_of_team2 sale_of_team3
P012 3 0 3 0
P016 27 7 0 20
P017 4 4 0 0
P019 2 0 2 0
P022 2 0 2 0

>>Sales
ProductId Date Week SalesUnit StaffId

P012 20070514 20 3 S002
P016 20070531 22 7 S001
P016 20070531 22 20 S003
P017 20070531 22 2 S001
P017 20070523 21 2 S001
P019 20070530 22 2 S002
P022 20070502 18 2 S002

>>Product
ProductId ProductName Category Cost

P012 ADSL 512M/256K Streamyx 77
P016 LT 38 Lets Talk 38
P017 PLAN A Lets Talk 68
P019 PLAN C Lets Talk 198
P022 Italk Mobile-50 Italk Prepaid Cards 50

>>Staff
StaffId StaffName Team Level

S001 Jass 1 1
S002 kwee 2 2
S003 Rain 3 2

Thank for helping...arigatou gozaimasu!!!

Re: HELP...SQL problem

andyharman5-Jun-07 23:14

andyharman

5-Jun-07 23:14

The following should do the trick:

SELECT Sales.ProductId,
   SUM(Sales.SalesUnit) AS TotalSale,
   SUM(CASE Staff.Team = 1 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team1,
   SUM(CASE Staff.Team = 2 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team2,
   SUM(CASE Staff.Team = 3 THEN Sales.SalesUnit ELSE 0 END AS Sale_of_team3
FROM Sales
INNER JOIN Staff
ON Staff.StaffId = Sales.StaffId
GROUP BY Sales.ProductId

The "case" clauses allow you to conditionally summate a column.

Or

SELECT Product.ProductId,
   (SELECT SUM(SalesUnit) FROM Sales) AS TotalSale,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 1
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team1,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 2
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team2,
   (SELECT SUM(SalesUnit) FROM Sales
       INNER JOIN Staff
       ON Staff.StaffId = Sales.StaffId
       AND Staff.Team = 3
       WHERE Sales.ProductId = Product.ProductId) AS Sale_of_team3
FROM Products

This uses correlated sub-queries to do the work.

Regards
Andy

convert varchar to date

rkherath5-Jun-07 17:32

rkherath

5-Jun-07 17:32

hi All,

i have a dates field which contains dates as varchar type

Dates

(yyyymmdd)
20071231
20070312
20071123

i wanna convert this in to

yyyy/mm/dd

2007/12/31
2007/03/12
2007/11/23

how can i do this please tell me

convert(varchar,dates,101)

didnt include the '/'

regards
Ruwandi

rkherath

Re: convert varchar to date

Krish - KP5-Jun-07 17:40

Krish - KP

5-Jun-07 17:40

to get the dates with "/" seperated you can use
substring(dates, 1, 4) + '/' + substring(dates, 5, 2) + '/' + substring(dates, 7, 2)
But the dates remains varchar

convert function actually converts to datetime data type.

Regards
KP

Re: convert varchar to date

rkherath5-Jun-07 18:55

rkherath

5-Jun-07 18:55

hi,

thank. i got it .

thx a lot.

regards
Ruwandi

rkherath

SSRS;Minus Sign

Amit Kumar G5-Jun-07 16:34

Amit Kumar G

5-Jun-07 16:34

I need to change the display from (123.98) to -123.98 in SSRS.

I tried couple of fromat option on text box and still no good results...

Any Help!!

Amit

Calling Ref Cursors in Data Access application blocks

kjosh5-Jun-07 16:21

kjosh

5-Jun-07 16:21

Hi,

I have one procedure in Oracle which is returning the REF Cursor I am accessing it by using the Data access application block DAAB like the following:

OracleParameter param = new OracleParameter();

param.ParameterName = "Emp_Cursor";

param.OracleType = OracleType.Cursor;

param.Direction = ParameterDirection.Output;

param.Size = 3000;

Database db = DatabaseFactory.CreateDatabase("DBConnection");

//DbCommand cmd = db.GetSqlStringCommand("Select * from emp");

DbCommand cmd = db.GetStoredProcCommand("emppackage.select_employee");

db.AddOutParameter(cmd, param.ParameterName, param.DbType, param.Size);

DataSet ds = new DataSet();

ds = db.ExecuteDataSet(cmd);

GridView1.DataSource = ds;

GridView1.DataBind();

But iam getting this error:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SELECT_EMPLOYEE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

How to call Ref Cursors in DAAB.

Thanks in advance.

SSIS: Changing app.config settings for a custom dll called from the script task

fodonnel5-Jun-07 13:46

fodonnel

5-Jun-07 13:46

Hi,
I am using a custom .NET DLL from within a script task in SSIS. I want to override some configuration settings in the the DLL (app.config settings). Can I do this is SSIS and if so how?

Thanks,
Fergal.

How to run a stored procedure from within a stored procedure

AndyDev5-Jun-07 13:41

AndyDev

5-Jun-07 13:41

Hi,

I have two stored procedures that returns a SELECT result.

I want to be able to return this results, from a third stored procedure, according to a parameter.

Something like this:

IF (@DisplayUnknown = 0)<br />
    BEGIN<br />
        EXEC stpSnapshotNormal(@Type, @dtDate)<br />
    END<br />
ELSE<br />
    BEGIN<br />
        EXEC stpSnapshotUnknown(@Type, @dtDate)<br />
    END

This of course does not work.
Does anyone knows how is it possible to make work?

Thanks!

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