|
Do either of you know of any tutorials that will show me how to do this properly?
|
|
|
|
|
Hello,
I have the following problem:
Let's say I have some small database with one table named "Activations" with two fields employeeID, date.
In order to add some row to the table I created stored procedure which looks like this:
<code>
CREATE PROCEDURE Activate
(
@employeeID int
)
AS
BEGIN TRANSACTION Activate
INSERT INTO Activations
(employeeID,date)
VALUES(@employeeID, GETDATE())
COMMIT TRANSACTION
</code>
In order to invoke this procedure I use the following code:
<code>
string connectionString = ConfigurationManager.ConnectionStrings["BazaConnection"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add(new SqlParameter("@employeeID", SqlDbType.Int, 4));
cmd.Parameters["@employeeID"].Value = 66; // some value
cmd.CommandText = "Activate";
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
cmd.Connection = con;
int rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine("Rows affected: " + rowsAffected);
}
catch (Exception err)
{
Console.WriteLine(err.Message);
}
finally
{
con.Dispose();
con.Close();
}
</code>
After executing this code it says that 1 row has been affected but when
I browse contents of the Activations table there isn't any value. Everything
works fine when I execute stored procedure using Database Explorer in visual c# express edition.
Any idea what's wrong ?
I use sql server 2005 express edition
Thanks
--
Regards
tommy
|
|
|
|
|
The code looks good to me. You dont need to specify a length for sqldatatype int, but I don't know if this causes a problem in the proc. The only other thing i could think of is where you are looking for results in the db is not where the connection string is pointed to. Other than that I don't see anything wrong with it.
|
|
|
|
|
Hi,
yeah you got the point, the problem was with connection string.
In the beginning it was poiting to the file for example C:\baza.mdf
but when I added this file to my solution explorer, VC# simply copied it to my
project directory. Basically, I've been writing data to C:\baza.mdf but displaying them from file which has been in my project directory
thanks a lot!
tommy 
|
|
|
|
|
Hello,
I am using VS 2005 (.NET 2.0) and MS-SQL 2005. I am trying to run a stored procedure from an ASP.NET application that uses a stored procedure that is passed a couple of parameters that are to be returned from the stored procedure, a return value from the procedure, and a recordset. The procedure will return the parameters and the returned value if I don't try and return the recordset, but as soon as I try to return the recordset the parameters and the return values don't get set?
The procedure does work fine inside the MS-SQL Server Manager. So the problem does not appear to be in the procedure. Is there something extra that needs to be setup in calling this kind of procedure?
Thanks,
Leo
Leo T. Smith
|
|
|
|
|
Leo - the return values/parameters do get set, but you have to read through the entire recordset before they get populated. This (apparently) is by design.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks, I'd like to know the logic, but I guess someone has a reason.
Leo T. Smith
|
|
|
|
|
In case anyone is interested, there is a plugin that does word-completion in Sql Server Management Studio at the link below. It just went into beta yesterday so it is currently free.
www.roundpolygons.com[^]
~Steve
|
|
|
|
|
Or, there's always http://www.red-gate.com/products/SQL_Prompt/index.htm[^] which is also free.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Is it possible to query the table of a remote data server (MS SQL) in "query analyzer" without using a Linked Server? What are the items that I need to setup/know in order to do that?
For example:
Select
local.*,
Rmt.*
From
LocalServer.LocalDatabase.dbo.Table1 local,
RemoteServer.RemoteDatabase.dbo.Table2 Rmt
NOTE: Query analyzer current server is “Local”
-- modified at 11:52 Wednesday 13th December, 2006
|
|
|
|
|
Lookup OpenRowset or OpenDataSource in Sql Books online:
Here's and example
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
Using the GridView is like trying to explain to someone else how to move a third person's hands in order to tie your shoelaces for you.
-Chris Maunder
|
|
|
|
|
I spent 4 hours searching Google for this answer, you’re the man.
Thanks Andy, to your time and effort to answer my question.
-- modified at 20:57 Wednesday 13th December, 2006
|
|
|
|
|
If it is already a linked server it is a bit simplier.
select * from
[server].[database].[owner].[table]
In pratice this works out as
Select * from [OrderServer].[orderdb]..[orders]
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
I use sql server 7.0
I have 2 tables A and B.Both tables have 200000-300000 records.B has a column named changedate which has an index.I do a query.First I use Query Analyzer(QA)
declare @p_begin datetime,@p_end datetime
select @p_begin='2006-10-01 00:00:00.000',@p_end='2006-10-31 23:59:59.000'
select * from A inner join B on A.fno=B.fno WHERE B.changedate between @p_begin and @p_end
It uses more than 2 minutes .If I write
select * from A inner join B on A.fno=B.fno WHERE B.changedate
between '2006-10-01 00:00:00.000' and '2006-10-31 23:59:59.000'
It uses only 6 seconds .
Then I create a stored procedure to test
create procedure myproc1
AS
declare @p_begin datetime,@p_end datetime
select @p_begin='2006-10-01 00:00:00.000',@p_end='2006-10-31 23:59:59.000'
select * from A inner join B on A.fno=B.fno WHERE B.changedate between @p_begin and @p_end
go
create procedure myproc2 @p_begin datetime,@p_end datetime
AS
select * from A inner join B on A.fno=B.fno WHERE B.changedate between @p_begin and @p_end
go
When I exec myproc1, I use more than 2 minutes.
when I exec myproc2
declare @p_begin datetime,@p_end datetime
select @p_begin='2006-10-01 00:00:00.000',@p_end='2006-10-31 23:59:59.000'
exec myproc2 @p_begin,@p_end
It use only 6 seconds.
What is the difference behind them?
If I don't want to use stored procedure and want to query directly in QA,Can I use variable to achieve good performance?
please explain for me,thank you
|
|
|
|
|
You really need to run up Sql Profiler and profile the two to see what the difference is, and what is really going on. Theoretically, there should be no difference if all things are equal, but I would take a look at what Profiler thinks is going on.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,I find a url to explaint it.But I don't understand very much.
http://www.microsoft.com/technet/technetmag/issues/2005/05/SQLQA/
You may see this and explain for me better,thanks
|
|
|
|
|
It is possible that on the second run the SQL Server will have cached much of the data in memory that it needed to pull off the disk the first time round. But I'll reiterate the other piece of advice. Have a look to see what the SQL Server is actually doing with these queries.
|
|
|
|
|
I have a three columns in which one column is auto generated by increment 1. if i delete one record from the table (example:4 record i deleted)it should be updated as 5th record as 4th record and so on?
|
|
|
|
|
Ravi Shankar43 wrote: I have a three columns in which one column is auto generated by increment 1. if i delete one record from the table (example:4 record i deleted)it should be updated as 5th record as 4th record and so on?
No. If you delete rows the number that was autogenerated for that row will not be reused.
|
|
|
|
|
You can reseed the indentity if you are trying to hide something, but it could mess up your db schema
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
how to cancatenate two columns of data table in one to display as one column
please help me
diya
|
|
|
|
|
diya_dev wrote: how to cancatenate two columns of data table in one to display as one column
You can concatenate string based columns (e.g. varchar) like this:
SELECT Column1 + Column2
FROM MyTable
|
|
|
|
|
<br />
SELECT Column1 + " " + Column2<br />
FROM MyTable<br />
If you want to be "Really Fancy"
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Frank Kerrigan wrote: SELECT Column1 + " " + Column2
FROM MyTable
If you want to be "Really Fancy"
Strictly speaking that will attempt to concatenate 3 columns, the middle column having a name consisting of a single space character. I think you meant to use an apostrophe:
SELECT Column1 + ' ' + Column2
FROM MyTable
|
|
|
|
|
you will also getting concate column Name like "IDRATE"
select (id + rate) as IDRATE from test
cheeer
Pavan Pareta
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
