how do i get the OUTPUT parameter value from a stored procedure that i am using to fill a

how do i get the OUTPUT parameter value from a stored procedure that i am using to fill a dataset?

Set the Direction property of your Parameter.

Dim myParamAs New SqlParameter' ... set value, name, etc.' if you are sending a value (Default)myParam.Direction = ParameterDirection.Input' if you are requesting a value (assigned in SQL)myParam.Direction = ParameterDirection.Output' if you are sending a value AND requesting' it's changed valuemyParam.Direction = ParameterDirectioin.InputOutput' If you want the return code for the SQLmyParam.Direction = ParameterDirection.ReturnValue

|||

no that doesn't work. i know how to use parameters, but i have only ever seen the Output Parameter used withcommand.executenonquery()when inserting data, but this is no good to me because I am only usingdataAdapter.Fill(myDataset)

how do i get the OutPut Parameter with only filling a dataset?

|||

Theoretically, after your call todataAdapter.Fill(myDataset)

the out parameter should be accessible using

dataAdapter.SelectCommand.Parameters("paramname")

I haven't tried it out though.

|||

Yes, this should work. I believe everything is copied by reference, though, when you add a parameter to a command, then a command to an adapter. Thus, you should still be able to use the original parameter.

'after dataadapter.fill()Dim strValueAs String = myParam.Value

|||I posted some sample code here. Check if it helps:http://forums.asp.net/thread/1478830.aspx|||thanks that works

How do I get it to a precisions scale of .00?

I have set the output columns to decimal and data scale of 2. And have also set the field to be 0.00, and in the csv desination file it always puts .000000, How can I get it to be 0.00?

Thanks you for the help

Try creating a derived column cast to DT_NUMERIC and scale of 2 between your data source and destination. That should allow you to get 2 decimal places for any numeric input type. You can add all the columns you want to do this to in the one derived column task.

How do I get a procedure OUTPUT-parameter...

In my ASP.NET page I use a stored procedure that have a parameter declared as OUTPUT...
however...I do not know how to get this OUTPUT to be stored in a ASP.NET-variable...

this is the sp:

CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier OUTPUT,
@.strHeading nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400)
AS
SET @.uidArticleId = newid()
INSERT INTO tblArticles
VALUES(@.uidArticleId ,@.strHeading,@.strAbstract,@.strText,@.dtDate,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAuthorEmail,@.strKeywords)

my asp code is something like this:

...
SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon);

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strHeading", SqlDbType.NVarChar, 300));
sqlcmdInsertNewsArticle.Parameters["@.strHeading"].Value = strHeading.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strAbstract", SqlDbType.NVarChar, 600));
sqlcmdInsertNewsArticle.Parameters["@.strAbstract"].Value = strAbstract.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strText", SqlDbType.NVarChar, 4000));
sqlcmdInsertNewsArticle.Parameters["@.strText"].Value = strText.Text;

...

sqlcmdInsertNewsArticle.Connection.Open();
sqlcmdInsertNewsArticle.ExecuteNonQuery();
sqlcmdInsertNewsArticle.Connection.Close();

How do I do if I want to catch the OUTPUT-parameter (@.uidArticleId)?

anyone?Start by defining another parameter in the Parameters collection:

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.uidArticleId", SqlDbType.UniqueIdentifier));
sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Direction = ParameterDirection.Output;

Then, after you call ExecuteNonQuery, grab the value of that parameter:

Dim id as GUID = sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Value;

I didn't test this code so you may need to tweak it, but that's the idea.

Don|||thanks... that would probably work if I just could get the %#&¤ connection to work... :)

(see: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=553656 )

How do I find I am administrator?

