Executing SP_SpaceUsed for tables in a other database with EXEC

Hi

I'm executing SP_SpaceUsed in a stored procedure like this :

Exec ('SP_SpaceUsed '+ @.table)

This works great but when i want to execute it for a table in a other
database i'm running in to troubles. Things i tried is this :

Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
syntax)

Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
(uncorrect syntax)

Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
syntax)

Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
(uncorrect syntax)

Could someone give me a clue

Thanx,

Hennie de Nooijerhdenooijer@.hotmail.com (Hennie de Nooijer) wrote in message news:<191115aa.0311040343.674519a6@.posting.google.com>...
> Hi
> I'm executing SP_SpaceUsed in a stored procedure like this :
> Exec ('SP_SpaceUsed '+ @.table)
> This works great but when i want to execute it for a table in a other
> database i'm running in to troubles. Things i tried is this :
> Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
> syntax)
> Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
> (uncorrect syntax)
> Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
> syntax)
> Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
> (uncorrect syntax)
> Could someone give me a clue
>
> Thanx,
> Hennie de Nooijer

exec('exec ' + @.database + '..sp_spaceused ' + @.table)

Simon

executing sp

Hi
I have sp in which i run dbcc inputbuffer.
Can I, and how grant permissions to somebody who
is not in sysadmin fixed server role to execute this sp?Hi,
No it is not possible.
DBCC INPUTBUFFER permissions default to members of the Sysadmin fixed server
role only, who can see any SPID. Other users can see any SPID they own.
Permissions are not transferable.
Thanks
Hari
MCDBA
<roman.ilic@.avtenta.si> wrote in message
news:efd1361.0403160208.7a8a3d9b@.posting.google.com...
> Hi
> I have sp in which i run dbcc inputbuffer.
> Can I, and how grant permissions to somebody who
> is not in sysadmin fixed server role to execute this sp?sql

Executing package from C# on 64bit platform

Hi

I have been successfully executing a series of IS packages from C# using the following syntax

Microsoft.SqlServer.Dts.Runtime.Package package;
package = app.LoadPackage(ISPackagePath + @."\myPackage.dtsx", null);
package.ImportConfigurationFile(ISPackagePath + @."\MyConfiguration.dtsConfig");
result = package.Execute();
package.Dispose();

My client has just moved their SQL boxes to a 64bit platform and this process no longer runs.

Now im also running some packages in a SQL Agent job on the SQL box and im aware of the issue of an SSIS job step defaulting to using the 64bit version of DTExec.exe. Ive updated those job steps to be operating system (cmdExec) type steps calling the 32bit version of DTExec.exe, and everything is fine there.

Is there anyway to force the Microsoft.SqlServer.Dts.Runtime.Package object in my C# code to use the 32bit version of DTEexec.exe? This code is running on the same 64bit SQL server box as the SQL agent job. Or will Microsoft.SqlServer.Dts.Runtime.Package object always default to using the 64 bit version if it is executed on a 64bit box ?

I would just call sp_start_job from C# and implement the IS packages in another job but unfortunatly my code cannot be given correct permissions to see the SQL agent jobs.

Any ideas?
Thanks

In a mixed platform environment you have to compile your code with AnyCPU instead of 32bits so just open your solutions in VS2005 and rebuild your code with AnyCPU and it will run in 64bits box. There maybe some configuration relevant to SSIS but in most mixed platform build with AnyCPU should fix it. I develop C# .NET applications with Oracle 9i/10g both 64bits with AnyCPU without any problems. Hope this helps.

|||

Caddre wrote:

I want to know about the performance using oracle packages in c#.net

Bhavin.

Bhavin_82@.hotmail.com

sql

Executing package from C#

Hi

I have been successfully executing a series of IS packages from C# using the following syntax

Microsoft.SqlServer.Dts.Runtime.Package package;
package = app.LoadPackage(ISPackagePath + @."\myPackage.dtsx", null);
package.ImportConfigurationFile(ISPackagePath + @."\MyConfiguration.dtsConfig");
result = package.Execute();
package.Dispose();

My client has just moved their SQL boxes to a 64bit platform and this process no longer runs.

