Dear Experts,
I have a stored procudure which can be successful called from application and update against tables, the question is how do I get the status which can indicate how many records were updated or no any record was updated in the action?
Thanks in advance.
Hi,@.@.ERROR returns the error number for the last T-SQL statement executed while
@.@.ROWCOUNT returns the number of rows affected by the last SQL statement.
Hello,
In my database (SQL Server 2005), some data were inserted from a external application.
In order to validate the data , I want to get the SQL statment executed by the application.
Is this possible?
Thanks
Robert
Hi Robert,
The Data Manipulation sql statements are not audited or logged by default.
If you want to capture SQL Statements from the application, you can use SQL Profiler.
If you need this for auditing, You could you use server side traces (its uses the same api as SQL Profiler but runs in the background).
Jag
|||Hi Jag,
Thanks for your replay.
SQL Profiler is a good tool, I finished my work with it.
Robert
Thanks for your attention
Freedeveloperread this post:
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=601604|||Hello!
Ok, Thank for the help.
I resolve the question in microsoft c# forum wit the use of sp_helpdb
SqlCommand cmd = new SqlCommand("sp_helpdb",objConexion);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@.dbname",databasename);
this store procedure return the database size and another data.
thaks,
Freedevelopersql
Hi,
I use a strong-typed DataSet to build the data access tier of my application, and the IDE generates most of code for me. However I can't find a way to get the value returned by a stored procedure(The stored procedure seems like 'RETURN 0').I noticed that a @.RETURN_VALUE parameter is added automatically for each query method, but I don't know how to read the value.
Anybody could help me with the problem? Thanks.
Hi,
I am assuming you are trying to return the value via VB or C#. You must load your sql command in to a data reader. It loads your values in an an array which is index. Here is an example which returns numerous values and assigns one of the values to a label.
VB:Dim myConnectionAs SqlConnection
Dim myCommandAs SqlCommand
myConnection =New SqlConnection("Data Source=MYCOMPUTER\SQLEXPRESS;Initial Catalog=MYDATABASEIntegrated Security=True")
myConnection.Open()
' sql statment
myCommand =New SqlCommand("Exec MY_SP", myConnection)
Dim dr = myCommand.ExecuteReader()
Dim iAs Integer = 1While dr.read()
Me.lblmel.Text = dr(2).ToStringEnd While
dr.Close()
myConnection.Close()
C#:
SqlConnection myConnection;|||
SqlCommand myCommand;
myConnection =new SqlConnection("Data Source=MYCOMPUTER\\SQLEXPRESS;Initial Catalog=MYDATABASEIntegrated Security=True");
myConnection.Open();
// sql statment
myCommand =new SqlCommand("Exec MY_SP", myConnection);
object dr = myCommand.ExecuteReader();while (dr.read()) {
this.lblmel.Text = dr(2).ToString;
}
dr.Close();
myConnection.Close();
U can use outparameter to get the value
Thank u
Baba
Please remember to click "Mark as Answer" on this post if it helped you.
I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.
So I have a question
1.) How do I get today's date in C# and how should this be passed to SQL server?
you can get the current date directly in SQL using getdate()|||Doing it in C# will return you the client system date. Getting the current date in SQL will ensure all datetime recorded in your application are in sync. If you are using the date for some sort of comparison determine the sequence of event, using the date from the client might pose some timing issue. Unless what you want is really the client's local time, you should use the Server time.|||
brgdotnet:
I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.
So I have a question
1.) How do I get today's date in C# and how should this be passed to SQL server?
to get current date in c# use DateTime.Now.ToString()......... there is also some different methods under Now... use what is appropriate for u .
if u r using a procedure it is easier.... say proc. name saveCurrentDate
String s=DateTime.Now.ToString();
String queryString="saveCurrentDate '"+s+"'"; //// or String queryString="insert int myTable(mydate) values ('"+s+"')";
hope it will hellped u
|||
Actually I am processing some records from a comma delimited .txt file. Each item seperated by a comma, will map to a field in a SQL Server database table.
The date is in the format: 20070423, that is YYMMDD
So I need to take the date value and then write it into SQL server. So this brings us to another question. Do I need to change this date format to comply with that of SQL server? If so what format does it need to be in? I need some specifics so if you could even produce a code sample for C#, that would be great.
|||You can pass in the date in format YYYYMMDD as a string. SQL Server will implicitly convert it to string. As long as you are using Universal format YYYYMMDD, it is fine. Other format like MMDDYYYY or DDMMYYYY will be depending on the language setting that you used. So stick to YYYYMMDD and you will not go wrong.|||I am confused about one thing though? I am reading from a text file where the format is in YYMMDD. Should I store this in SQL server as a data type of datetime or smalldatetime?
If so, I definitely have a date that, I can read from the text file, but I don't have a time? What do you think? If I did use datetime, would it just append the current time to the date I entered?
|||you should always use proper data type for the data. In this case, you should use datetime or smalldatetime to store the date.
"I can read from the text file, but I don't have a time?"
You can still use datetime data type. Just set the time to 00:00:00. For your case, as your date string is in YYYYMMDD format without time, when you insert into table, the time will be stored as 00:00:00
I need to get the application directory from report server. I wrote System.AppDomain.CurrentDomain().BaseDirectory() in the expression. It works when I click preview in IDE. But it doesn't work when I deploy on the server. How come?
Because BaseDirectory requires CAS FileIOPermission. Try creating an utility assembly and registering it in rssrvpolicy.config. You may need to assert FileIOPermission as well.
sqlI have an application that uses SQL-DMO to list object properties. Due to user-schema separation in Sql server 2005, sql-dmo returns owner name as schema. How to get the owner name or schema name of an object using SQL-dMO ?
Regards,
PraveenSQL-DMO has been made backward compatible, and does not know the difference between a SQL Server 2000 owner-schema and a SQL Server 2005 schema. As we do not plan to expose SQL Server 2005 features in SQL-DMO, this will not be possible.
In SMO the collection of schemas is exposed through which you can infer ownership.
I want the number of rows returned by this query.
How can i get the number of rows?
Please help me..
waiting for your replies..
Prem
(premratan@.hotmail.com)Select count (*) as "row count" from (select <any query here>) as t
For some reason, the final table alias "as t" is required.
Goetz Graefe
"Prem" <premratan@.hotmail.com> wrote in message
news:2f7d06ff.0311111515.2a2a040c@.posting.google.c om...
> To all gurus,
> I am developing an application in which i want to show the number
> of rows returned by the query.
> e.g.
> Select Categories.CategoryName, Products.ProductName,
> Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
> ProductSales
> FROM
> ((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
> Orders.OrderID)
> INNER JOIN Products ON [Order Details].ProductID = Products.ProductID)
> INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
> WHERE
> (Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
> GROUP BY
> Categories.CategoryName, Products.ProductName
> I want the number of rows returned by this query.
> How can i get the number of rows?
> Please help me..
> waiting for your replies..
> Prem
> (premratan@.hotmail.com)|||Refer to @.@.ROWCOUNT in SQL Server Books Online. If you are using ADO in your
application, then you can use the recordset's RecordCount property to get
the value at the client side.
--
-- Anith
( Please reply to newsgroups only )|||premratan@.hotmail.com (Prem) wrote in message news:<2f7d06ff.0311111515.2a2a040c@.posting.google.com>...
> To all gurus,
> I am developing an application in which i want to show the number
> of rows returned by the query.
> e.g.
> Select Categories.CategoryName, Products.ProductName,
> Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
> ProductSales
> FROM
> ((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
> Orders.OrderID)
> INNER JOIN Products ON [Order Details].ProductID = Products.ProductID)
> INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
> WHERE
> (Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
> GROUP BY
> Categories.CategoryName, Products.ProductName
> I want the number of rows returned by this query.
> How can i get the number of rows?
> Please help me..
> waiting for your replies..
> Prem
> (premratan@.hotmail.com)
After running the query, you can do this:
select @.@.rowcount
If you need to use the value later, you can put it in a variable:
set @.rows = @.@.rowcount
Simon
Hi,
I am new to sql and is working with sql server managment 2005 +c# 2005.
My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.
there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.
In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.
I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)
How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.
Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.
Priyadarshini
My question is how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails.
Using TransactionScope with single connection in DataObject. I am trying to insert row in two dataTable using its own tableAdapter (two tableAdapter).
I have Product table with ProductID primary key with incriment identity. and that ProductID is used to insert row in ProductHistory Table. Lets say Product table has the last ProductID=8 (8 rows) and the next ProductID will be 9.
When I insert row in both table and if the second table insert fails both gets roll back (which is good). but when I insert again another time the Product ID=10 not 9. Is there any way to roll back the ProductID in Product table so when i insert next time it has incriment number instead of gap.
That is the nature of IDENTITY column. The number once assigned to a row is gone whether the transaction succeeds or not. Even if the transaction succeeds, if you delete the row, the number is not re-assigned. Hopefully your application is based only on the PK-FK relationship and not on the serial order of the data.
|||Thats what I thought but thought there might be a way I guess not, My table relation is based on PK-FK . It looks odd when some one is viewing Product table and has a gap in Product ID in between product (further someone might have question or confuse). I guess I have to manually create Product ID Primary Key data.
Thank for your answer
|||Just out of curiosity, why is the ProductId important? ProductId should just be a PK to identify the product, it should not matter if there are gaps.
|||For Developer standpoint it shouldnt matter at all, For other users, they would like to see incriment by 1. The product page displays Product ID, Product Name, Product Insert Date. So if they will see gap in Product ID inbetween they will have question and trust me they dont want that way, I have dealt in similar situation before.
|||So why is productId even shown? Do users care?
|||Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID
|||
rumax96:
Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID
Then a gap won't matter - unless your users remember that the product they want appeared 4 products further down a page than product 26, and therefore assume they want product 30. I know users can be extremely odd, but that seems to me to be pushing it a bit.
|||
Thanks Mike, I understand , From a developing perspective it doenst make sense wether there is a gap or not in Primary key field. The maing thing is user dont like gap in their ProductID and If that is the way the users wants then We have no choice. Like I said I have deal before in similar situation.