Hi Sunny,
1.What is that means. I do not have admin rights to whole server?
Based on the output you fell inside the 'db_owner' role in your database.
This ensure that you can do any activiites inside that database.
But you not in the part of 'SYSADMIN' role, which is the server wide role.
Due to that your SP_HELPLOGINS failed. This command will list the details of
all Logins who can access SQL server.
2.What is that means. I do not have admin rights to whole server?
Yes, You have full access to only ur database.
3.Is package admin is different than server adminn?
Yes, While saving the package you can mention a Owner password. That might
be needed while accessing the exiting package.
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:#CzPhl4GEHA.3772@.TK2MSFTNGP12.phx.gbl...
SQL
> I selected MyTestDB and ran the store proc and it returns 3 records,
> extracts from the query results:
> GroupName DefDBName
> db_owner master
> db_accessadmin master
> db_ddladmin master
> I believe I have admin rights to MyTestDb.
> Then I selected master database and ran same sp. I got message "User does
> not have permission to perform this action."
> What is that means. I do not have admin rights to whole server?
> the
> Is package admin is different than server adminn?
> Thank you very much for providing detail information.
> SQL
installed
> admin
> change
> any
manager
> on
>
Thanks Hari.
Since I am db_owner, why I am not able to create new store procedure, view
and table? It gives me error "CREATE TABLE permission denied in database
'myTestDB'.
Your suggestion would be great help.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi Sunny,
> 1.What is that means. I do not have admin rights to whole server?
> Based on the output you fell inside the 'db_owner' role in your database.
> This ensure that you can do any activiites inside that database.
> But you not in the part of 'SYSADMIN' role, which is the server wide
role.
> Due to that your SP_HELPLOGINS failed. This command will list the details
of
> all Logins who can access SQL server.
> 2.What is that means. I do not have admin rights to whole server?
> Yes, You have full access to only ur database.
> 3.Is package admin is different than server adminn?
> Yes, While saving the package you can mention a Owner password. That might
> be needed while accessing the exiting package.
> Thanks
> Hari
> MCDBA
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
am
> SQL
execute
does
knowwing
good
of
> installed
there
> manager
>
|||Hi Sunny,
It seems, the Administrator has denied access to "Create table" , "Create
Procedure" and "Create View" for your SQL server user using the
DENY statement;
deny create table to <user>
go
deny create procedure to <user>
go
deny create view to <user>
In this case even if you are db_owner for a database you will not able to do
Create table / Create View or Create Procedure.
If required ask your administrator to Grant back those previlages using
GRANT statement.
The below previlages can be denied from a DB_OWNER by administartor;
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:eiDTikBHEHA.324@.tk2msftngp13.phx.gbl...
> Thanks Hari.
> Since I am db_owner, why I am not able to create new store procedure, view
> and table? It gives me error "CREATE TABLE permission denied in database
> 'myTestDB'.
> Your suggestion would be great help.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
database.
> role.
details
> of
might
I
> am
access
> execute
> does
> knowwing
> good
> of
in
> there
>

How do I find I am administrator?

Hi Sunny,
1.What is that means. I do not have admin rights to whole server?
Based on the output you fell inside the 'db_owner' role in your database.
This ensure that you can do any activiites inside that database.
But you not in the part of 'SYSADMIN' role, which is the server wide role.
Due to that your SP_HELPLOGINS failed. This command will list the details of
all Logins who can access SQL server.
2.What is that means. I do not have admin rights to whole server?
Yes, You have full access to only ur database.
3.Is package admin is different than server adminn?
Yes, While saving the package you can mention a Owner password. That might
be needed while accessing the exiting package.
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:#CzPhl4GEHA.3772@.TK2MSFTNGP12.phx.gbl...
SQL
> I selected MyTestDB and ran the store proc and it returns 3 records,
> extracts from the query results:
> GroupName DefDBName
> db_owner master
> db_accessadmin master
> db_ddladmin master
> I believe I have admin rights to MyTestDb.
> Then I selected master database and ran same sp. I got message "User does
> not have permission to perform this action."
> What is that means. I do not have admin rights to whole server?
> the
> Is package admin is different than server adminn?
> Thank you very much for providing detail information.
> SQL
installed
> admin
> change
> any
manager
> on
>
Thanks Hari.
Since I am db_owner, why I am not able to create new store procedure, view
and table? It gives me error "CREATE TABLE permission denied in database
'myTestDB'.
Your suggestion would be great help.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi Sunny,
> 1.What is that means. I do not have admin rights to whole server?
> Based on the output you fell inside the 'db_owner' role in your database.
> This ensure that you can do any activiites inside that database.
> But you not in the part of 'SYSADMIN' role, which is the server wide
role.
> Due to that your SP_HELPLOGINS failed. This command will list the details
of
> all Logins who can access SQL server.
> 2.What is that means. I do not have admin rights to whole server?
> Yes, You have full access to only ur database.
> 3.Is package admin is different than server adminn?
> Yes, While saving the package you can mention a Owner password. That might
> be needed while accessing the exiting package.
> Thanks
> Hari
> MCDBA
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
am
> SQL
execute
does
knowwing
good
of
> installed
there
> manager
>
|||Hi Sunny,
It seems, the Administrator has denied access to "Create table" , "Create
Procedure" and "Create View" for your SQL server user using the
DENY statement;
deny create table to <user>
go
deny create procedure to <user>
go
deny create view to <user>
In this case even if you are db_owner for a database you will not able to do
Create table / Create View or Create Procedure.
If required ask your administrator to Grant back those previlages using
GRANT statement.
The below previlages can be denied from a DB_OWNER by administartor;
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:eiDTikBHEHA.324@.tk2msftngp13.phx.gbl...
> Thanks Hari.
> Since I am db_owner, why I am not able to create new store procedure, view
> and table? It gives me error "CREATE TABLE permission denied in database
> 'myTestDB'.
> Your suggestion would be great help.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
database.
> role.
details
> of
might
I
> am
access
> execute
> does
> knowwing
> good
> of
in
> there
>

