SQLServerCentral / Discuss Content Posted by Haidong Ji / Article Discussions / Article Discussions by Author

Monitoring Disk Space and Sending Alerts with TSQL

Haidong Ji — Sun, 10 Aug 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/monitoringdiskspaceandsendingalertswithtsql.asp

xp_cmdshell failing in SQL Server 2000 SP4

lee.norman 47652 — Thu, 23 Feb 2012 13:11:50 GMT

This problem is occurring on a server that just recently fell to me. The OS is Server2003 SP2. There are nightly backups going to a local drive. Sometime later in the day, Veritas NetBackup copies them off to tape. There are maintenance plans in place that delete the old backups. The problem is that things have grown to the point that there is not enough room for two backups out there. I've tried messing with maintenance plans trying to get the old backups deleted before generating the new ones, but I haven't had any luck. Since the old backups are still there when the new ones run, the backups fail due to inadequate disk space. I don't know how long this has been going on - it's longer than the job history.I have tried to get around this by deleting the old backups before the maintenance plans kick off. To do this, I created a job to run 5 minutes before the maintenance plans start. That job contains a single step consisting of the following:cmd /C DEL /S /Q /A-A "D:\SQLBackups\*.bak"When I execute the job, it fails. The only message I can find anywhere is in the job history. There is nothing in the SQL Server Logs. There is nothing in the Event Viewer. There is nothing I can find anywhere. This is what appears in the job history:The job failed. The Job was invoked by User <username>. The last step to run was step 1 (Delete Old Backups).I have have pasted everything from the DEL on into a command prompt and it works fine. I have pasted the entire string into Start-->run and it works fine. But the job routinely fails.This job is owned by the service account. It is the account that SQL Server and the agent are running under. It's a local admin on the box. It has the System Administrators role checked. It has db_owner on every database on the box. When I look at the properties on xp_cmdshell and click permissions, I see this account with EXEC checked. If this is a permissions issue, I sure can't find where it's being denied.As nearly as I can tell, this account has God-like permissions on both the server and with SQL Server. I know the command string is good because I have run it using two other methods. Yet the job always fails and gives me nothing to go on but that nebulous "The job failed."While my immediate question is how to make this work or where else to look for some actual error message or number, I'm also open to any other ideas for how to accomplish this task.

Using SQL Trace to Audit Database Access

Haidong Ji — Thu, 12 Dec 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/trace.asp

Auditing DTS Packages

Haidong Ji — Sun, 05 Oct 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/auditingdtspackages.asp

Using VBScript to Automate Tasks

Haidong Ji — Fri, 31 Oct 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp

Oracle and SQL Server Data Interoperability Issues - Part 1

Haidong Ji — Sat, 10 Apr 2004 19:40:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.c

Becoming More Productive

Haidong Ji — Mon, 06 Dec 2004 16:56:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/becomingm

Linked Server // Query doesnt use Index

simon.ciglia — Tue, 21 Jul 2009 01:08:09 GMT

Environment:SQL 2005 with SP3Linked Server = Oracle 11g ServerWe are using the Oracle 10g ClientIf I made a query the duration takes a very long time until i get the results.In the Oracle Session Manager I saw that the SQL Server wasnt using the Oracle index. How could I force the SQL Server to use the Oracle indexes?Our Providers (OraOLEDB.Oracle) settings:"Allow inprocess" and "Supports 'Like' operator" is activated, the rest is deactivated.Our Linked Server Properties:The follow settings are "True":"Collation Compatible""Data Access""Use Remote Collation"Any help would be appreciated

Using xp_cmdshell

Haidong Ji — Sun, 28 Dec 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/usingxp_cmdshell.asp

Insert Into Oracle FROM Sql Server over Linked Server

allen.zobian — Tue, 04 Dec 2007 15:06:02 GMT

