Executing SQL Server 2005 stored procedures on Windows 2003

I'm a new developer to both SQL Server 2005 & Windows 2003, so forgive me if this question seems a little too basic. I'm coming from a Oracle and UNIX background.

I've create a stored procedure in SQL Server 2005. I now want to execute this from the command line in Windows 2003. Eventually, I want our UNIX scheduler, autosys (which runs on a different UNIX machine obviously) to be able to execute this. In my old environment, I created a UNIX shell script as a wrapper let's say 123.sh. This shell script would accept as a parameter the name of the stored procedure I wanted to execute. If this stored procedure also had parameters it needed to be passed to it, I would have strung these values out in the command line in UNIX. Two examples of how the command line in UNIX I used to execute the Oracle stored procedure might look are listed below.

123.sh sp_my_stored_procedure input_parm1 input_parm2

123.sh sp_different_stored_procedure input_parm1

This way anytime I created a new stored procedure, I could reuse the shell script wrapper 123.sh and just pass in the name of the newly created stored procedure and any parameters it needed.

How can I accomplish this same type of functionality in the SQL Server 2005/Windows 2003 environment.

Thanks, Jim

You are looking for an oSql utility that will allow you to write command line SQL executions.

You can find it here

Adamus

|||

For SQL Server 2005, you really should use SQLCmd.exe (osql.exe is for backward compatibility.)

Documentation is available here.

|||If it help I heard about a book, SQL Server 2000 for the Oracle DBA .

Executing MAXL Scripts in Execute Process Task

Hello,

first of all a few words about our achitecture:

we have a windows 2003 server with sql sever 2005 and connect on this server with 3 clients (Visual Studio) on the Windows 2003 Server there is also an Hyperion Essbase Server installed.

In the Visual Studio I try to execute a batch script which is located on the server. But how i develop that? I have tried to rebuilt the servers File structure and save the Package on the Server. < didn't worked

My Question how can I make a package in which I execute a bat file which is located on the Server without developing it on the Server?

Thank you in advance!

Can you put the batch file in a share, so that you can use a UNC path to reference it? That will work from server or client.

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel B1) Locate the job (under SQL Agent)
2) Right click on the job
3) Pick View Job History...
4) Check the Show Job Details box
5) Read the error message(s)

-PatP|||This is the error message i get.

The job failed. The Job was invoked by User REPEATSEAT\dberezniak. The last step to run was step 1 (DTS Execute App).

This doesn't tell me much. It blows up when trying to execute the app.
( The application is test and does work on its own and within the package. )|||Did you click on the "Show Job Details" box to be sure that it is checked? That message sounds like what you'd get if that box wasn't checked.