How do I find I am administrator?

Hi Sunny,
1.What is that means. I do not have admin rights to whole server?
Based on the output you fell inside the 'db_owner' role in your database.
This ensure that you can do any activiites inside that database.
But you not in the part of 'SYSADMIN' role, which is the server wide role.
Due to that your SP_HELPLOGINS failed. This command will list the details of
all Logins who can access SQL server.
2.What is that means. I do not have admin rights to whole server?
Yes, You have full access to only ur database.
3.Is package admin is different than server adminn?
Yes, While saving the package you can mention a Owner password. That might
be needed while accessing the exiting package.
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:#CzPhl4GEHA.3772@.TK2MSFTNGP12.phx.gbl...
SQL
> I selected MyTestDB and ran the store proc and it returns 3 records,
> extracts from the query results:
> GroupName DefDBName
> db_owner master
> db_accessadmin master
> db_ddladmin master
> I believe I have admin rights to MyTestDb.
> Then I selected master database and ran same sp. I got message "User does
> not have permission to perform this action."
> What is that means. I do not have admin rights to whole server?
> the
> Is package admin is different than server adminn?
> Thank you very much for providing detail information.
> SQL
installed
> admin
> change
> any
manager
> on
>
Thanks Hari.
Since I am db_owner, why I am not able to create new store procedure, view
and table? It gives me error "CREATE TABLE permission denied in database
'myTestDB'.
Your suggestion would be great help.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi Sunny,
> 1.What is that means. I do not have admin rights to whole server?
> Based on the output you fell inside the 'db_owner' role in your database.
> This ensure that you can do any activiites inside that database.
> But you not in the part of 'SYSADMIN' role, which is the server wide
role.
> Due to that your SP_HELPLOGINS failed. This command will list the details
of
> all Logins who can access SQL server.
> 2.What is that means. I do not have admin rights to whole server?
> Yes, You have full access to only ur database.
> 3.Is package admin is different than server adminn?
> Yes, While saving the package you can mention a Owner password. That might
> be needed while accessing the exiting package.
> Thanks
> Hari
> MCDBA
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
am
> SQL
execute
does
knowwing
good
of
> installed
there
> manager
>
|||Hi Sunny,
It seems, the Administrator has denied access to "Create table" , "Create
Procedure" and "Create View" for your SQL server user using the
DENY statement;
deny create table to <user>
go
deny create procedure to <user>
go
deny create view to <user>
In this case even if you are db_owner for a database you will not able to do
Create table / Create View or Create Procedure.
If required ask your administrator to Grant back those previlages using
GRANT statement.
The below previlages can be denied from a DB_OWNER by administartor;
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:eiDTikBHEHA.324@.tk2msftngp13.phx.gbl...
> Thanks Hari.
> Since I am db_owner, why I am not able to create new store procedure, view
> and table? It gives me error "CREATE TABLE permission denied in database
> 'myTestDB'.
> Your suggestion would be great help.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
database.
> role.
details
> of
might
I
> am
access
> execute
> does
> knowwing
> good
> of
in
> there
>