Execute Scalar ?

I'm trying to do something like the code below, but it's saying "specified cast is not valid"

If i change the value returned to an "int", it works fine. My issue is, i'd like to get the value returned with more accuracy than an int as there will be 2 decimal places.

protectedfloat getProjectHours(string project)

{

string selectCmd ="SELECT SUM(hours) FROM tasks WHERE project=@.project";

string strConnection =ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;

SqlConnection myConnection =newSqlConnection(strConnection);

SqlCommand myCommand =newSqlCommand(selectCmd, myConnection);

myCommand.Parameters.Add(newSqlParameter("@.project",SqlDbType.VarChar));myCommand.Parameters["@.project"].Value = project;

myConnection.Open();

float total = (float)myCommand.ExecuteScalar();

myConnection.Close();

return total;

}

Could you post the sql function please?

|||

what do mean? it's all up there

|||

If this is the line you are getting an error on, then try this:

double total = Convert.ToDouble(myCommand.ExecuteScalar());

|||

worked perfect! Thanks!

|||

you are welcome...

|||

Oh, sorry. Need more coffee...

Execute permission for all sprocs

I am looking to write a script that will grant a specified user execute
access to all stored procedures, but no other permissions
Any help would be appreciated,
Craig"Craig HB" schrieb:
> I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig
Just add the name of the user account in the second line and run this:
declare @.user varchar(256)
set @.user = 'The name of the user account'
declare @.sp varchar(256)
declare cu cursor fast_forward for
select [name] from sysobjects where [xtype] = 'P'
open cu
fetch next from cu into @.sp
while @.@.fetch_status = 0
begin
execute('grant execute on [' + @.sp + '] to ' + @.user)
fetch next from cu into @.sp
end
close cu deallocate cu|||sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A903026B-14A6-40F0-9FB0-52906A69026C@.microsoft.com...
>I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig

execute DTS from asp.net 2.0 - package does not exist error

I am trying to execute a dts from asp.net, but getting an error