Now im also running some packages in a SQL Agent job on the SQL box and im aware of the issue of an SSIS job step defaulting to using the 64bit version of DTExec.exe. Ive updated those job steps to be operating system (cmdExec) type steps calling the 32bit version of DTExec.exe, and everything is fine there.

Is there anyway to force the Microsoft.SqlServer.Dts.Runtime.Package object in my C# code to use the 32bit version of DTEexec.exe? This code is running on the same 64bit SQL server box as the SQL agent job. Or will Microsoft.SqlServer.Dts.Runtime.Package object always default to using the 64 bit version if it is executed on a 64bit box ?

I would just call sp_start_job from C# and implement the IS packages in another job but unfortunatly my code cannot be given correct permissions to see the SQL agent jobs.

Any ideas?
Thanks

In a mixed platform environment you have to compile your code with AnyCPU instead of 32bits so just open your solutions in VS2005 and rebuild your code with AnyCPU and it will run in 64bits box. There maybe some configuration relevant to SSIS but in most mixed platform build with AnyCPU should fix it. I develop C# .NET applications with Oracle 9i/10g both 64bits with AnyCPU without any problems. Hope this helps.

|||

Caddre wrote:

I want to know about the performance using oracle packages in c#.net

Bhavin.

Bhavin_82@.hotmail.com

Executing an asp page from sql server.

Hi;

I don't know if this is the right forum, if not please move it.

I have an asp page using fso etc to create txt files on server. I want to call this asp page from sql server for example a table is updated. I mean I want to execute or call this file inside a trigger so a table is updated sql server will execute that asp page and create the text files i needed automatically.

Any help will be appriciated.

Thanks...

hey,

Triggers behave synchronously which means that you will have to wait for the external application (which you probably would need) to come back for the transaction to commit. In common this leads to very bad performance as well as a lot of ugly error retrieving if anything goes wrong. I would suggest using the following approach. After inserting the data into the table, write a log entry in a separate table. Write an application which can retrieve this information and call the asp page if needed. The scheduling of this application can be then either done on an OS basis (like the AT command) or SQl Server Agent, depends on where you want to have the control and wheter you have SQL Server Agent in place (e.g. It does not exists on SQL Server Express). That would act in a asynchronous way, not blocking the original transaction.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Assumption : Using SQL Server 2000

Yes.. As Jens K. Suessmeyer suggested you can convert your logic on Triggers itself.

If you don't want to write the code on SQL Server then you can write those logic as
components (ActiveX DLL - just copy paste the VB script from ASO) and you can register those on your SQL Server box, reuse it
from the SQL Server.

Declare @.oComp int
Declare @.HResult int
Declare @.Result as Varchar(8000)

Exec @.HResult = sp_OACreate 'YourCompPackageName.YourComponentName', @.oComp Output;

If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oComp, 'YourMethod', @.Result Output;

EXEC sp_OADestroy @.oComp;

If you don't want to write a component and still want to execute from SQL Server. the
following code will do. But it will degrade your performance.(NOT RECOMANDED)

Declare @.oHttp int
Declare @.HResult int
Declare @.Output as Varchar(8000)
Exec @.HResult = sp_OACreate 'MSXML2.XMLHttp', @.oHttp Output, 1

if @.HResult = 0
Begin
Exec @.HResult = sp_OAMethod @.oHttp, 'Open', NULL, 'POST', 'http://localhost', 'false'
If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, ''
If @.HResult = 0
Exec @.HResult = sp_OAGetProperty @.oHttp, 'ResponseText', @.Output OUTPUT
If @.HResult = 0
Select @.Output
End

EXEC sp_OADestroy @.oHttp

|||

Thanks for both of yours replies, I will try.

Have a nice day.

|||I tried the example and it works great.

How would you POST XML data (or any data) to the page? I have tried the following but keep getting errors on the 'Send'. Is there something I'm missing?

Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, '<field name="id"/>'
AND
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', '<field name="id"/>'

Thanks for any help!

Executing an asp page from sql server.

Hi;

I don't know if this is the right forum, if not please move it.

I have an asp page using fso etc to create txt files on server. I want to call this asp page from sql server for example a table is updated. I mean I want to execute or call this file inside a trigger so a table is updated sql server will execute that asp page and create the text files i needed automatically.

Any help will be appriciated.

Thanks...

