[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

spooling database osql command error

Posted on 2010-04-07
12
Medium Priority
?
694 Views
Last Modified: 2012-05-09
I am troubleshooting an Ahsay online backup error and was instructed by their tech support to issue the following osql command to see if the database file in question can be spooled successfully. the command issued was:

osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'database_name' BACKUP DATABASE @dbname TO DISK = 'C:\temp\testdump.txt' WITH SKIP"

'datbase_name' is the name of the database

executing this command I get the following error:

Msg 207, Level 16, State 1, Server SERVERNAME Line 1 Invalid column name 'database_name'

the sql server is sql 2005 (free version). Can anyone explain how to correct. I am not too familiar with SQL
Thank you
0
Comment
Question by:ITIExperts1
  • 6
  • 6
12 Comments
 
LVL 3

Expert Comment

by:computerstreber
ID: 30050197
You need to change this: database_name to the name of your database.
0
 
LVL 3

Expert Comment

by:computerstreber
ID: 30050257
Here is an example using the AdventureWorks database:
osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'AdventureWorks' BACKUP DATABASE @dbname TO DISK = 'C:\temp\testdump.bak' WITH SKIP"

Open in new window

0
 

Author Comment

by:ITIExperts1
ID: 30117904
I used database_name here just for illustrative purposes. the actual command did contain the database name pgs_core:
osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'pgs_core' BACKUP DATABASE @dbname TO DISK = 'C:\temp\testdump.txt' WITH SKIP"

Msg 207, Level 16, State 1, Server SPGHOST Line 1 Invalid column name 'pgs_core'
 sorry for the confusion.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 3

Expert Comment

by:computerstreber
ID: 30122894
It my experience, the error you received generally occurs when you use, for example, double quotes instead of single quotes. Can you open a command prompt and try this:
C:\SET DBNAME=pgs_core [PRESS ENTER]
C:\OSQL -SSPGHOST -E -dMASTER -Q "BACKUP DATABASE %DBNAME% TO DISK = 'C:\temp\testdump.bak' WITH SKIP"

Open in new window

0
 

Author Comment

by:ITIExperts1
ID: 30139919
I tried your command, output as follows:

Processed 560 pages for database 'pgs_core', file 'tsfp' on file 1.
Processed 1 pages for database 'pgs_core', file 'tsfp_log' on file 1.
BACKUP DATABASE successfully processed 561 pages in 0.446 seconds (10.30
MB/sec).
0
 
LVL 3

Expert Comment

by:computerstreber
ID: 30141434
I can't really explain why, but I think there is either a syntax error in your command or it was failing with the "txt" extension because of size. But the message you got indicates that the backup was successful. If you go to the C:\TEMP directory of the SPGHOST server, you should see a file called testdump.bak. This is the back file.
0
 

Author Comment

by:ITIExperts1
ID: 30141807
i dont know what is going on here. i reissued the command and got the following output:
C:\>osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'pgs_core' BACKUP DATABAS
E @dbname TO DISK = 'C:\temp\testdump.txt' WITH SKIP"

Processed 560 pages for database 'pgs_core
', file 'tsfp' on file 1.
Processed 1 pages for database 'pgs_core
', file 'tsfp_log' on file 1.
BACKUP DATABASE successfully processed 561 pages in 0.284 seconds (16.182
MB/sec).
0
 

Author Comment

by:ITIExperts1
ID: 30141953
yes, the testdump.bak file is there as well as the testdump.txt file ahsay support said to try and create. i have submitted that file to their tech support along with my results. i should mention sql is running on vista home premium and these commands seemed to work when i ran the command prompt as the administrator.
0
 
LVL 3

Expert Comment

by:computerstreber
ID: 30143068
Ah.... That may have been the issue. I think with Vista you have to run OSQL commands in a command prompt that is running as the administrator.
0
 

Author Comment

by:ITIExperts1
ID: 30207921
Thank you for your help. I do not know if you are familiar with Ahsay backup software, but the errors I am getting are here:
[2010/04/09 08:50:14] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '\.'.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '\.'.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '\.'.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ' '.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ' '.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ' '.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

I have sent this log file to Ahsay for their assistance. If you can possibly shed any light on it, I'm all ears, if not. we can close this and I will accept the solution.
0
 
LVL 3

Accepted Solution

by:
computerstreber earned 2000 total points
ID: 30212270
I am not familiar with Ahsay. on. However, the errors appear to be due to syntax issues, namely incorrect use of ['] and ["]. Do you have control over the statements being prepared or does the application to all the statement preparation? To test, you might try setting the quoted identifier option. However, I would not due this in production, as it may have some adverse results.  
0
 

Author Comment

by:ITIExperts1
ID: 30213466
I have accepted the solution. I will wait for Ahsay to reply before I proceed any further. Thank you again for the help.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question