Database Discussion Boards

manni_n13-Jun-07 22:16

13-Jun-07 22:16

i have just started learninf SQL 2000
when i mamakin simplest query in query analyzer select * from authors, where authors is the name of table,
its giving error that invalid object ' authors'.
how this error can be resolved???

Re: invalid objects

Sylvester george13-Jun-07 22:39

Sylvester george

13-Jun-07 22:39

1. check the authors tabls exists in the database
2. check whether you selected / pointed the right database

Regards,
Sylvester G
sylvester_g_m@yahoo.com

Re: invalid objects

Colin Angus Mackay13-Jun-07 23:07

Colin Angus Mackay

13-Jun-07 23:07

manni_n wrote:
its giving error that invalid object ' authors'.
how this error can be resolved???

If you are just learning a better question would be "What does the error mean?" so you can understand what it means in the future.

SQL Server has a number of database objects. These include tables, views, stored procedures, functions and so on.

You can get a list of all object in the database by typing

SELECT * FROM sysobjects

The message "invalid object" indicates that the object either does not exist, or it is the wrong type of object for the context in which you are trying to use it.

In this case you refer to a table object in the SELECT statement that does not exist (there may be another object in the system called authors, but that will be a different type of object, or be used in a different context.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

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

My website

How to return cursor from stored proc??

FishiFishi13-Jun-07 22:08

FishiFishi

13-Jun-07 22:08

Hi all

i wana come to know that how can i return cursor from a stored procedure???

Regards

Qaiser

Re: How to return cursor from stored proc??

Tirthadip13-Jun-07 22:36

Tirthadip

13-Jun-07 22:36

I think A cursor is a server side thing. You can't "return" this to a client.
Anyway why do u need this??

Tirtha

Do not go where the path may lead, go instead where there is no path and leave a trail.

Author: Ralph Waldo Emerson (1803-82), American writer, philosopher, poet, essayist

Re: How to return cursor from stored proc??

N a v a n e e t h14-Jun-07 23:22

N a v a n e e t h

14-Jun-07 23:22

Why you need to return a cursor from a Stored Procedure ? I think you got confused with Oracle .. Is it ?

Navaneeth
My Website

How to get top n records

FishiFishi13-Jun-07 22:00

FishiFishi

13-Jun-07 22:00

Hi All

i want to get top 5 records from the table. what would be the query?

Regards

Qaiser

Re: How to get top n records

Tirthadip13-Jun-07 22:32

Tirthadip

13-Jun-07 22:32

Returning only the first N records in a SQL query differs quite a bit between database platforms. Here's some samples:

Microsoft SQL Server

SELECT TOP 10 column FROM table

PostgreSQL and MySQL

SELECT column FROM table<br />
LIMIT 10

Oracle

SELECT column FROM table<br />
WHERE ROWNUM <= 10

Sybase

SET rowcount 10<br />
SELECT column FROM table

Firebird

SELECT FIRST 10 column <br />
FROM table

Tirtha

Do not go where the path may lead, go instead where there is no path and leave a trail.

Author: Ralph Waldo Emerson (1803-82), American writer, philosopher, poet, essayist

how to call a webservice from stored procedure in SQL Server2000?

chronossu13-Jun-07 21:27

chronossu

13-Jun-07 21:27

I've created a dll (with vb6) to call a webservice, and this works fine when called in vb6.
I need to run this from stored procedure in SQL Server 2000,
because we have many clients that run application that access an SQL server.
It'll be hard work to install the dll into each of the clients pc (and many of them don't have MS SOAP installed), so instead I've been planning to install the dll in the server that runs SQL server, while others clients can just use store procedure to get the result from the webservice (which access to another SQL server machine).
We've tried using linked server but the response is too slow.
When I've tried to create store procedure to access the dll, it always return NULL value.

Anyone can help with this problem?

Thanks in advance,

Chronos

create alias and tables on my pc and copy them with setup or create them in installed pc

upamir13-Jun-07 21:19

upamir

13-Jun-07 21:19

Have a good day.

Must i create alias and tables on my pc while design and copy them with setup or must i create them in installed pc.

so my real question,

i want to create password protected tables to use but i couldnt create this table in run time.i can do it from database desktop->restructure menu.
İs there anyway to create this table in runtime or make a table which is not protected in c++ and restruct it in c++;

thanks.

Text datatype not exceeding 8000 characters

Rajiya13-Jun-07 20:51

Rajiya

13-Jun-07 20:51

Hi,

I have a stored procedure which is having a input parameter as TEXT datatype. I am passing a string to it whose length is 9000. It does not accept more then 8000 characters of the string. How to solve this issue?
Is there any method in which i can increase the length of the datatype. or is there any other datatype whose datalength is more then 8000?

Thanks,

Riz

Re: Text datatype not exceeding 8000 characters

Krish - KP13-Jun-07 23:21

Krish - KP

13-Jun-07 23:21

to my knowledge 8000 is max. in SQL Server 2000

Regards
KP

Re: Text datatype not exceeding 8000 characters

Mike Dimmick14-Jun-07 9:20

Mike Dimmick

14-Jun-07 9:20

If you're using classic ADO, you must use the adLongVarChar datatype for the parameter, and use AppendChunk to write the data to the parameter rather than simply assigning the Value property.

For ADO.NET and the SQL Server data provider, use the SqlDbType.Text type.

Stability. What an interesting concept. -- Chris Maunder

Re: Text datatype not exceeding 8000 characters

Rajiya17-Jun-07 18:59

Rajiya

17-Jun-07 18:59

Hi,

Thanks for ur reply....
I want to know how exactly to implement it in the stored procedure. Can u give me an example? It's very urgent.

Thanks,

Riz

Query doubt.....(Urgent need plz hlp me),

