|
Hi
If you are using .Net then you can create XSD file for that and then you can store the output in dataset created by XSD.
Regards
Tushar kothari
|
|
|
|
|
How to install the Postgre SQL using Silent Mode in Windows 2000?
|
|
|
|
|
Hi!
I am using SQL Server 2000. I wanted to send MIME type mail through SQL Mail. So I downloaded xpsmtp80.dll and copied to C:\Program Files\Microsoft SQL Server\MSSQL\Binn\DLL. Then I registered it by exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp80.dll'. I granted permission to public by grant execute on xp_smtp_sendmail to public.Now when I am executing this
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MyEmail@MyDomain.com',
@TO = N'MyFriend@HisDomain.com',
@subject = N'My first HTML mail',
@message = N'This is some HTML content',
@type = N'text/html'
select RC = @rc
go
An error generating stating
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xpsmtp80.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
(1 row(s) affected)
Please anyone help me to sort out this problem.
Arghya Mahapatra
|
|
|
|
|
Hi Everybody
Can any body tell me the differnce beetween Client-Side Cursor and Server-Side Cursor.
Thanking you ,
Param Thaker
param
|
|
|
|
|
There are many differences.
Basically, Client-Side cursors work on a default result set that is cached on the client. They can be forward-only or static.
Server-Side cursors have much more functionality, for example, Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor.
Steve
|
|
|
|
|
Hello!
Can anybody suggest me whitch of the following :MS SQL, MySQL, MS Access,(or maybe other one), is more suitable for developing with Java(JSP).
Regards!
|
|
|
|
|
Probably MySQL because I could never find a dedicated MS SQL Server database driver for Java (there's one freely available for MySQL on their website). I native driver lets the application tier (your Java code) connect directly to the database, otherwise you'd have to go through an ODBC driver which is slightly slower. Appart from that MS SQL Server would be fine also.
Sunday Ironfoot
www.dominicpettifer.co.uk (work in progress)
|
|
|
|
|
I'm having problems with Parameterized Queries. The problem seems to occur when I use them in the SELECT portion of the SQL statement. Note the following code...
string sql = "SELECT TOP @NumberOfBlogs blgID, blgShortName, blgLongName, blgDatePosted " +
"FROM tblBlogs " +
"WHERE blgEnabled = 1";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@NumberOfBlogs", 3));
This throws an SQLException "Incorrect syntax near '@NumberOfBlogs'.". However, if I change it and use a Parameterized Query in another part of the statement such as the where clause, it works fine. For instance...
string sql = "SELECT TOP 3 blgID, blgShortName, blgLongName, blgDatePosted " +
"FROM tblBlogs " +
"WHERE blgEnabled = @IsEnabled";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@IsEnabled", 1));
...this code runs fine. Am I right in understanding that you can't use Parameterized Queries in the SELECT portion of the SQL statement? Or is there something I'm not doing right? Will I have to fall back on string concatenation instead? Many thanks!
Sunday Ironfoot
www.dominicpettifer.co.uk (work in progress)
|
|
|
|
|
string sql = "SELECT TOP (@NumberOfBlogs) blgID, blgShortName, blgLongName, blgDatePosted " +
"FROM tblBlogs " +
"WHERE blgEnabled = 1";
Of course you should be using stored proc instead of dynamic sql statements like this anyway
only two letters away from being an asset
|
|
|
|
|
Thanks for that, works a charm. I would normally use Sprocs except it seems overkill for such a simple SQL SELECT statement. Cheers!
Sunday Ironfoot
www.dominicpettifer.co.uk (work in progress)
|
|
|
|
|
Sunday Ironfoot wrote: I would normally use Sprocs except it seems overkill for such a simple SQL SELECT statement.
There are many security advatages by going all Stored Procedures for access. If you revoke access on all your tables and grant execute access to only stored procedures then anyone who may log in can only access the stored procedures. If they can only access stored procedures then you instantly know what they can do. And they can only do what your stored procedures allow them to do.
|
|
|
|
|
Hello everyone,
Recently I've inherited the maintenance of an application who's database contains and image column. I've received a few requests to update a few records, but I've never worked with image-typed columns before, and unfortunately the source code for the application is unavailable to me. I have the files I need to update the table with on my local machine - what would be the best way to get them into the table?
Thanks in advance for any help.
-------------------
abort, retry, fail?
|
|
|
|
|
Here is a small snippet of code from a C# bitmap helper library I use. I don't know of a way to handle this in SQL itself - I have always used an application to update image columns.
_bm is a private Bitmap member variable. I use a stored proc to handle the update. I assign the returned byte array (returned as object) to the image parameter.
public object Save()
{
if (_bm == null)
{
return DBNull.Value;
}
else
{
MemoryStream ms = new MemoryStream();
_bm.Save(ms,_bm.RawFormat);
byte[] buf = ms.GetBuffer();
ms.Close();
return buf;
}
}
|
|
|
|
|
Hi,
I have a student table consisting of rollno,subject,marks
The data looks as follows
1 Maths 89
1 English 77
1 Hindi 80
2 Maths 89
2 English 70
2 Hindi 60
I want to get this 3 rows data into a single row as follows
1 89 77 80
2 89 70 60
How can I get this
I cannot alter the structure of my table
Thanks & Regards
Uma
|
|
|
|
|
Assuming your table stucture looks like this:
StudentScores
=================
StudentID INTEGER
Course VARCHAR(20)
Score INTEGER
You can write a corelated subquery like this:
SELECT
StudentID,
(SELECT Score FROM StudentScores
WHERE StudentId = ss.StudentId AND
Course = 'Maths') AS Maths,
(SELECT Score FROM StudentScores
WHERE StudentId = ss.StudentId AND
Course = 'English') AS English,
(SELECT Score FROM StudentScores
WHERE StudentId = ss.StudentId AND
Course = 'Hindi') AS Hindi
FROM
StudentScores ss
ORDER BY
StudentID
I don't like this type of query because it assumes you know that each student has one score for each Course and that you know the name of each course. You may want to rewrite the subqueries to respect NULL/duplicated results.
(ISNULL(SELECT MAX(Score) FROM StudentScores
WHERE StudentId = ss.StudentId AND
Course = 'Maths'),0) AS Maths
|
|
|
|
|
If you don't know how many classes are in the table, you could make a stored procedure that dynamically creates an sql returning a column for every class:
CREATE PROCEDURE GetGrades AS
DECLARE @sql VARCHAR(2500), @cClass VARCHAR(10)
SET @sql = ''
DECLARE curClasses CURSOR FOR SELECT DISTINCT class FROM tblGrades
OPEN curClasses
FETCH NEXT FROM curClasses INTO @cClass
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', (SELECT grade
FROM tblGrades t2
WHERE t2.StudentId = t1.StudentId
and t2.Class = ''' + @cClass + ''') as ' + @cClass
FETCH NEXT FROM curClasses INTO @cClass
END
close curClasses
DEALLOCATE curClasses
SET @sql = 'SELECT studentid ' + @sql + ' FROM tblGrades t1 GROUP BY studentid'
exec(@sql)
GO
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
when we use function ?
when we use stored procedure ?
how can i defin a function (syntax)?
Witch better to use function or sp ?
can you be fast pls
???
thank you ?
|
|
|
|
|
Hi,
as far as I know, function returns a single value as result. Stored procedures are used to compute something without any direct result. For example filling a table with statistical data can be done perfectly with a sp. Functions are better when you want to compute a single value, for example the gross versus net sum.
Hope this helps a bit,
regards
Sebastian
P.S: For definition of a function use google, I think it will point you in the right direction.
|
|
|
|
|
you can not execute a t-sql query in a function but in stored procedure you can.
|
|
|
|
|
functions return only one value.........sp can used to return more than one value....
function need to be complied each time u use....but sp ones complied u can use it...till if cant change any thing
|
|
|
|
|
This came up in a job interview (I probably didn't get  ) and was wondering what solution was?
If i have the tables (IIRC):
CD:
id, title, serialnumber, cdinfo
Artist:
id, name, artistinfo
Track:
id, title, length
CDTrack (Associates tracks to cd's ?)
id, cdid, trackid
ArtistTrack (Associates artists to tracks ?)
id, artistid, trackid
How do I query based on, say CD name or id and get the rest of the CD data + tracklist complete with the list of artists that performed the each track?
My sql skills are limited to basic selects updates and inserts and this seems to require a lot of joining (I assume) which I'm not too hot on.
Thanks in advance.
|
|
|
|
|
Hi,
okay let's have a try.
I would say this query will compute the desired output:
<br />
select cd.title, cd.serialnumber, cd.cdinfo, track.title, track.length, artist.name, artist.artistinfo<br />
from <br />
cd inner join cdtrack on cd.id = cdtrack.cdid <br />
inner join track on track.id = cdtrack.trackid<br />
inner join artisttrack on artisttrack.trackid = cdtrack.trackid<br />
inner join artist on artisttrack.artistid = artist.id<br />
Don't know if this is completly correct but should go in the right direction.
Regards,
Sebastian
P.S. Wish you the best while searching a job.
|
|
|
|
|
To retrieve data from multiple tables in a single query, you join the tables. To do this, you use the JOIN keyword:
SELECT *
FROM CD
JOIN CDTrack ON CD.id = CDTrack.cdid In the ON clause, you give a boolean expression; the database engine conceptually evaluates every combination of rows from both tables, and if this expression evaluates to True for a given combination, the row is included in the results (unless further constrained by a WHERE or HAVING clause). If the boolean expression uses only equality expressions it's called an equijoin.
There are a number of types of joins. The most common, and the default if you don't specify a type of join, is an inner join. Here, only rows where the ON expression evaluates to True are included. There are also left (outer) joins, where if there are no rows on the right-hand side where the expression is true for a given row on the left-hand side, the output contains the data from the row on the left, while the fields that should contain the data from the right-hand side are set to NULL. A right (outer) join is the same as a left outer join, but with the roles of the left and right tables reversed. There's also a full outer join where rows from both tables with no match from the other table are output. Finally there's a cross join which simply outputs every row from the left-hand table combined with every row from the right-hand table - this uses no ON clause.
The outer joins are generally useful when you're joining a live table to a reference lookup table and can't guarantee that you have the corresponding data in the reference table, but it's imperative that you return all rows from the live table.
You can chain together join operations into a larger query, so for example to get a complete listing of every track on every CD you would write:
SELECT *
FROM CD
JOIN CDTrack ON CD.id = CDTrack.cdid
JOIN Track ON CDTrack.trackid = Track.id
JOIN ArtistTrack ON CDTrack.trackid = ArtistTrack.trackid
JOIN Artist ON ArtistTrack.artistid = Artist.id To know which columns to include in an ON clause requires you to know the schema and what each column represents. In that example we could have joined ArtistTrack either using CDTrack.trackid or Track.id since they have the same value. I'm not sure whether it makes any difference to the query optimizer - probably not, so whichever is clearer.
Mentioning the query optimizer - the optimizer will normally reorder the joins into whatever order it thinks will execute most quickly, taking into account any filter expressions in a WHERE clause. You can force it to execute in the order you specify (might be useful for avoiding deadlocks) by adding OPTION (FORCE ORDER) to the end of the query (on Microsoft SQL Server).
Once you have joined tables together, you may have some ambiguous column names - for example, here all tables have a column named 'id'. You must qualify an ambiguous column name with the table name (e.g. 'Track.id'). Best practice is to always specify the table name, so that your query is robust if columns are later added to one of the tables which clash with a column name from one of the other tables. To avoid having to type out the full name of a table with a long name, you can add an alias for a table to the query, which goes after the table name (optionally after the AS keyword) in the FROM/JOIN clause.
|
|
|
|
|
Brillaint! thank you so much 
|
|
|
|
|
I'm desiging a form that has related information from several tables: parts, manufacturing methods, jobs, raw material, customer po's, etc. To minimize network traffic, I start with a dataset populated with part numbers and another dataset populated with a table of part processes (i.e. a part may have processA, process B, etc.)
Should I use one main dataset to store all the different tables generated by different stored procedures as well as one main SqlDataAdapter to communicate between sql database and windows form? If I do, then when pushing changes back to database via adapter, won't I have to re-set the "select", "update", and "delete" commands for the adapter based on the appropriate table when updating?
The alternative is to have several adapters for the form with their own specific "select" statements, initialized one time, and ready to be used whenever a change is pushed.
Does this sound correct and if so, which is the preferred design method?
Thanks in advance!
John
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
