Database

Re: Text datatype not exceeding 8000 characters

13-Jun-07 23:21

to my knowledge 8000 is max. in SQL Server 2000

Regards
KP

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

2 DataBases? Yes, 2 DataBases

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

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.

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

Krish - KP13-Jun-07 18:06

13-Jun-07 18:06

If the table is not owned by dbo then?

Regards
KP

andreumv13-Jun-07 21:58

andreumv

13-Jun-07 21:58

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

Krish - KP13-Jun-07 17:49

Maximum length of a field in SQl SERVER

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

@@identity

seemamltn13-Jun-07 7:25

seemamltn

13-Jun-07 7:25

Hi,

I have a table in which I have four columns.

ID (primary Key)
FirstName,
LastName,
Email

I want to add another column as @@identity in this already existing
table.

How can I do that.

seema

Re: @@identity

andyharman13-Jun-07 7:31

andyharman

13-Jun-07 7:31

If ID is an integer then you would do:

alter table MyTable alter column
  ID integer identity(1,1) not null

When you insert new records then you use @@identity to show the identity number that SQL-Server has allocated to your new record:

Insert into MyTable (FirstName, LastName, Email)
  values ('Andy', 'Harman', 'Andy@AndysWorld.com')
Select @@Identity

You can find out more about this in the SQL Server Books-Online.

Regards
Andy

S.Rajeshwar13-Jun-07 6:13

S.Rajeshwar

13-Jun-07 6:13

Hi All,

I am working on SQL SERVER 2000.I have a problem of Characters maximum length.I declared a cloumn of length 2000 characters of Data type Char.But it is taking only 255 characters.even i tried with ntext,text,varchar,nvarchar datatyupes with 2000 length but it is accepting 255 characters only.No restriction from coding side as well.

Plase any help me to solve this problem.

Thanks & Regards
S.Raju.

Colin Angus Mackay13-Jun-07 6:24

Colin Angus Mackay

13-Jun-07 6:24

I've never seen this problem. I've happily inserted much larger amounts of text. You'll have to post your code. I'm guessing that somewhere or other you have something that is truncating the text at 255 chars and that is causing the problem rather than the database.

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

Mike Dimmick13-Jun-07 12:15

Mike Dimmick

13-Jun-07 12:15

If Query Analyzer is only displaying 255 characters, that's because that's the maximum it asks for by default.

Go to Tools, Options, Results tab and increase Maximum characters per column.

Stability. What an interesting concept. -- Chris Maunder

Colin Angus Mackay13-Jun-07 13:11

Colin Angus Mackay

13-Jun-07 13:11

Mike Dimmick wrote:
If Query Analyzer is only displaying 255 characters...

Well thought of! I never thought that it might be what he was seeing in Query Analyzer. I just assumed his application was getting back less information that was being put in.