Running a Sql query from Crontab.

Hi,

I want to run a sql query from a crontab.
I hav written a script which enters SQLPLUS , executes the query and directs the output to a flat file.

My problem is when Iam palcing the script in crontab it is throwing an error .But when executed in the prompt ,it is running.

pls give ur suggestions on that.If you don't spell the name of the script correctly, then SQL Plus will throw an error.

As you probably guessed, I'm trying to be funny. ;) Without you providing information about the error being thrown, we have to guess at it. There's a pretty wide area for guessing!

Please be more specific. Cut the error message from the log (SQL Plus output) file, and post it here. If possible (without security or related problems), cut the whole command you are using, and post that here too.

With all of the people that contribute here, I'm sure that someone can help you if you can give them the information that they need to do that!

-PatPsql

RUNNING A DTS PACKET FROM WITHIN TSQL

Here is my problem: I don't have permissions to the 'master' db, thusly I don't have permissions to 'xp_cmdshell'. However, I need to output several small tables that I have created from one large table into individual text files.

Now, from the documentation I have read this is easily possible with the use of dts packets. However, my knowledge of dts packets is non-existent. So, I have created a dts packet that will take the data from a table and export it to a text file.

Here is what I need to know how to do: I would like all the small tables to go to their individual text files based on the file name I provide from within tsql. I would also like this to be done with the use of local tables (i.e. #tablename).

I am trying to alleviate human interface and the resulting errors from the sql statements we have now. We have an elaborate db system with tables and permissions all over the place, and thusly I want this done and removed with the use of local tables. Is this something that anyone could assist me with?

To execute a DTS package from a stored procedure or other t-sql code, you have to use the sp_OACreate and sp_OAMethod system procedures.

The problem, if you don't have permission to the master db, you won't be able to execute these either.

My recommendation to you would be to create SQL Server Agent job(s) to execute the DTS Package and allow the package to execute on a schedule. The SQL Agent execution account should have access to run the DTS Package.

The only other option is to be granted sufficient permission to the master db.

sql

RUNNING A DTS PACKET FROM WITHIN TSQL

Here is my problem: I don't have permissions to the 'master' db, thusly I don't have permissions to 'xp_cmdshell'. However, I need to output several small tables that I have created from one large table into individual text files.

Now, from the documentation I have read this is easily possible with the use of dts packets. However, my knowledge of dts packets is non-existent. So, I have created a dts packet that will take the data from a table and export it to a text file.

Here is what I need to know how to do: I would like all the small tables to go to their individual text files based on the file name I provide from within tsql. I would also like this to be done with the use of local tables (i.e. #tablename).

I am trying to alleviate human interface and the resulting errors from the sql statements we have now. We have an elaborate db system with tables and permissions all over the place, and thusly I want this done and removed with the use of local tables. Is this something that anyone could assist me with?

To execute a DTS package from a stored procedure or other t-sql code, you have to use the sp_OACreate and sp_OAMethod system procedures.

The problem, if you don't have permission to the master db, you won't be able to execute these either.

My recommendation to you would be to create SQL Server Agent job(s) to execute the DTS Package and allow the package to execute on a schedule. The SQL Agent execution account should have access to run the DTS Package.

The only other option is to be granted sufficient permission to the master db.

run the output

I want to delete the index:
How to run the output of:
select 'alter table ' + b.name + ' drop constraint ' + a.name from
sysobjects a
inner join syscolumns on syscolumns.id = a.parent_obj
inner join sysobjects b on syscolumns.id = b.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(b.name),'IsMSShipped') = 0
and a.xtype = 'D'
ThanksM,
Copy the output from the result pane into the query pane and Ctrl+E (or F5
or Alt+X).
HTH
Jerry
"M" <mxchen@.hotvoice.com> wrote in message
news:encucR20FHA.460@.TK2MSFTNGP15.phx.gbl...
> I want to delete the index:
> How to run the output of:
>
> select 'alter table ' + b.name + ' drop constraint ' + a.name from
> sysobjects a
> inner join syscolumns on syscolumns.id = a.parent_obj
> inner join sysobjects b on syscolumns.id = b.id
> where syscolumns.name = 'rowguid'
> and objectproperty(object_id(b.name),'IsMSShipped') = 0
> and a.xtype = 'D'
> Thanks
>
>|||Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u6FbrU20FHA.1264@.tk2msftngp13.phx.gbl...
> M,
> Copy the output from the result pane into the query pane and Ctrl+E (or F5
> or Alt+X).
> HTH
> Jerry
> "M" <mxchen@.hotvoice.com> wrote in message
> news:encucR20FHA.460@.TK2MSFTNGP15.phx.gbl...
>
>