|
oskardiazdeleon wrote: Does anyone have any idea on how to do a restore. Any help would be appreciated
You cannot backup and restore across a network. The backup device, including a hard disk, must be physically attached to the SQL Server.
|
|
|
|
|
I'm starting a project to convert from a JET databse to an MS SQL database. The application using the data is written in VB 6. On the surface the conversion looks strait forward. Anyone know of any minefields I'm going to run into?
Thanks,
Nick
|
|
|
|
|
I imagine most (if not all) of the SQL in the VB6 app will work just as well with no change on a MS SQL backend.
Of course, you will need a new connection string.
The OleDb adapters, readers etc should work with MS SQL too. Though you will probably want to change them to SQLAdapters etc.
Some saved queries (Access/JET) will need to be converted to MS SQL stored procedures, functions etc.
May want to look through the code for any Access/JET automation code that may be used to do things like show reports etc.
Other than that, I'd be interested to know what you found myself!
Steve
|
|
|
|
|
Q.1 what is function of GO in SP's?
Q.2 If we have to use nested queries then how far we can go i.e.
Select * from where ( select id from where (........IS THERE ANY LIMIT HERE TO USE NESTED LOOPS..........))))))
OTHER QUERIES I'LL POST LATER
THANKS  IN ADVANCE FOR GIVING THE ANSWERS
PARAG
|
|
|
|
|
GO signifies that the current batch of Transact-SQL statements should be sent to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
GO must be on a line of its own but may include comments.
Any number of sub-queries may be nested in a statement.
Steve
|
|
|
|
|
My post regarding the GO statement was correct, however, I omitted to mention that this relates to the Query Analyzer and not stored procedures (as Colin pointed out).
I apologise if that omission rendered my post misleading.
Steve
|
|
|
|
|
ParagGupta wrote: Q.1 what is function of GO in SP's?
In contrast to what the other poster has said GO has nothing to do with stored procedures. It is simply a preprocessor command given in Query Analyzer (and you can set Query Analyzer to accept other things in place of GO if you prefer). It is not part of the T-SQL language.
GO tells Query Analyzer where to split the script into batches. Each batch is executed on the SQL Server in sequence, but without reference to any batch that came before it in the script. In other words if you DECLARE a variable in one batch, you cannot see it in the next.
With regard to Stored Procedures, there is no way to tell SQL Server that where the end of the stored procedure is. It must therefore come last in a batch and Query Analyzer will delimit the end of the stored procedure with the GO keyword.
ParagGupta wrote: Q.2 If we have to use nested queries then how far we can go
No idea - I've gone 4 deep in one query, but if you are going that deep you might want to rethink your strategy. About 6 months after I wrote that 4-deep query I refactored it to use temporary tables instead, and the query went from taking about 20 minutes to 7 seconds. There is only so much the SQL Server query optimiser can cope with.
|
|
|
|
|
GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.
osql Utility
The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server.
isql Utility
The isql utility allows you to enter Transact-SQL statements, system procedures, and script files; and uses DB-Library to communicate with Microsoft® SQL Server™ 2000.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently.
A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.
SQL Server applications can send multiple Transact-SQL statements to SQL Server for execution as a batch. The statements in the batch are then compiled into a single execution plan. Programmers executing ad hoc statements in the SQL Server utilities, or building scripts of Transact-SQL statements to run through the SQL Server utilities, use GO to signal the end of a batch.
Applications based on the DB-Library, ODBC, or OLE DB APIs receive a syntax error if they attempt to execute a GO command. The SQL Server utilities never send a GO command to the server.
GO is a utility command that requires no permissions. It can be executed by any user.
Regards,
Uma
|
|
|
|
|
I think upto 32 levels. But please check SQL online....
Go is used for batch submission.
Amit
|
|
|
|
|
At the moment I have a query that returns data about meetings in XML format using the
"FOR XML AUTO, ELEMENTS" clause
The problem is that if there is a meeting with the same person on the same day (I know, its hardly a common occurance) the XML output comes out like this:
<br />
<mdate><br />
<![CDATA[02/06/2006]]><br />
</mdate><br />
<br />
<Meetings><br />
<id><br />
<![CDATA[92]]><br />
</id><br />
<complete><br />
<![CDATA[1]]><br />
</complete><br />
</Meetings><br />
<br />
<Meetings><br />
<id><br />
<![CDATA[93]]><br />
</id><br />
<complete><br />
<![CDATA[2]]><br />
</complete><br />
</Meetings><br />
Unfortunatley because of the XSL file doing the formating it does not make table entries for the second meeting (id:93 in this example).
What I want idealy is for the XML output to contain all the meeting information in seperate entries ... and NOT cluster together meetings with the same people one the same date like it is doing.
Any ideas? or should i make a different XSL file to handle this out put? (and if so any hints on doing that as I mostly just loop through output in a very simple manner)
Thanks
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