-PatPsql

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel BThis is really a question for the Microsoft SQL Server (http://www.dbforums.com/f7) forum. I'd be happy to answer the question there!

-PatP

ExecuteNonQuery requires an open and available Connection

Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
message when trying to UPDATE a very large table:
"ExecuteNonQuery requires an open and available Connection. The connection's
current state is closed."
The UPDATE command affects all the rows in the column. The confusing part
is - it only occurs on a very large table (over 1M rows). It does not orrur
on smaller tables. So I don't think it actually has anything to do with the
connection.
Any ideas would be greatly appreciated.Just a guess, the connection might have timed out or broken. Try increasing
the timeout
"Neil W." wrote:

> Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
> message when trying to UPDATE a very large table:
> "ExecuteNonQuery requires an open and available Connection. The connection
's
> current state is closed."
> The UPDATE command affects all the rows in the column. The confusing part
> is - it only occurs on a very large table (over 1M rows). It does not orr
ur
> on smaller tables. So I don't think it actually has anything to do with t
he
> connection.
> Any ideas would be greatly appreciated.
>
>
>|||Neil W. (neilw@.NOSPAMTHANKYOUnetlib.com) writes:
> Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
> message when trying to UPDATE a very large table:
> "ExecuteNonQuery requires an open and available Connection. The
> connection's current state is closed."
> The UPDATE command affects all the rows in the column. The confusing
> part is - it only occurs on a very large table (over 1M rows). It does
> not orrur on smaller tables. So I don't think it actually has anything
> to do with the connection.
Might be so, but I would put my bets on the ADO .Net code. How does
the relevant bits look like.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Execute windows on client machine

Hello.

How to execute add users window, backup wizard and other windows from
Enterprice Manager on client machine? Does MDAC or sth is able to do this?

bye...

--
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +----------+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+Hi

It is not clear exactly what you are wishing, but at a guess you have EM on
the client machine but your server is not registered using an account that
is sufficiently privileged.

You may want to write your own interface that calls the appropriate stored
procedures.

John

"[BuKoX]" <bukox.wytnij@.tlen.pl> wrote in message
news:186574153.20041223193834@.tlen.pl...
> Hello.
> How to execute add users window, backup wizard and other windows from
> Enterprice Manager on client machine? Does MDAC or sth is able to do this?
> bye...
> --
> __ __
> |__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
> |__// |__|| | \\ \__// / \\ +----------+
> +[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+

Execute SSIS package at remote server across domain

Hi,

Is that possible to execute SSIS packages from a remote SQL Server 2005 in another domain at local computer (Windows XP)?

Can I use SQL authentication to connect to the remote SSIS? My problem is, the user who execute the SSIS is not allow to access to SQL server directly. Is that possible to code everything in a batch file instead?

I tried to use web services to execute the remote SSIS but it always return "authentication error"?

Can someone help?

Many thanks!

Regards,

Mashmallow

Going via SQL Server is the simplest. Remote execution of batch files is not really viable,a nd will certainly have issues cross domain, unless you have a trust. I assume not hence the question.

The Books Online link below covers such the SQL Agentand Web Service method, including notes on getting security to work, have you seen it?

Loading and Running a Remote Package Programmatically
(http://msdn2.microsoft.com/en-us/library/ms403355(SQL.90).aspx)

|||

Thanks Darren.

I have seen this link before and I have tried doesn't work. When I try to debug, it say "Authentication error". Do I need to use Window Authentication or SQL Authentication.

I try to use the dtexecui utility from my local machine to call the package from remote server. However, It doesn't allow SQL authentication. I am currently using VPN to dial in to client network before I am able to run the SSIS.

Regarding the link that you have sent to me. I follow the same code and I get result as below:-

1) use window authentication - I get the "Authentication Error" when execute the code

2) use SQL authentication - I do not get error when execute the code but it doesn't trigger the SSIS execution at remote server.

Please help. Many thanks.

Regards,

Mashmallow

execute permission for stored procedures

Hello SQL Experts,

we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.

A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.

The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.

In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?

The Owner of sp and table is dbo.

Thanks for your replies!You can't enable the user to only have permission to some of the data in the table. What you would need to do is have a view, and a column by which you would differentiate between different users. There is little cost associated with a view, and you can make the view owned by the user, so no one else can use it, or no one else can use it by mistake.

IE. [user].[viewname]

Cheers,
-Kilka|||While you can restrict access to columns using permissions, I don't know of any way to restrict access to rows using permissions.

I think that Kilka101 has the right idea about using a view. You may be able to construct a single view that uses User_Id() or Suser_Sname(), or you may need to resort to separate views for each user.

Keep in mind that as you scale upward, this gets a lot more complicated to manage, especially if you introduce any "third tier" processing like an application server. For two-tier applications this isn't likely to be a problem, but as you grow it can become a real problem.

-PatP|||Thanks for your replies!

I thought about a workaround with views, too. But I'm rather sure to remember that before Windows 2003 and SQL Sp3 it has been sufficient to grant a user the right to execute a procedure which performs a select on a table without having the explicit right to select from the table.

For example:

create proc sp_showusers
as
select * from users
where name= 'abc'
go

grant execute on sp_showusers to [my_user]
go

Though 'my_user' doesn't have granted the select permission on table users, he's got all data from table users by executing the sp.

I suppose it has something to do with the stricter security settings coming up with Windows 2003. Anyway, there must be a way out of it... :confused:|||Yes, when you create a stored procedure, the statements in it are checked against the security permissions of the creator. Permission to execute the procedure can be given to users with different permissions, that can then execute the procedure even though it does things such as your example select that the user couldn't do directly. Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

-PatP|||Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

Oh... okay, I guess this is exactly the point! The stored procedure I got the problems with contains some dynamic SQL... :( So I have to think about views then. ;)

Thank you very much for your help! :)

Execute permission denied on stored procedure

Hi,
A user of a VB.Net windows application gets this error message when
trying to add data. She is able to add data to other tables by
executing other stored procedures except in this 1 screen. I've checked
the database user/role that she is using and both have execute
permission for that stored procedure in error. Please help! I'm lost as
how to debug this problem.
Appreciate your inputs.
Thanks!Check to see if the use or role has DENY permissions. When both GRANT and
DENY permissions are present, DENY takes precedence. Execute sp_helprotect
'ProcName' to list all proc permissions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"hillcountry74" <shruthibg@.yahoo.com> wrote in message
news:1154708924.877916.202230@.m73g2000cwd.googlegroups.com...
> Hi,
> A user of a VB.Net windows application gets this error message when
> trying to add data. She is able to add data to other tables by
> executing other stored procedures except in this 1 screen. I've checked
> the database user/role that she is using and both have execute
> permission for that stored procedure in error. Please help! I'm lost as
> how to debug this problem.
> Appreciate your inputs.
> Thanks!
>