Member 387988113-Jun-07 17:55

Member 3879881

13-Jun-07 17:55

Hi i am week in query,
plz solve my probs...,
the following queries,that first query and second query both are working...,
Now my question is,
What are the fields, i am getting in the two queries those want to show in single query...,
Means,in first query ur giving date in that it want to take the year only
and check with tblmonthhourdetails.sdate
and same time the tblmonthhourdetails empid want to equal with the employeemaster.empid

------------------------------------------------------------------
SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName,
dbo.tblTimeCardmain.carddate, dbo.tblTimeCardmain.reportingtime,
dbo.tblTimeCardmain.leavingtime, dbo.tblSalaryDetails.Whrs,
dbo.tblTimeCardmain.shift, dbo.tblTimeCardmain.projectid
FROM dbo.tblEmployeeMaster INNER JOIN dbo.tblTimeCardmain ON
dbo.tblEmployeeMaster.empId = dbo.tblTimeCardmain.empid INNER JOIN
dbo.tblSalaryDetails ON
dbo.tblEmployeeMaster.empId = dbo.tblSalaryDetails.EmpID
WHERE dbo.tblTimeCardmain.carddate = '5/2/2007'
AND dbo.tblTimeCardmain.app1 = 1 AND
dbo.tblTimeCardmain.projectid = 100
-----------------------------------------------------------------------
SELECT SUM(MedicalLeave) AS Ml, SUM(AnnualLeave) AS AL, EmpId
FROM dbo.tblMonthHourDetails
WHERE (DATEPART(YYYY, SDate) = 2007)
GROUP BY EmpId, SDate
-----------------------------------------------------------------------
Plz hlp me...,

-- modified at 1:05 Thursday 14th June, 2007

Magi

Re: Query doubt.....(Urgent need plz hlp me),

andyharman13-Jun-07 23:01

andyharman

13-Jun-07 23:01

Hi Magi

Try this:

SELECT EM.empId, EM.empName, TC.carddate, TC.reportingtime,
  TC.leavingtime, SD.Whrs, TC.shift, TC.projectid,
  (SELECT SUM(MedicalLeave) FROM dbo.tblMonthHourDetails MHD
    WHERE MHD.EmpId = EM.empId AND YEAR(MHD.SDate) = 2007) AS ML,
  (SELECT SUM(AnnualLeave) FROM dbo.tblMonthHourDetails MHD
    WHERE MHD.EmpId = EM.empId AND YEAR(MHD.SDate) = 2007) AS AL
FROM dbo.tblEmployeeMaster EM
INNER JOIN dbo.tblTimeCardmain TC
ON EM.empId = TC.empid
INNER JOIN dbo.tblSalaryDetails SD
ON EM.empId = SD.EmpID 
WHERE TC.carddate = '5/2/2007' 
AND TC.app1 = 1
AND TC.projectid = 100

I have used table aliases (EM, TC, etc) to make your query more readable. Correlated sub-queries are used to get the medical and annual leave.

Regards
Andy

Re: Query doubt.....(Urgent need plz hlp me),

Member 387988113-Jun-07 23:42

Member 3879881

13-Jun-07 23:42

Thanks lot...,
Its working cool,
Keep in touch,
Take care,

Magi

Re: Query doubt.....(Urgent need plz hlp me),

Andy *M*13-Jun-07 23:15

Andy *M*

13-Jun-07 23:15

Magh_M wrote:
Query doubt.

You have a question, not a doubt.

How to display duplicate values in a table ?

ahmedalisha13-Jun-07 17:30

ahmedalisha

13-Jun-07 17:30

Hi,

I have one table like

sno name
--- ------
1 hai
2 hello
3 hai
4 hello
5 hello
6 bye
7 take care

from the above table i want to display only duplicate values like

sno name
---- ------
1 hai
2 hello
3 hai
4 hello
5 hello

how to display it ? Confused | :confused:

Re: How to display duplicate values in a table ?

Krish - KP13-Jun-07 17:35

Krish - KP

13-Jun-07 17:35

SELECT * FROM tableA WHERE name IN (SELECT name FROM tableA GROUP BY name HAVING Count(*) > 1)

Here Sub-Query retruns only those names which are present more than once and main Query returns all rows where the same is present.

Regards
KP

2 DataBases? Yes, 2 DataBases

andreumv13-Jun-07 8:38

andreumv

13-Jun-07 8:38

I wish to know if there is any way to create a trigger that runs when a user makes some insert / update / delete action on a table of a database and then add the same values to a table that dwells in another separate database, both Sql-Server. How can I use the table in the external database? is it possible?

Thanks.

Re: 2 DataBases? Yes, 2 DataBases

Sankar Komma 13-Jun-07 11:08

Sankar Komma

13-Jun-07 11:08

hi
It is possible,
just call the tablename like

databasename.dbo.tablename

k.ravi sankar

Re: 2 DataBases? Yes, 2 DataBases

Krish - KP13-Jun-07 18:06

Krish - KP

13-Jun-07 18:06

If the table is not owned by dbo then?

Regards
KP

Re: 2 DataBases? Yes, 2 DataBases

andreumv13-Jun-07 21:58

andreumv

13-Jun-07 21:58

Yay, it worked nice!
thanks a lot, mate.

Re: 2 DataBases? Yes, 2 DataBases

Krish - KP13-Jun-07 17:49

Krish - KP

13-Jun-07 17:49

andreumv wrote:
How can I use the table in the external database?

does this mean both databases are not in one server. if so, you need to link both using sp_addlinkedserver check online help for syntax and usage.
once after linking you can access the tables like <server>.<database>.<owner>.<table>

if both databases are on same then
<database>.<owner>.<table>

I've used both (in queries/functions/procedures) exceipt in triggers

Regards
KP

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