-
Script
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
-
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.
-
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?
-
You may use global temp table.
-
If I do that will I be able to use my script just how it is?
-
May have issue if you run the script on multiple dbs at same time.
-
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?
-
Tried this:
INSERT ##Test(Check_Log)
EXEC master..xp_cmdshell 'osql -Sserver -E -Q"DBCC CHECKDB(db_name)"'
-
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?
-
You can try 'select log_time, check_log from ##test' instead of 'select * ...'.
Last edited by rmiao; 12-20-2006 at 01:19 PM.
-
That didn't work. I am still getting the same result.
-
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
-
Didn't work. I still get these spaces between the lines. ANy other ideas?
-
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--
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|