Execute permission denied on stored procedure

Hi,
A user of a VB.Net windows application gets this error message when
trying to add data. She is able to add data to other tables by
executing other stored procedures except in this 1 screen. I've checked
the database user/role that she is using and both have execute
permission for that stored procedure in error. Please help! I'm lost as
how to debug this problem.
Appreciate your inputs.
Thanks!Check to see if the use or role has DENY permissions. When both GRANT and
DENY permissions are present, DENY takes precedence. Execute sp_helprotect
'ProcName' to list all proc permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"hillcountry74" <shruthibg@.yahoo.com> wrote in message
news:1154708924.877916.202230@.m73g2000cwd.googlegroups.com...
> Hi,
> A user of a VB.Net windows application gets this error message when
> trying to add data. She is able to add data to other tables by
> executing other stored procedures except in this 1 screen. I've checked
> the database user/role that she is using and both have execute
> permission for that stored procedure in error. Please help! I'm lost as
> how to debug this problem.
> Appreciate your inputs.
> Thanks!
>

Execute permission denied on 'sp_xml_preparedocument'

Hello,
We have hosted our .NET application on a windows 2003 server runing SQL
Server 2000. We have a stored procedure that uses
"'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
is throwing the following error in one of the testing environment, but
working fine in the development environment, Any Ideas ?
Failed to update company details. Error message: EXECUTE permission
denied on object 'sp_xml_preparedocument',
database 'master', owner 'dbo'. Could not find prepared statement with
handle 0.
EXECUTE permission denied on object 'sp_xml_removedocument', database
'master', owner 'dbo'.
The statement has been terminated.
Thanks in Advance.
With Regards,
Sharath.Whoever you're logging in as in the test environment needs execute
permissions on the sp. You can easily check (and change) the permissions on
it in EM and compare to your other server. You can also change the
permissions through QA (GRANT statement).
"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
> Hello,
>
> We have hosted our .NET application on a windows 2003 server runing SQL
> Server 2000. We have a stored procedure that uses
> "'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
> is throwing the following error in one of the testing environment, but
> working fine in the development environment, Any Ideas ?
>
> Failed to update company details. Error message: EXECUTE permission denied
> on object 'sp_xml_preparedocument',
> database 'master', owner 'dbo'. Could not find prepared statement with
> handle 0.
> EXECUTE permission denied on object 'sp_xml_removedocument', database
> 'master', owner 'dbo'.
> The statement has been terminated.
>
> Thanks in Advance.
> With Regards,
> Sharath.|||Mike C# wrote:
> Whoever you're logging in as in the test environment needs execute
> permissions on the sp. You can easily check (and change) the permissions
on
> it in EM and compare to your other server. You can also change the
> permissions through QA (GRANT statement).
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
>
>
>
Thanks Mike for the reply. I am not well versed with SQL commands nor I
have access to the Server thru EM, If you could please send the script
(GRANT), that would be very helpful.|||"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Thanks Mike for the reply. I am not well versed with SQL commands nor I
> have access to the Server thru EM, If you could please send the script
> (GRANT), that would be very helpful.
USE master
GO
GRANT EXEC ON sp_xml_preparedocument TO public
GO
Of course you can change "public" above to another role/user. More
information is available in BOL:
http://msdn.microsoft.com/library/d...br />
8odw.asp|||Thank you Mike. I will try that.
Mike C# wrote:
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
>
> USE master
> GO
> GRANT EXEC ON sp_xml_preparedocument TO public
> GO
> Of course you can change "public" above to another role/user. More
> information is available in BOL:
> http://msdn.microsoft.com/library/d... />
z_8odw.asp
>

Execute permission denied on 'sp_xml_preparedocument'