hey,

Triggers behave synchronously which means that you will have to wait for the external application (which you probably would need) to come back for the transaction to commit. In common this leads to very bad performance as well as a lot of ugly error retrieving if anything goes wrong. I would suggest using the following approach. After inserting the data into the table, write a log entry in a separate table. Write an application which can retrieve this information and call the asp page if needed. The scheduling of this application can be then either done on an OS basis (like the AT command) or SQl Server Agent, depends on where you want to have the control and wheter you have SQL Server Agent in place (e.g. It does not exists on SQL Server Express). That would act in a asynchronous way, not blocking the original transaction.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Assumption : Using SQL Server 2000

Yes.. As Jens K. Suessmeyer suggested you can convert your logic on Triggers itself.

If you don't want to write the code on SQL Server then you can write those logic as
components (ActiveX DLL - just copy paste the VB script from ASO) and you can register those on your SQL Server box, reuse it
from the SQL Server.

Declare @.oComp int
Declare @.HResult int
Declare @.Result as Varchar(8000)

Exec @.HResult = sp_OACreate 'YourCompPackageName.YourComponentName', @.oComp Output;

If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oComp, 'YourMethod', @.Result Output;

EXEC sp_OADestroy @.oComp;

If you don't want to write a component and still want to execute from SQL Server. the
following code will do. But it will degrade your performance.(NOT RECOMANDED)

Declare @.oHttp int
Declare @.HResult int
Declare @.Output as Varchar(8000)
Exec @.HResult = sp_OACreate 'MSXML2.XMLHttp', @.oHttp Output, 1

if @.HResult = 0
Begin
Exec @.HResult = sp_OAMethod @.oHttp, 'Open', NULL, 'POST', 'http://localhost', 'false'
If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, ''
If @.HResult = 0
Exec @.HResult = sp_OAGetProperty @.oHttp, 'ResponseText', @.Output OUTPUT
If @.HResult = 0
Select @.Output
End

EXEC sp_OADestroy @.oHttp

|||

Thanks for both of yours replies, I will try.

Have a nice day.

|||I tried the example and it works great.

How would you POST XML data (or any data) to the page? I have tried the following but keep getting errors on the 'Send'. Is there something I'm missing?

Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, '<field name="id"/>'
AND
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', '<field name="id"/>'

Thanks for any help!
sql

ExecuteQuery taking a long time

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup
Try using a stored procedure if you can (dont know if you can do that in JDBC )|||

Hello Arup,

I would agree that the best way to solve long running query executions is to use a stored procedure which does all the processing of the data on the server and only return to the client the necessary amount of processed data via output parameters. You can use the CallableStatement API provided by the JDBC driver for doing this.

That being said, the Microsoft SQL Server 2005 v1.2 JDBC driver includes a critical 'feature' called "Adaptive Response Buffering" which allow developers to retrieve large amounts of data. The lengthy latency you are encountering is not because of the query execution, but because of the time it takes the driver to spool the data on the client machine before control is given back to the application.

You can ready more about "Adaptive Reponse Buffering" at http://blogs.msdn.com/jdbcteam/archive/2007/05/02/what-is-adaptive-response-buffering-and-why-should-i-use-it.aspx. This functionality if available in the V1.2 CTP1 driver which has the version 1.2.2323.101. You can download it at http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en.

We appreciate your feedback using the newly added functionality of this driver.

Kind Regards,

Jaaved

Execute SQL Task Error

Hi:

I am getting the following error message while trying to run a Execute SQL task with Variables in BIDS. My connection type is ADO.NET . My Variables defined are

Varout and Varin. Both are String type Variables. @.varout has a value set to "Category" and @.varin has a value set to "Test Category". I am using the expression

" Select * into " + @.[User::VarOut] + " FROM " + @.[User::Varin]

The expression eveluates correctly. The error I get when i run the package is:

Package Validation Error. Failed to lock Variable "Select * into TestCategory from category" for read access with error 0XC0010001. The Variable cannot be found. This occurs when an attempt is made to retrieve a variable from the variables collection on a container during the execution of package and the variable is not there. The Variable name may have changed or the Variable is not being created.

Can anyone please tell me what I am doing wrong or where do I need to look at?.

Thank you

