get a return value from an insert without using a stored proc.

hi all,

lets say i have this insert command being executed from C# to a SQL Db.

//store transaction logSqlCommand cmdStoreTrans =new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@.ImportID,@.ProfileID,@.RowID);",conn);cmdStoreTrans.Parameters.Add("@.ImportID",importId);cmdStoreTrans.Parameters.Add("@.ProfileID",profileId);cmdStoreTrans.Parameters.Add("@.RowID",i);try{conn.Open();cmdStoreTrans.ExecuteNonQuery();conn.Close();}catch(SqlException ex){throw(ex);}

I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @.@.Identity

any help appreciated

TIA, mcm

Try doing a SELECT SCOPE_IDENTITY() at the end of your INSERT statement. and use ExecuteScalar instead of ExecuteNonQuery.|||

thanks,

will do.

mcm

Get @@rowcount data from MSSQL using SqlDataSource with delete command

Hi

I'm using a simple SqlDataSource to connect to a stored proc to delete a number of rows from different tables.

In my SQL im using:

DECLARE @.table1CountintDELETE FROM Table1WHERE id = @.new_idSET @.table1Count=@.@.rowcountSELECT @.table1Count

I'm then using an input box and linking it to the delete control parameter. Then on a button click event i'm running SqlDataSource1.Delete() which all works fine. But how do i get the @.table1Count back into my aspx page?

Thanks

use OUT parameter

CREATE PROC dbo.SP_DeleteID(@.new_id int, @.effRowCnt intOUT)

As

begin

DELETE FROM Table1WHERE id = @.new_id

SET @.effRowCnt=@.@.rowcount

end

|||

ok, but how do i reference the @.effRowCnt in the code behind page?

thanks

|||

If you are just trying to get a count of the number of rows deleted, an easier way to do it is to use the Deleted methodof the DataSource, something like:

ProtectedSub SQLDataSource1_Deleted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SQLDataSource1.Deleted

NumberofRowsDeleted = e.AffectedRows

EndSub

HTH

generating database schema thru command line

In informix there is a tool called dbschema which can generate a sql file
to create a database fully. I am aware that in the Enterprise Manager one
can do it easily. However i need the same functionality via command line
so that we can plug the command in the build tool.
TIAEM does it using sql-dmo, take a look a this api.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_g_11rm.asp
AMB
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||You can also invoke the functionality of the EM wizards using SQL-NS API.
Look for SQL-NS constant SQLNS_CmdID_GENERATE_SCRIPTS and SQL-NS method
ExecuteCommandByID.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlns/ns_ref_78z5.asp
AMB
"Alejandro Mesa" wrote:
> EM does it using sql-dmo, take a look a this api.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_g_11rm.asp
>
> AMB
> "rkusenet" wrote:
> > In informix there is a tool called dbschema which can generate a sql file
> > to create a database fully. I am aware that in the Enterprise Manager one
> > can do it easily. However i need the same functionality via command line
> > so that we can plug the command in the build tool.
> >
> > TIA
> >|||I've listed some options here:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rkusenet" <usenet.rk@.gmail.com> wrote in message news:3d9nmvF6rhk35U1@.individual.net...
> In informix there is a tool called dbschema which can generate a sql file to create a database
> fully. I am aware that in the Enterprise Manager one can do it easily. However i need the same
> functionality via command line
> so that we can plug the command in the build tool.
> TIA|||Tibor,
Does SCPTXFR.EXE come with SQL Server 2000 also?
AMB
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||you talked about doing a build, I'm wondering if this is part of your daily
or at least scheduled build process?
You may want to save yourself all the time and effort of re-inventing the
wheel...
http://www.dbghost.com
Build, compare, deploy.
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||> Does SCPTXFR.EXE come with SQL Server 2000 also?
Seems so. In the upgrade directory :-).
C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:649EF740-EF67-4618-A363-B765823989DC@.microsoft.com...
> Tibor,
> Does SCPTXFR.EXE come with SQL Server 2000 also?
>
> AMB
> "rkusenet" wrote:
>> In informix there is a tool called dbschema which can generate a sql file
>> to create a database fully. I am aware that in the Enterprise Manager one
>> can do it easily. However i need the same functionality via command line
>> so that we can plug the command in the build tool.
>> TIA|||Thanks!!!
"Tibor Karaszi" wrote:
> > Does SCPTXFR.EXE come with SQL Server 2000 also?
> Seems so. In the upgrade directory :-).
> C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:649EF740-EF67-4618-A363-B765823989DC@.microsoft.com...
> > Tibor,
> >
> > Does SCPTXFR.EXE come with SQL Server 2000 also?
> >
> >
> > AMB
> >
> > "rkusenet" wrote:
> >
> >> In informix there is a tool called dbschema which can generate a sql file
> >> to create a database fully. I am aware that in the Enterprise Manager one
> >> can do it easily. However i need the same functionality via command line
> >> so that we can plug the command in the build tool.
> >>
> >> TIA
> >>
>
>

Generating a flat file output from a select

I want to create a script file to executed from the command line. The script will contain a simple select, which depending on which database it is run against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when the script is executed from the command line it will produce a flat file with a list of the users on the database.
http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.
|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generating a flat file output from a select

I want to create a script file to executed from the command line. The scrip
t will contain a simple select, which depending on which database it is run
against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when
the script is executed from the command line it will produce a flat file wit
h a list of the users on the database.http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.