April 6, 2004 at 4:42 am
I have recently amended a script and changed it to a Stored Procedure. I call this SP from a job, setup under SQL Server Agent. The output from the job has [SQLSTATE 01000] at the end of every line. Is there a way of suppressing this as it makes the output look extremely untidy. Also, why does this happen ?
Thanks in advance.
April 6, 2004 at 6:43 am
First, I don't get that on ANY of my jobs.
Second, I did find out information about that in the BOL. Use the Index tab and enter SQLSTATE. Then select the troubleshooting option.
SQLSTATE messages are error messages and the one you are seeing is an ODBC API (Driver Manager) Error - General Warning.
-SQLBill
BOL=Books OnLine=Microsoft SQL Server's Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
April 6, 2004 at 8:05 am
THANKS FOR THAT sqlbill. I had already looked at BOL and it didn't mean a lot to me, hence the post. I was hoping someone may have experienced something similar to this and provide an explanation rather than state the bleeding obvious. It must be great to be a SQL guru and not have to ask for assistance. Maybe if I reply to everyone in a similar fashion I will end up with over 800 posts !
April 6, 2004 at 8:43 am
It appears my attempt to help you irritated you instead. That was not my intent.
You never said you checked the BOL or I would not have posted that suggestion.
I always do my best to help posters, even when I haven't experienced their problems myself. And I did point out to you that I have not had this issue.
Meanwhile I have been Googleing and looking through my other books to see if I could find more for you in case the previous information didn't help. Now I wonder if I should continue to try to help you.
But don't fret, I will still do my best to find you an answer.
Also, remember, you didn't give very much information. Like what did you change in the script? Did you run it as a stored procedure before you amended it? If so, did it give the same error?
-SQLBill
April 6, 2004 at 8:56 am
Is there more to the error message than just [SQLSTATE 01000]?
Everything I've seen on Microsoft's site indicates there should be more to the error message.
Basically, the SQLSTATE 01000 indicates an issue with the connection. (Do you have your database set for AUTOCLOSE?) The rest of the message should be more specific as to the actual cause. (Again, that's according to what I've read).
The reason I ask about autoclose is that if it is set to on, something in your script could be closing and reopenning the connection. And that could be causing the error.
Also, does the job complete or does it stop when it gives the error?
-SQLBill
April 6, 2004 at 10:20 am
Interestingly I used to get that with a procedure that called child procs and was executed through a job. Although the job never failed I could never find out the reason for the message.
April 6, 2004 at 10:45 am
CampbellM,
Despite the fact that the only information you gave us was:
1. you had amended a script
2. you turned the script into a procedure
3. you get [SQLSTATE 01000]
4. you were awfully rude
I am still trying to help you solve your issue.
You have still not answered my questions, but I understand you might be busy. So I'm just going to post something else I found. I have no clue if it applies to you or will help, since as I said above you really didn't give us much information to work with.
This Microsoft website: http://support.microsoft.com/default.aspx?scid=kb;en-us;140696
Has this information:
ODBC does not have the concept of message or error handlers like the ones in DB-Library. The Microsoft ODBC SQL Server Driver instead returns the output of PRINT and RAISERROR statements through the SQLError() function. PRINT statements will cause the SQL statement execution to return SQL_SUCCESS_WITH_INFO, and a subsequent call to SQLError() returns a SQLState of 01000. A RAISERROR with a severity of ten or lower will behave the same as PRINT. A RAISERROR with a severity of 11 will cause the execute to return SQL_ERROR and a subsequent call to SQLError() returns SQLState.
Does that apply to your issue?
Also, can you answer these questions:
1. Before you amended the script, was it a stored procedure?
2. Did you run the amended script before you made it a stored procedure?
3. If not, can you run it in Query Analyzer and see if you get the same result or if it only appears when the script is run as a procedure?
4. Basically what does the script do? The part before amending? The part you amended?
5. Any other error messages? (be sure to also check the SQL Server Error Logs and the Windows Event Viewer Logs - application,security,system)
-SQLBill
April 6, 2004 at 11:01 am
The SP is doing a transaction log backup of all databases and I have supplied an output file on the 'advanced' option of the edit job step. Originally I ran this script via a .cmd file which would write its output to the same location as the SP. I am just trying to remove a layer of administration. The output is identical, except for the [SQLSTATE 01000] message. I have noticed on the 'View Job History' that it states [SQLSTATE 01000] [Message 0] but there is nothing in event viewer and the databases are not set to autoclose. The job is successful but produces output with the above Syntax/Grammar. The account that the job runs under has full domain admin privileges.
thanks Bill for you help, bad day !
April 6, 2004 at 11:17 am
Again, interestingly, in a few short minutes you've answered something SQLBill that I could not get figured out in weeks. That's exactly what was going on with the proc that I had running, there were a lot of print statements embedded, that would be the reason.
April 6, 2004 at 11:37 am
CampbellM,
All is forgiven. I seem to recall in my searching something about this error and backups. I'll find it and post it here.
Stacenic,
Glad I was able to help you, now if only I can solve CampbellM's problem just as easily.
-SQLBill
April 6, 2004 at 11:55 am
CampbellM,
I just found something (and then I lost the webpage) that talked about what you seem to be experiencing:
1. backup job
2. [SQLSTATE 01000] [Message 0]
It indicated it's an issue with the MDAC version. Try upgrading your MDAC to 2.7 or higher (I think 2.8 is the latest). The post I saw (and it took me a lot of 'digging' to find) said the error went away after upgrading the MDAC to 2.7.
-SQLBill
June 29, 2004 at 7:36 am
This may be a little late, but you could try using osql through xp_cmdshell and send the output to a file. I had the same problem and used this method. It seems to work fine.
i.e.
EXEC xp_cmdshell 'osql /E /Q "<SQL>" -o "output.txt"'
June 29, 2004 at 1:18 pm
I've experienced this on many of my "utility" stored procedures. It always shows up on output produced by print statements in a stored procedure, not in scripts.
I've not tried updating MDAC to see if that affects it, but I will.
Steve
Here's an example...
**************************************************************************************************************** [SQLSTATE 01000]
1. Checking database afDBA: [SQLSTATE 01000]
recovery model is FULL. [SQLSTATE 01000]
emergency mode is off. [SQLSTATE 01000]
database is not in load. [SQLSTATE 01000]
database is not in recovery. [SQLSTATE 01000]
database is not in standby. [SQLSTATE 01000]
database is not in failed recovery status. [SQLSTATE 01000]
database is not offline. [SQLSTATE 01000]
database is not shutdown. [SQLSTATE 01000]
database is not suspect. [SQLSTATE 01000]
afDBA has passed all tests and will be backed up. [SQLSTATE 01000]
---> BACKUP DATABASE afDBA TO DISK = 'D:\SQL_Backups\AFFLEX_2004-06-28_19-00-01.BKP' WITH NOINIT, STATS=25 <--- [SQLSTATE 01000]
29 percent backed up. [SQLSTATE 01000]
58 percent backed up. [SQLSTATE 01000]
76 percent backed up. [SQLSTATE 01000]
Processed 536 pages for database 'afDBA', file 'afDBA_Data' on file 3. [SQLSTATE 01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1 pages for database 'afDBA', file 'afDBA_Log' on file 3. [SQLSTATE 01000]
BACKUP DATABASE successfully processed 537 pages in 0.770 seconds (5.703 MB/sec). [SQLSTATE 01000]
June 29, 2004 at 2:04 pm
Tested on my test server which was running MDAC 2.7, and received the SQLState messages. Upgraded MDAC on that server to 2.8, tested again, still got 'em.
Steve
July 7, 2004 at 8:48 pm
Has any one been able to resolve this issue?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply