http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=263197

and

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=293095

have similar situations.

The first is very tricky as the statement

Set @Var = Exec ServerName.DB.DBO.proc

works while

Insert TableName

Exec ServerName.DB.DBO.proc

doesn't.

I saw three related/similar posts in this forum/site. None of which have a finite solution.

Any resolution to this one yet?

I have this same problem. Any resolution to this yet? It may have something to do with triggers. Triggers are part of the transaction.

Thanks everyone for all your inputs. "scptxfr.exe" is very helpful for the database objects. I'm still looking at DMO for the repliactions, jobs.

You can change the logon account used by the MSSQLServer service.

Start > Run > Services.msc

Right-click the MSSQL service, properties, and change the logon account at the Logon tab.

sysprocesses -- thanks Bill! I'll have a look.

That would be quite difficult as the script is in an input file.

However, I fount in one of the links you gave, a way to append all the output in...

Thanks James, however, the cmd sees the OSQL command as not having an error.

Here's what I did.

--Batch File--

@ECHO OFF

OSQL -E -SHQSearch3 -i"C:\SQL DBA\ServerDrivesMonitoring\ServerDrivesMonitoring.sql" -o"C:\SQL DBA\ServerDrivesMonitoring\HQSearch3Result.txt"

IF NOT ERRORLEVEL 0 Goto...

Thanks for all your replies. I'll look into these tools.

BTW, the reason why I need to know which is causing the log outbursts (probably a job) is to find out if...

Select distinct * INTO #tmp from YourTable

Truncate Table YourTable

Insert YourTable Select * from #tmp

My apologies David.

Use a temp table...

Create #tmpTable(definition here)

Insert #tmpTable

Exec(@Qry)

If the cursor is really needed,

...cursor for select * from #tmpTable

David's answer will no work as Vladan mentioned, since the cursor is declared inside the dynamic SQL,...

Frances,

The answer has been mentioned in this thread so many times... USE THIS..

update InpatientMedStat set  InpatientMedStat.PrincipalDiagnosisCode = ltrim(rtrim(rptpxa2.DCode)) from InpatientMedStat

 INNER JOIN   rptpxa2 ON InpatientMedStat.PatientAccountNumber = rptpxa2.PNO

WHERE     (LEN(RTRIM(InpatientMedStat.PrincipalDiagnosisCode)) = 0) and (rptPXA2.DCode)...

Where exists(Select 1 From apvend   Where fvtype = 'MD' AND apvend.[rcitem].[fvendno] = [rcitem].[fvendno])