Script

[inet]

Hi All,

When I run the script below I get the error "Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#table'".


CREATE TABLE #table (Check_Log VARCHAR(1000), Log_Time datetime default GetDate())

INSERT #table(Check_Log)
EXEC master..xp_cmdshell 'osql -S server -U user -P password -d db -Q"DBCC CHECKDB"'

EXEC master..xp_cmdshell 'bcp dbname.user.#table out Z:\Test\CheckDBRes.txt -S server -U user -P password'

SELECT * FROM #table

IF EXISTS (SELECT * FROM #table
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database')

PRINT 'No errors'

ELSE

RETURN

DROP TABLE #table

Can you please tell what I am doing wrong?

Thanks

[rmiao]

Local temp table is only available to current session, while EXEC master..xp_cmdshell 'bcp dbname.user.#table out Z:\Test\CheckDBRes.txt -S server -U user -P password' opens another session.

[inet]

So if I'll create a regular table instead of the temp table then I shouldn't get this error? The reason I wanted to use a temp table is because I was planning on putting this script in the job and run this job for every database I have. I would change the -d parameter to reflect that. That means that I have to create a table within each db, I was trying to avoid that. Do you know of any other way to do what I am trying to accomplish?

[rmiao]

You may use global temp table.

[inet]

If I do that will I be able to use my script just how it is?

[rmiao]

May have issue if you run the script on multiple dbs at same time.

[inet]

The job will be scheduled to run for multiple dbs at different times. I've modified the script to use a global temp table.

CREATE TABLE ##Test (Check_Log VARCHAR(1000), Log_Time datetime default GetDate())

INSERT ##Test(Check_Log)
EXEC master..xp_cmdshell 'osql -Sserver -E -ddbname -Q"DBCC CHECKDB"'

EXEC master..xp_cmdshell 'bcp dbname.user.##Test out C:\Test\CheckDBRes.txt -Sserver -c -Uuser -Ppassword'

IF EXISTS (SELECT * FROM ##Test
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database')
PRINT 'No errors'

ELSE
RETURN

Now I am getting a different error.

Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'dbname' ignored, referencing object in tempdb

Can you tell me why I am getting this error?

[rmiao]

Tried this:

INSERT ##Test(Check_Log)
EXEC master..xp_cmdshell 'osql -Sserver -E -Q"DBCC CHECKDB(db_name)"'

[inet]

I've decided not to use bcp and use osql utility with -Q parameter.

CREATE TABLE ##Test (Check_Log VARCHAR(1000), Log_Time datetime default GetDate())

INSERT ##Test(Check_Log)
EXEC master..xp_cmdshell 'osql -Sserver -E -ddbname -Q"DBCC CHECKDB"'

IF EXISTS (SELECT * FROM ##Test
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database')
PRINT 'No errors'

ELSE
RETURN

EXEC master..xp_cmdshell 'osql -S server -U user -P password -d dbname -Q "select * from ##Test" -o C:\Test\CheckDBRes.txt'

I am trying to format the output that looks like this:

Check_Log













Log_Time
------------------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------

DBCC results for 'dbname'.













2006-12-20 10:47:05.897
DBCC results for 'sysobjects'.













2006-12-20 10:47:05.897
There are 4704 rows in 100 pages for object 'sysobjects'.













2006-12-20 10:47:05.897
DBCC results for 'sysindexes'.


Can you please help?

[rmiao]

You can try 'select log_time, check_log from ##test' instead of 'select * ...'.

[inet]

That didn't work. I am still getting the same result.

[rmiao]

If you want to get rid of blanks, you can use len function to substring check_log column:

select substring(check_log, 1, len(check_log)) as log from ##test

[inet]

Didn't work. I still get these spaces between the lines. ANy other ideas?

[mikr0s]

EXEC master..xp_cmdshell 'osql -S server -U user -P password -d dbname -Q "select CAST(Check_Log AS VARCHAR(100)), Log_Time from ##Test" -o C:\Test\CheckDBRes.txt'

--replace 100 with a number that suits your needs

--HTH--