AK

Can anyone please please give me a solution. This is really urgent. I found a KB describing a issue related to Script task but not sure if it is applicable to this issue. It involves applying a hot fix. Anyone from MS please comment.|||this link might help: http://msdn2.microsoft.com/en-us/library/ms141003.aspx|||

Thanks Duane. That Article helped. I was selecting the SQLSourcetype as Variable and that was throwing errors. I changed it and my package runs like a charm!!!.

Thanks again.

Execute sql script in the job

Hi!
I want to run a sql script in the job using T-SQL against a user
database. I tried to copy and paste the whole script in the T-SQL window
of the job but bc of length of the script (it is too long), it truncated
half of the script. How do I do that? Can we call this script using
xp_cmdshell and execute it? Is that possible? Any ideas?
The other option is to run it in the Query Analyzer which is a manual
effort and I do not want to do it bc this process is going to be ongoing
process and it is better if we make it automated.
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:#6MfyY6XFHA.2688@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I want to run a sql script in the job using T-SQL against a user
> database. I tried to copy and paste the whole script in the T-SQL window
> of the job but bc of length of the script (it is too long), it truncated
> half of the script. How do I do that? Can we call this script using
> xp_cmdshell and execute it? Is that possible? Any ideas?
> The other option is to run it in the Query Analyzer which is a manual
> effort and I do not want to do it bc this process is going to be ongoing
> process and it is better if we make it automated.
> Thanks for your help!
> *** Sent via Developersdex http://www.examnotes.net ***
Why not put the T-SQL code in to a stored procedure and then have the job
call the stored procedure.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick but we do not want to create stored procs:
1) avoid creating objects.
2) these scripts will also apply against other dbs ..so again it would
be a manual effort to create procs first and then run it under a job.
Any other ideas?
*** Sent via Developersdex http://www.examnotes.net ***|||"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:#M4q9L8XFHA.4000@.TK2MSFTNGP10.phx.gbl...
> Thanks Rick but we do not want to create stored procs:
> 1) avoid creating objects.
> 2) these scripts will also apply against other dbs ..so again it would
> be a manual effort to create procs first and then run it under a job.
> Any other ideas?
> *** Sent via Developersdex http://www.examnotes.net ***
Perhaps a DTS package then. You could call the package from a job.
Rick Sawtell
MCT, MCSD, MCDBA

Execute Program with Time Delay

Hi
I would like to execute a program from an SQL Server Proceedure.
But I need to Execute it with a 3 minute time delay, plus if there is
another instance of this delayed execute running then I need to not add
another.
Any help would be much appreciated
Thanks
BTry this
WAITFOR DELAY '00:03:00"
Madhivanan|||Synchronous or asynchronous?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ben" <Ben@.NoSpam.com> wrote in message news:%23wVcch9HFHA.3608@.TK2MSFTNGP14.phx.gbl...[col
or=darkred]
> Hi
> I would like to execute a program from an SQL Server Proceedure.
> But I need to Execute it with a 3 minute time delay, plus if there is anot
her instance of this
> delayed execute running then I need to not add another.
> Any help would be much appreciated
> Thanks
> B
>[/color]|||Hi Tibor
Asynchronous, as I would like the user to continue working, in ignorance of
the delay/execution of the application.
Thanks
B
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHzPo99HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Synchronous or asynchronous?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ben" <Ben@.NoSpam.com> wrote in message
> news:%23wVcch9HFHA.3608@.TK2MSFTNGP14.phx.gbl...
>|||You can, from the stored procedure, create or just schedule a SQL Server Age
nt job. See Books Online
for things as sp_add_job etc. You need to put in some code for the checking
whether the job is
already "on queue". Agent will not execute a job is it is already executing
(will not have two
instances of the same job executing at the same time), so perhaps this is su
fficient for you?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ben" <Ben@.NoSpam.com> wrote in message news:e2m4wF%23HFHA.3484@.TK2MSFTNGP12.phx.gbl...[col
or=darkred]
> Hi Tibor
> Asynchronous, as I would like the user to continue working, in ignorance o
f the delay/execution of
> the application.
> Thanks
> B
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHzPo99HFHA.1172@.TK2MSFTNGP12.phx.gbl...
>[/color]