|
Hello everybody ...
Well, I have a problem in something with SQL Server 2000 ...
I want to write a procedure in stored procedure in SQL Server 2000, this stored procedure should output a variable holding the number of records in the table.
ex
select count(*) from tbl
where tblAccount = 2828;
I want this procedure to output the number of records retrieved, and i want to get it from ASP page. Actually i used a command object in ASP to execute my procedure, and with a recordset.
rs.open cmd
but how can i get the output from this procedure???? and how can i write like this procedure in SQL Server 2000 ????
Best Regards
Sam
Sam2004
|
|
|
|
|
If you've used SELECT and opened a Recordset, the results will be in the Recordset object. The recordset has the concept of the current row - the Value property of the Field objects in the Recordset's Fields collection will contain the retrieved value of that field in the current row.
Straight after opening, if any rows were retrieved, the Recordset object's current row will be the first row returned. If no rows were retrieved, the EOF property will be False.
Since you haven't given the field a name in the result set, you will only be able to access it by position, i.e. as rs.Fields(0). To give it a name, use
SELECT COUNT(*) AS RowCount
FROM tbl
WHERE tblAccount = 2828; As for the procedure, the easiest way is to send the SQL explicitly to the server, by setting the command object's CommandText property. This isn't the best performing method, though; look up the documentation on stored procedures and parameters.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi
how to know the sapce used in the data file or log files ?
the sp_spaceused stored proc gets the space used by a database or object.But what i need is the space used by in each data or log file.
how to get this info ?
|
|
|
|
|
You can use your programming language to get size of your database and its log file.If its C# you can use FileInfo.Length and if it is C++ you can use GetFileSize().
Mazy
"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein
|
|
|
|
|
thank you .. but seems that my question wasn't clear enough.
I can get the file size (OS file size) using sp_helpFile. But this is the total space allocated for the file..
What i need is to know how much actual space consumed by data or log within the file..
for example you can allocate a 50MB file while the data actually use 20MB. In this case i want to get 30MB.
|
|
|
|
|
USE database
EXEC sp_spaceused I can't find a way of specifying which database you want to look at, other than just selecting the database with USE.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hello,
DB Admin is not one of my stong points so I'm a bit stuck. I have a development database that has almost filled the disk. I want to reduce the file sizes so I can continue development (a process I left running over the weekend has increased the log file by 10Gb - It didn't work so I have to fix and rerun it and I only have 5Gb left)
Can anyone let me know how to reduce the file sizes back to something sensible (the operation I was running modified about 17% of the records and deleted 83% of the records it was working on). I tried Shrink Database but it reports that it has shrunk the database by a few hundred Mb but the files are physically the same size. 
Any help would be much appreciated.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
|
(This applies to SQL Server 2000)
If you have recovery model for the database set to Full, everything you do is permanently recorded in the transaction log until you back it up. Only then will SQL Server truncate the log to the backup point (you can disable this behaviour by specifying WITH NO_TRUNCATE).
Full recovery allows you to restore the database to any point in time, which SQL Server performs by undoing all the operations in the transaction log after that point (and any operations in transactions that hadn't committed before that point). If you don't want this capability, select the Simple recovery model. In this model, you can only restore a backup of the database - you can't rewind to a particular point in time.
A compromise candidate is Bulk-Logged mode. Here, any bulk operations (BULK INSERT, bcp, CREATE INDEX, SELECT INTO, WRITETEXT and UPDATETEXT) are only marked as having happened; the actual operations are not recorded. However, SQL Server does indicate which regions of the database have been affected by a bulk operation, and backs those up with a full database backup or a transaction log backup. If you aren't performing bulk operations, this option saves no space at all.
The default for Standard or Enterprise Edition is Full, while the default for Personal Edition or MSDE is Simple. Remember that the settings for any new databases are copied from the model database (since a new database simply is a copy of model, which is how SQL Server manages to create a new database in a few seconds).
For more horrible detail, my reference book is Inside SQL Server 2000 by Kalen Delaney (MS Press).
If you're just developing, rather than working on a production server, I suggest using the Simple model. If you deploy to your production server with a backup/restore model, remember to change the recovery model afterwards.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you for quite a comprehensive answer.
I did get my problem solved by other means. I detached the database, threw away the log file and reattached it. (Well, I was a bit more careful than that but that was essentially the result - which got me back more disk space that I had when I started)
Curiously, the process appears to be working now, and the log file isn't filling up quite so fast! 
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
|
|
|
|
|
Dear all
I have some problems while connection oracle 10g
i have tryed oracle connect ,ole connction and ODBC conection and i could not connect
does any body know any information about this problem
Regards
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
Does anyone know how to join two datatables in ADO.NET (VB.NET)
i want to basicly do an outer join of two tables with the same primary keys
note: i can not connect to the database to do an sql command.
|
|
|
|
|
babydogman wrote:
Does anyone know how to join two datatables in ADO.NET (VB.NET)
See DataRelation class.
Mazy
"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein
|
|
|
|
|
Is there any way (besides triggers) to have an auto updating column in SQL Server? I'm looking for something like the "timestamp" column type from MySQL that automatically updates to the current date/time whenever an update/insert is performed. Is there anything like this type in SQL Server? Thanks Muchly.
- Aaron
|
|
|
|
|
It doesn't update to the current date and time, but SQL Server 2000 does have the rowversion type which is automatically updating. IIRC, the value for a changed row is only guaranteed to be different from the old value, not necessarily greater than the old value.
If you want date/time behaviour, you might want to consider using a trigger:
CREATE TRIGGER tg_timestamp ON MyTable
FOR INSERT, UPDATE
AS
UPDATE MyTable
SET TimeStampField = GETUTCDATE()
WHERE PrimaryKey IN
( SELECT PrimaryKey FROM inserted ) inserted is a pseudo-table (constructed from the transaction log) containing the new data for all the rows that were inserted or modified (there's also a deleted pseudo-table for the old version of modified rows, for an UPDATE trigger). You'll want to ensure that recursive triggers are not enabled, or you'll just end up going recursive.
I suggest using the UTC date and time so you don't get Daylight Savings Time effects.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
If there's no date/time updating field I guess I'll go with a trigger. Much thanks for the reply and the example.
- Aaron
|
|
|
|
|
Hi,
I'm using a little code to insert data in a Database.
Dim mCmd As System.Data.SqlClient.SqlCommand
mCmd = Me.vObjConn.getCmd
mStrSql = "INSERT INTO ZAP_CATEGORIE (CAT_Description, INS_USER, INS_DATTME) VALUES (@Cat_Desc, 'cde', getdate())"
' A SqlCommand object is used to execute the SQL statement.
mCmd.CommandText = mStrSql
With mCmd
.Parameters.Add(New SqlParameter("@Cat_Desc", SqlDbType.NVarChar, 250)).Value = Me.aCAT_Desc
End With
Try
mCmd.ExecuteNonQuery()
Catch exec As Exception
Return exec.message()
End Try
The first time I call this method, no problem.
The second time I got the following error message in the Excpetion :
The variable name '@Cat_Desc' has already been declared. Variable names must be unique within a query batch or stored procedure."
Can someone tell me where I'm wrong.
Thx
Sybux
|
|
|
|
|
SqlDbType.NVarChar, 250)).Value = Me.aCAT_Desc
YOU MISSTYPED @ WITH LETTER a it should be @CAT_DESC 
ForgetAboutIt
|
|
|
|
|
You're caching the command object between runs, right? The first time you run through, the line
.Parameters.Add(New SqlParameter("@Cat_Desc", SqlDbType.NVarChar, 250)).Value = Me.aCAT_Descadds the parameter to the command. When you run it again, the parameter @Cat_Desc is already there, so you get the exception.
There are two ways you can go: separate out setting the value from creating the parameter, and only creating the parameter once, or creating a new command object every time. I'd actually go for the second option, unless you need to run this command inside a tight, performance-critical block of code. In the long run, using less memory tends to perform better than caching lots of objects.
To do this, simply change
Dim mCmd As System.Data.SqlClient.SqlCommand
mCmd = Me.vObjConn.getCmd<pre>to<pre>Dim mCmd As New System.Data.SqlClient.SqlCommand
Secondly, never catch System.Exception. You can end up catching all sorts of critical errors other than those coming from the SQL layer. If you want to catch those, catch SqlException. From a design standpoint, don't catch an exception if all you're going to do is return a return code. Instead, allow the exception to propogate to the caller, and catch it there. It's OK to catch an exception and throw a new one of a different type to simplify the caller's exception code, but only if you can add more meaning (for example, throwing a user-defined BadFormatException if a file operation raised an IOException when you expected more data when reading a file). If you do this, remember to set the InnerException property.
The CLR will propogate some critical exceptions (such as ThreadAbortException or, IIRC, StackOverflowException) even if you do catch them.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thx for your answer but I will a third method becaus my SQLCommand is issue from my Database class so I can't make a new each time.
Here is my solution :
<code>
Dim mParDesc As New SqlParameter("@CAT_Desc", SqlDbType.NVarChar, 250)
With mCmd
.Parameters.Add(mParDesc).Value = Me.aCAT_Desc
End With
mCmd.ExecuteNonQuery()
mCmd.Parameters.Remove(mParDesc)
</code>
I don't know what is ths impact on perf. but I don't need an answer in the last 0.0001 seconds
Regards
Sybux
|
|
|
|
|
In my home installation of VS.NET 2002, I now cannot create a new project (of any kind, WIn WEb etc.) because I get the error "Automation Sever cannot create object". I can run existing projects and even fill in the broken Solution/Project stub with files created at work. When I do that the projects will run fine.
The "stun" that is left behind consists of the sln and proj files without any other IDE generated files that are appropriate to the type of project selected. Un the simplest case (a Class Library project) there is now default class cs file generated.
This installation ran fine for a long time then broke. I had just finished deleting some projects. Perhaps I deleted something that I shouldn't have?
Any clues?
Gary Hyslop (at home)
|
|
|
|
|
This is Gary at work. Noone seems to have any suggestions. See my problem in Soapbox section. I thin the solution is to change my e-mail address in the Profile section of this site to the one of Garya at home. If Gary at home can edit this message from work then he can be the sole user of the site since he can read his home e-mail when he's not home.
Gary Hyslop (at work)
|
|
|
|
|
I have C# project that connects to a SQL server 2000 database , I want to deploy it with the project to the end user PC. can you help me please? [I failed to do it using Install Shield 9.0]
Mahmoud Hossam
|
|
|
|
|
For my last project I used the SQL Server Database Maintenance functions from http://www.installsite.org/pages/en/isp_db.htm[^]
Michael
But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel
|
|
|
|
|
I have no experience in this area, but have something to say anyway
SQL Server 2000 is a licensed software product, each copy of which must be legally licensed. Unless you have licenses from Microsoft for each distributed copy, it would be illegal to distribute it with your software.
MSDE (the stripped down version) is a different matter. That can be legally distributed in many cases. You may find more of what you are looking for if you focus your searches/enquiries on MSDE.
|
|
|
|
|
I'm using the following command to select all the columns from a table
SELECT * FROM table When the command executes it's not returning the columns in the same order they were added to the table. For example if I created the table with column1, column2, column3, column4, they're being returned as column1, column4, column2, column3 (for example). Is this normal? Is the best way around it to simply specify the columns in the order I want them in the query? Thanks. I'm using MSDE.
- Aaron
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