“The specified DTS Package ('Name = 'DTS_USERS_LIST'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist.”

I know I have saved the dts package on the sqlserver, and have run it from the server successfully.

Here is my code:

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(

sServer,

sUID,

sPWD,

//DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,

DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,

null,

null,

null,

sPkgName,

ref pVarPersistStgOfHost);

package.Execute();

package.UnInitialize();

// force Release() on COM object

//

System.Runtime.InteropServices.Marshal.ReleaseComObject(package);

package = null;

any inputs would be aprpeciated.

I see this is a DTS 2000 issue. This forum only covers SSIS in SQL Server 2005.

Have a look at this article

http://www.c-sharpcorner.com/Code/2002/Sept/DTSnCS.asp

- see if it has anything useful for you. Otherwise, you may want to look at www.sqldts.com and the forums there.

Donald Farmer

|||Hello Donald:
This is indeed an SSIS package issue. The SSIS package was saved in mssql 2005 server. The code above is in asp.net 2.0. Using the word DTS is I guess an old habit.

Your response for be appreciated.

thx
|||

But it looks like you are using DTS code here. The interfaces you are listing - and the resultant error message are DTS interfaces and erros, not SSIS.

For SSIS execution, have a look at http://msdn2.microsoft.com/zh-cn/library/ms136090.aspx

hth

Donald Farmer

|||

I had my machine switched to a Chinese locale for some (much needed) practice and pasted a link to Chinese books-online.

Here is the US English link: http://msdn2.microsoft.com/en-us/library/ms136090.aspx

对不起

Donald Farmer

|||thank you for the help. I now have been able to run SSIS stored in MSDB by using stored procedure by following the sql agent method. Here is the sp that i used

exec master..xp_cmdshell 'DTEXEC /U sa /P xxx /Ser servername /SQ "DTS_USERS_LIST"/de ""'

I now want to add an input parameter to the sp and pass that parameter onto the SSIS. I know how to pass the parameter from asp.net to the sp, but cannot figure out how to pass the parameter to the SSIS on the sp. I figure i need to append it somehow to the dtexec code above. Can you point me to the right direction?

A second question, how do I modify the SSIS that is stored in MSDB?

thx again,

|||

There is a much better way of executing a package using SQL Agent. See http://msdn2.microsoft.com/en-us/library/ms139805(SQL.90).aspx

xp_cmdshell is off by default for good reasons - we do not recommend using it, as it opens up a significant attack vector for security.

SSIS packages in MSDB are deployed packages: you should still have a design-time version available in BIDS.

See http://msdn2.microsoft.com/en-us/library/ms137633.aspx and http://msdn2.microsoft.com/en-us/library/ms141772.aspx

In 2005, there is a clear distinction between the development environment (Business Intelligence Development Studio) and the deployed / admin environment (SQL Server Management Studio). We support a true "develop and test -> deploy to production -> administer" methodology.

In other words, rather than editing the version in MSDB, you would edit the version you have in your development environment, and redeploy the new version to your server (after thorough testing, of course.)

Donald

execute DTS from asp.net 2.0 - package does not exist error

I am trying to execute a dts from asp.net, but getting an error

“The specified DTS Package ('Name = 'DTS_USERS_LIST'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist.”

I know I have saved the dts package on the sqlserver, and have run it from the server successfully.

Here is my code:

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(

sServer,

sUID,

sPWD,

//DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,

DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,

null,

null,

null,

sPkgName,

ref pVarPersistStgOfHost);

package.Execute();

package.UnInitialize();

// force Release() on COM object

//

System.Runtime.InteropServices.Marshal.ReleaseComObject(package);

package = null;

any inputs would be aprpeciated.

I see this is a DTS 2000 issue. This forum only covers SSIS in SQL Server 2005.

Have a look at this article

http://www.c-sharpcorner.com/Code/2002/Sept/DTSnCS.asp

- see if it has anything useful for you. Otherwise, you may want to look at www.sqldts.com and the forums there.

Donald Farmer

|||Hello Donald:
This is indeed an SSIS package issue. The SSIS package was saved in mssql 2005 server. The code above is in asp.net 2.0. Using the word DTS is I guess an old habit.

Your response for be appreciated.

thx
|||

But it looks like you are using DTS code here. The interfaces you are listing - and the resultant error message are DTS interfaces and erros, not SSIS.

For SSIS execution, have a look at http://msdn2.microsoft.com/zh-cn/library/ms136090.aspx

hth

Donald Farmer

|||

I had my machine switched to a Chinese locale for some (much needed) practice and pasted a link to Chinese books-online.

Here is the US English link: http://msdn2.microsoft.com/en-us/library/ms136090.aspx

对不起

Donald Farmer

|||thank you for the help. I now have been able to run SSIS stored in MSDB by using stored procedure by following the sql agent method. Here is the sp that i used

exec master..xp_cmdshell 'DTEXEC /U sa /P xxx /Ser servername /SQ "DTS_USERS_LIST"/de ""'

I now want to add an input parameter to the sp and pass that parameter onto the SSIS. I know how to pass the parameter from asp.net to the sp, but cannot figure out how to pass the parameter to the SSIS on the sp. I figure i need to append it somehow to the dtexec code above. Can you point me to the right direction?

A second question, how do I modify the SSIS that is stored in MSDB?

thx again,

|||

There is a much better way of executing a package using SQL Agent. See http://msdn2.microsoft.com/en-us/library/ms139805(SQL.90).aspx

xp_cmdshell is off by default for good reasons - we do not recommend using it, as it opens up a significant attack vector for security.

SSIS packages in MSDB are deployed packages: you should still have a design-time version available in BIDS.

See http://msdn2.microsoft.com/en-us/library/ms137633.aspx and http://msdn2.microsoft.com/en-us/library/ms141772.aspx

In 2005, there is a clear distinction between the development environment (Business Intelligence Development Studio) and the deployed / admin environment (SQL Server Management Studio). We support a true "develop and test -> deploy to production -> administer" methodology.

In other words, rather than editing the version in MSDB, you would edit the version you have in your development environment, and redeploy the new version to your server (after thorough testing, of course.)

Donald