Does anyone know how to insert into an Oracle table with timestamp columns from a SQL Server table with datetime columns? Here are the table defs (kept the table name and column names the same across both platforms:ORACLE:create table Table_Name(Col1 varchar2(9),Col2 timestamp,Col3 varchar2(1000),Col4 varchar2(40),Col5 timestamp,constraint pk_col1 unique (Col1) using index tablespace TS_INDEX_01)tablespace TS_DATA_01;SQL SERVER:CREATE TABLE [dbo].[Table_Name] ( [Col1] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Col2] [datetime] NULL , [Col3] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Col4] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Col5] [datetime] NULL ) ON [PRIMARY]GOHere is what I have tried:Tested syntax and this select works:select * from openquery(linkedserver, 'select Col1,TO_CHAR(Col2, ''YYYY-MM-DD HH24:MI:SSXFF3''),Col3,Col4,TO_CHAR(Col5, ''YYYY-MM-DD HH24:MI:SSXFF3'') from Table_Name')So, I tried this query (two queries at same time - syntax works with tables with no timestamp/datetime fields):INSERT into openquery(linkedserver, 'select Col1,TO_CHAR(Col2, ''YYYY-MM-DD HH24:MI:SSXFF3''),Col3,Col4,TO_CHAR(Col5, ''YYYY-MM-DD HH24:MI:SSXFF3'') from Table_Name') #OracleSELECT Col1,Col2,Col3,Col4,Col5 from Table_Name #SQL ServerAnd I get this error (I can insert into the tables from SQL*Plus and QA, so neither table is read-only):Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'OraOLEDB.Oracle' reported an error. [OLE/DB provider returned message: ROW-00008: Cannot update data in a read-only column]OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowsetChange::InsertRow returned 0x80004005: ].Any assistance would be greatly appreciated!!!Thanks,Allen

Managing DTS packages - Editing, Scheduling, and Viewing Package Logs

Haidong Ji — Sun, 29 Dec 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/managingdtspackageseditingschedulingandviewingpack.asp

DTS PAckage

dsshivakumar — Sun, 05 Oct 2008 06:11:08 GMT

Hi All,I have requirement in DTS Package!!! i.e, we need to pick a latest .csv file from a folder and the vales in the csv file needs to uploaded into the database...Can any one suggest me how to achieve this???can we accomplish this by writing Activex script file in DTS package, If so PLease guide me in this reagard......

Introduction to SQLCMD

Haidong Ji — Mon, 02 Jan 2006 12:44:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/introductiontosqlcmd.asp

Using Database Mail instead of SQL Mail

Daniel Flanagan — Fri, 14 Sep 2007 09:26:00 GMT

error

Don't Overdo It, SQL Server

Haidong Ji — Tue, 25 Apr 2006 21:27:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/dontoverdoitsqlserver.asp

RE:

Warren Ball — Thu, 21 Sep 2006 12:01:00 GMT

These questions are about your second example.

First, what does the '-s' mean after the 'FTP' on the line to run the script? Does it mean 'send 1 file'?

Second, how can I create a logfile of the transaction? I'm trying the Chr(62) Chr(62) (>> stuff followed by my .log file but it does not get created.

Third, how can I freeze the black DOS screen that displays the commands, when the script is run? It flashes quickly in front of me and disappears. I have tried 'pause', 'echo', 'prompt' and a few other DOS commands to stop the screen in place but nothing works.

The file is not transferring and I would like a .log file and the DOS screen display, to help determine why. Does anyone know how I can do this?

Thanx!

Warren

Code Generation: Using SQL to Generate SQL and Other Code

Haidong Ji — Mon, 07 Feb 2005 16:37:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/codegenerationusingsqltogeneratesqlandothercode.asp

Oracle and SQL Server Interoperability Part 3

Haidong Ji — Wed, 28 Dec 2005 13:14:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/oracleandsqlserverinteroperabilitypart3.asp

PASS Summit Impressions

Haidong Ji — Sun, 09 Oct 2005 09:37:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/passsummitimpressions.asp

Using Different Techniques for SQL Server Automation

Haidong Ji — Tue, 06 Jul 2004 13:56:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/

Comparison of Oracle Drivers

Haidong Ji — Tue, 06 Jul 2004 13:56:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/compar

Review of Lumigent Entegra

Haidong Ji — Sun, 22 Feb 2004 07:53:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/reviewof

Using WMI

Haidong Ji — Fri, 05 Dec 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/usingwmi.asp

Automate DTS Logging

Haidong Ji — Fri, 29 Aug 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/automatedtslogging.asp