Hello,
We have hosted our .NET application on a windows 2003 server runing SQL
Server 2000. We have a stored procedure that uses
"'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
is throwing the following error in one of the testing environment, but
working fine in the development environment, Any Ideas ?
Failed to update company details. Error message: EXECUTE permission
denied on object 'sp_xml_preparedocument',
database 'master', owner 'dbo'. Could not find prepared statement with
handle 0.
EXECUTE permission denied on object 'sp_xml_removedocument', database
'master', owner 'dbo'.
The statement has been terminated.
Thanks in Advance.
With Regards,
Sharath.
Whoever you're logging in as in the test environment needs execute
permissions on the sp. You can easily check (and change) the permissions on
it in EM and compare to your other server. You can also change the
permissions through QA (GRANT statement).
"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
> Hello,
>
> We have hosted our .NET application on a windows 2003 server runing SQL
> Server 2000. We have a stored procedure that uses
> "'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
> is throwing the following error in one of the testing environment, but
> working fine in the development environment, Any Ideas ?
>
> Failed to update company details. Error message: EXECUTE permission denied
> on object 'sp_xml_preparedocument',
> database 'master', owner 'dbo'. Could not find prepared statement with
> handle 0.
> EXECUTE permission denied on object 'sp_xml_removedocument', database
> 'master', owner 'dbo'.
> The statement has been terminated.
>
> Thanks in Advance.
> With Regards,
> Sharath.
|||Mike C# wrote:
> Whoever you're logging in as in the test environment needs execute
> permissions on the sp. You can easily check (and change) the permissions on
> it in EM and compare to your other server. You can also change the
> permissions through QA (GRANT statement).
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
>
>
Thanks Mike for the reply. I am not well versed with SQL commands nor I
have access to the Server thru EM, If you could please send the script
(GRANT), that would be very helpful.
|||"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Thanks Mike for the reply. I am not well versed with SQL commands nor I
> have access to the Server thru EM, If you could please send the script
> (GRANT), that would be very helpful.
USE master
GO
GRANT EXEC ON sp_xml_preparedocument TO public
GO
Of course you can change "public" above to another role/user. More
information is available in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
|||Thank you Mike. I will try that.
Mike C# wrote:
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
> USE master
> GO
> GRANT EXEC ON sp_xml_preparedocument TO public
> GO
> Of course you can change "public" above to another role/user. More
> information is available in BOL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
>

Execute permission denied on 'sp_xml_preparedocument'

Hello,
We have hosted our .NET application on a windows 2003 server runing SQL
Server 2000. We have a stored procedure that uses
"'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
is throwing the following error in one of the testing environment, but
working fine in the development environment, Any Ideas ?
Failed to update company details. Error message: EXECUTE permission
denied on object 'sp_xml_preparedocument',
database 'master', owner 'dbo'. Could not find prepared statement with
handle 0.
EXECUTE permission denied on object 'sp_xml_removedocument', database
'master', owner 'dbo'.
The statement has been terminated.
Thanks in Advance.
With Regards,
Sharath.Whoever you're logging in as in the test environment needs execute
permissions on the sp. You can easily check (and change) the permissions on
it in EM and compare to your other server. You can also change the
permissions through QA (GRANT statement).
"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
> Hello,
>
> We have hosted our .NET application on a windows 2003 server runing SQL
> Server 2000. We have a stored procedure that uses
> "'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
> is throwing the following error in one of the testing environment, but
> working fine in the development environment, Any Ideas ?
>
> Failed to update company details. Error message: EXECUTE permission denied
> on object 'sp_xml_preparedocument',
> database 'master', owner 'dbo'. Could not find prepared statement with
> handle 0.
> EXECUTE permission denied on object 'sp_xml_removedocument', database
> 'master', owner 'dbo'.
> The statement has been terminated.
>
> Thanks in Advance.
> With Regards,
> Sharath.|||Mike C# wrote:
> Whoever you're logging in as in the test environment needs execute
> permissions on the sp. You can easily check (and change) the permissions on
> it in EM and compare to your other server. You can also change the
> permissions through QA (GRANT statement).
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
>>Hello,
>>
>>We have hosted our .NET application on a windows 2003 server runing SQL
>>Server 2000. We have a stored procedure that uses
>>"'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
>>is throwing the following error in one of the testing environment, but
>>working fine in the development environment, Any Ideas ?
>>
>>Failed to update company details. Error message: EXECUTE permission denied
>>on object 'sp_xml_preparedocument',
>>database 'master', owner 'dbo'. Could not find prepared statement with
>>handle 0.
>>EXECUTE permission denied on object 'sp_xml_removedocument', database
>>'master', owner 'dbo'.
>>The statement has been terminated.
>>
>>Thanks in Advance.
>>With Regards,
>>Sharath.
>
>
Thanks Mike for the reply. I am not well versed with SQL commands nor I
have access to the Server thru EM, If you could please send the script
(GRANT), that would be very helpful.|||"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Thanks Mike for the reply. I am not well versed with SQL commands nor I
> have access to the Server thru EM, If you could please send the script
> (GRANT), that would be very helpful.
USE master
GO
GRANT EXEC ON sp_xml_preparedocument TO public
GO
Of course you can change "public" above to another role/user. More
information is available in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp|||Thank you Mike. I will try that. :)
Mike C# wrote:
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
>>Thanks Mike for the reply. I am not well versed with SQL commands nor I
>>have access to the Server thru EM, If you could please send the script
>>(GRANT), that would be very helpful.
>
> USE master
> GO
> GRANT EXEC ON sp_xml_preparedocument TO public
> GO
> Of course you can change "public" above to another role/user. More
> information is available in BOL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
>