sql server tools: failure

Showing posts with label failure. Show all posts

Monday, March 19, 2012

Applying SP2 for SQL 2005 Enterprise Edition (on 64 bit windows)

Hello,

I'm experiencing some issues with applying SP2. I'm getting a failure for the database engine (on two instances that have been installed on a server). I've noticed in the forum that a few others have had some issues in this area as well. The slightly strange thing is that if I check the version of SQL Server on each instance it is telling me that SP2 has been installed:
9.00.3042.00 SP2 Enterprise Edition (64-bit)

So, the logs tell me I have a failed upgrade, but SQL is telling me it has been upgraded.
Any help would be appreciated.

--
from SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
--
There was a failure during installation search up in this log file for this message:
SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '='.. To continue, correct the problem, and then run SQL Server Setup again.
<EndFunc Name='ComponentUpgrade' Return='102' GetLastError='0'>
PerfTime Stop: CommitSqlUpgrade : Fri Jun 22 11:52:44 2007
Gathering darwin properties for failure handling.
<EndFunc Name='LaunchFunction' Return='102' GetLastError='0'>
MSI (s) (1C:98) [11:52:44:201]: Transforming table InstallExecuteSequence.
MSI (s) (1C:98) [11:52:44:201]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (1C:98) [11:52:44:216]: Transforming table InstallExecuteSequence.
MSI (s) (1C:98) [11:52:44:216]: Transforming table InstallExecuteSequence.
MSI (s) (1C:98) [11:52:44:216]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (1C:98) [11:52:44:216]: Transforming table InstallExecuteSequence.
MSI (s) (1C:98) [11:52:44:216]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (1C:98) [11:52:44:216]: Transforming table InstallExecuteSequence.
MSI (s) (1C:98) [11:52:44:216]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
Action ended 11:52:44: CommitSqlUpgrade.3EA9D9BF_D9D2_4023_B2A7_9E2137B2FB1B. Return value 3.

the end of that file has the following information:

MSI (s) (1C:98) [11:52:44:701]: Product: Microsoft SQL Server 2005 (64-bit) - Update 'Service Pack 2 for SQL Server Database Services 2005 (64-bit) ENU (KB921896)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log.
MSI (s) (1C:98) [11:52:44:701]: Note: 1: 1729
MSI (s) (1C:98) [11:52:44:701]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:701]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (1C:98) [11:52:44:716]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:716]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:716]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (1C:98) [11:52:44:716]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:716]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (1C:98) [11:52:44:716]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:716]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (1C:98) [11:52:44:716]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:716]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (1C:98) [11:52:44:732]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:732]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:732]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (1C:98) [11:52:44:732]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:732]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (1C:98) [11:52:44:732]: Transforming table Error.
MSI (s) (1C:98) [11:52:44:732]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (1C:98) [11:52:44:732]: Product: Microsoft SQL Server 2005 (64-bit) -- Configuration failed.
MSI (s) (1C:98) [11:52:44:748]: Attempting to delete file C:\WINDOWS\Installer\3cda1ca.msp
MSI (s) (1C:98) [11:52:44:794]: Cleaning up uninstalled install packages, if any exist
MSI (s) (1C:98) [11:52:44:794]: Post-install cleanup: removing installer file 'C:\WINDOWS\Installer\3cda1ca.msp'
MSI (s) (1C:98) [11:52:44:794]: MainEngineThread is returning 1603
MSI (s) (1C:94) [11:52:44:794]: Destroying RemoteAPI object.
MSI (s) (1C:BC) [11:52:44:794]: Custom Action Manager thread ending.
=== Logging stopped: 22/06/2007 11:52:44 ===
MSI (c) (60:AC) [11:52:44:841]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (60:AC) [11:52:44:841]: MainEngineThread is returning 1603
=== Verbose logging stopped: 22/06/2007 11:52:44 ===

Did you had a chance to look at
http://support.microsoft.com/default.aspx/kb/933945|||I have had a look at that but wasn't certain that it applied as it was for an earlier fix. I am thinking about trying to install the critical update for SP2 to see if that helps (http://support.microsoft.com/?kbid=934458). I haven't tried that, but am about to. If that works I will update this thread.

From Summary.txt from existing SP2 installation attempt:
-
Product : Database Services (SQL01)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed. Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.
-
Product : Database Services (SQL02)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '='.. To continue, correct the problem, and then run SQL Server Setup again.
-
...and at the end of the file:
**********************************************************************************
Summary
One or more products failed to install, see above for details
Exit Code Returned: 29537

|||I tried applying the critical update for SP2 (http://support.microsoft.com/?kbid=934458).

I basically got very, very similar symptons to what I expereienced when installing SP2.

The logs tell me that the process was not successful for the database engine(s) and I am still getting different error messages per instance. Some logs are included:
-
Summary.txt
-
Product Installation Status
Product : SQL Server Database Services 2005 (SQL01)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB934458_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed. Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.
-
Product : SQL Server Database Services 2005 (SQL02)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB934458_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '='.. To continue, correct the problem, and then run SQL Server Setup again.
-

-
SQL9_Hotfix_KB934458_sqlrun_sql.msp.log
-
There was a failure during installation search up in this log file for this message:
SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed. Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.
<EndFunc Name='ComponentUpgrade' Return='1802' GetLastError='0'>
PerfTime Stop: CommitSqlUpgrade : Tue Jun 26 16:43:40 2007
Gathering darwin properties for failure handling.
<EndFunc Name='LaunchFunction' Return='1802' GetLastError='0'>
MSI (s) (84:90) [16:43:40:476]: Transforming table InstallExecuteSequence.
MSI (s) (84:90) [16:43:40:476]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:90) [16:43:40:492]: Transforming table InstallExecuteSequence.
MSI (s) (84:90) [16:43:40:492]: Transforming table InstallExecuteSequence.
MSI (s) (84:90) [16:43:40:492]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:90) [16:43:40:492]: Transforming table InstallExecuteSequence.
MSI (s) (84:90) [16:43:40:492]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:90) [16:43:40:492]: Transforming table InstallExecuteSequence.
MSI (s) (84:90) [16:43:40:492]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:90) [16:43:40:492]: Transforming table InstallExecuteSequence.
MSI (s) (84:90) [16:43:40:492]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:90) [16:43:40:492]: Transforming table InstallExecuteSequence.
MSI (s) (84:90) [16:43:40:492]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
Action ended 16:43:40: CommitSqlUpgrade.3EA9D9BF_D9D2_4023_B2A7_9E2137B2FB1B. Return value 3.
Action ended 16:43:40: INSTALL. Return value 3.

--
SQL9_Hotfix_KB934458_sqlrun_sql.msp_0.log
--
There was a failure during installation search up in this log file for this message:
SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '='.. To continue, correct the problem, and then run SQL Server Setup again.
<EndFunc Name='ComponentUpgrade' Return='102' GetLastError='0'>
PerfTime Stop: CommitSqlUpgrade : Tue Jun 26 16:45:13 2007
Gathering darwin properties for failure handling.
<EndFunc Name='LaunchFunction' Return='102' GetLastError='0'>
MSI (s) (84:28) [16:45:13:713]: Transforming table InstallExecuteSequence.
MSI (s) (84:28) [16:45:13:713]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:28) [16:45:13:728]: Transforming table InstallExecuteSequence.
MSI (s) (84:28) [16:45:13:728]: Transforming table InstallExecuteSequence.
MSI (s) (84:28) [16:45:13:728]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:28) [16:45:13:728]: Transforming table InstallExecuteSequence.
MSI (s) (84:28) [16:45:13:728]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:28) [16:45:13:728]: Transforming table InstallExecuteSequence.
MSI (s) (84:28) [16:45:13:728]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:28) [16:45:13:728]: Transforming table InstallExecuteSequence.
MSI (s) (84:28) [16:45:13:728]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
MSI (s) (84:28) [16:45:13:728]: Transforming table InstallExecuteSequence.
MSI (s) (84:28) [16:45:13:728]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038
Action ended 16:45:13: CommitSqlUpgrade.3EA9D9BF_D9D2_4023_B2A7_9E2137B2FB1B. Return value 3.
Action ended 16:45:13: INSTALL. Return value 3.
--

If I then check what version files are at, sqlservr.exe is at version 2005.90.3054.0 and Microsoft.SqlServer.MaintenancePlanTasks.dll is at version 9.0.3054.0.

select @.@.version is returning "Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)"

So does this mean that the logs are not correct and SP2 and the updates have been successfully applied?

|||Could you check the version of Binaries ? sqlservr.exe|||Associated Hotfix Build: 3159
File version: 2005.090.3054.00
Product Version: 9.00.3054.00

This is for both instances. So, it looks as though the database engine got upgraded successfully - but the logs are telling me otherwise!
|||Exactly.

Applying SP2 for SQL 2005 Enterprise Edition (on 64 bit windows)

Hello,

the end of that file has the following information:

I basically got very, very similar symptons to what I expereienced when installing SP2.

If I then check what version files are at, sqlservr.exe is at version 2005.90.3054.0 and Microsoft.SqlServer.MaintenancePlanTasks.dll is at version 9.0.3054.0.

So does this mean that the logs are not correct and SP2 and the updates have been successfully applied?

Sunday, March 11, 2012

Apply Log File Transactions

I lost my mdf because of a failure on the RAID. The log
file was on another disk and it is apparently good thru
this morning. My last complete backup was last Friday
morning. I made two copies of the good log file and
restored the database using the option to restore more
log files. I would like to apply the log file I saved to
this database but the restore process needs a file
created by backup. I cannot figure out how to back up the
saved log file using a file name. How can I do this?
BACKUP LOG XXX FILE = ? TO DISK
= 'D:\MSSQL\BACKUP\SAFE.BKP'
Hi
Perform BACKUP LOG databasename WITH NO_TRUNCATE (For more details please
refer to BOL)
"pbrattin" <pbrattin@.removethis-bigfoot.com> wrote in message
news:181501c426d5$a693f360$a101280a@.phx.gbl...
> I lost my mdf because of a failure on the RAID. The log
> file was on another disk and it is apparently good thru
> this morning. My last complete backup was last Friday
> morning. I made two copies of the good log file and
> restored the database using the option to restore more
> log files. I would like to apply the log file I saved to
> this database but the restore process needs a file
> created by backup. I cannot figure out how to back up the
> saved log file using a file name. How can I do this?
> BACKUP LOG XXX FILE = ? TO DISK
> = 'D:\MSSQL\BACKUP\SAFE.BKP'
>
>

Apply Log File Transactions

I lost my mdf because of a failure on the RAID. The log
file was on another disk and it is apparently good thru
this morning. My last complete backup was last Friday
morning. I made two copies of the good log file and
restored the database using the option to restore more
log files. I would like to apply the log file I saved to
this database but the restore process needs a file
created by backup. I cannot figure out how to back up the
saved log file using a file name. How can I do this?
BACKUP LOG XXX FILE = ' TO DISK
= 'D:\MSSQL\BACKUP\SAFE.BKP'Hi
Perform BACKUP LOG databasename WITH NO_TRUNCATE (For more details please
refer to BOL)
"pbrattin" <pbrattin@.removethis-bigfoot.com> wrote in message
news:181501c426d5$a693f360$a101280a@.phx.gbl...
> I lost my mdf because of a failure on the RAID. The log
> file was on another disk and it is apparently good thru
> this morning. My last complete backup was last Friday
> morning. I made two copies of the good log file and
> restored the database using the option to restore more
> log files. I would like to apply the log file I saved to
> this database but the restore process needs a file
> created by backup. I cannot figure out how to back up the
> saved log file using a file name. How can I do this?
> BACKUP LOG XXX FILE = ' TO DISK
> = 'D:\MSSQL\BACKUP\SAFE.BKP'
>
>

Apply Log File Transactions

I lost my mdf because of a failure on the RAID. The log
file was on another disk and it is apparently good thru
this morning. My last complete backup was last Friday
morning. I made two copies of the good log file and
restored the database using the option to restore more
log files. I would like to apply the log file I saved to
this database but the restore process needs a file
created by backup. I cannot figure out how to back up the
saved log file using a file name. How can I do this?
BACKUP LOG XXX FILE = ' TO DISK
= 'D:\MSSQL\BACKUP\SAFE.BKP'Hi
Perform BACKUP LOG databasename WITH NO_TRUNCATE (For more details please
refer to BOL)
"pbrattin" <pbrattin@.removethis-bigfoot.com> wrote in message
news:181501c426d5$a693f360$a101280a@.phx.gbl...
> I lost my mdf because of a failure on the RAID. The log
> file was on another disk and it is apparently good thru
> this morning. My last complete backup was last Friday
> morning. I made two copies of the good log file and
> restored the database using the option to restore more
> log files. I would like to apply the log file I saved to
> this database but the restore process needs a file
> created by backup. I cannot figure out how to back up the
> saved log file using a file name. How can I do this?
> BACKUP LOG XXX FILE = ' TO DISK
> = 'D:\MSSQL\BACKUP\SAFE.BKP'
>
>

Friday, February 24, 2012

application failure due to sql upgrade

Hi, could someone give me some general pointers on why an application would
fail after an sql upgrade from 6.5 -> 7 or 7 -> 2000.
What are some of the reasons/causes an application would not become
compatible with an upgrade of sql. Are there any references/resources on
this?
Thanks!
PeterPeter,
There is a large Backward Compatability section in the BOL. Online it can
be found at:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp
Also there is a section on New and Enhanced Features in Transact-SQL.
Online this is at:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp
Hope that these help.
Russell Fields
"Hz" <Hz@.nospam.com> wrote in message
news:flmAb.4463$xm.141272@.nasal.pacific.net.au...
> Hi, could someone give me some general pointers on why an application
would
> fail after an sql upgrade from 6.5 -> 7 or 7 -> 2000.
> What are some of the reasons/causes an application would not become
> compatible with an upgrade of sql. Are there any references/resources on
> this?
> Thanks!
> Peter
>|||Russell gave some good links, but in the end,
functionality, code calls, and syntax can change.
Plus, DB-Lib, which was (pre-7.0) the preferred method of
connecting to SQL Server, is no longer that way from 7.0
on, and newer features cannot be used with DB-Lib.
The application must be tested PRIOR to upgrading to fix
any problems in a test environment.
>--Original Message--
>Hi, could someone give me some general pointers on why an
application would
>fail after an sql upgrade from 6.5 -> 7 or 7 -> 2000.
>What are some of the reasons/causes an application would
not become
>compatible with an upgrade of sql. Are there any
references/resources on
>this?
>Thanks!
>Peter
>
>.
>|||Thanks for the pointers, they are much appreciated!
Peter
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:e49301c3bc25$181fb990$a601280a@.phx.gbl...
> Russell gave some good links, but in the end,
> functionality, code calls, and syntax can change.
> Plus, DB-Lib, which was (pre-7.0) the preferred method of
> connecting to SQL Server, is no longer that way from 7.0
> on, and newer features cannot be used with DB-Lib.
> The application must be tested PRIOR to upgrading to fix
> any problems in a test environment.
> >--Original Message--
> >Hi, could someone give me some general pointers on why an
> application would
> >fail after an sql upgrade from 6.5 -> 7 or 7 -> 2000.
> >
> >What are some of the reasons/causes an application would
> not become
> >compatible with an upgrade of sql. Are there any
> references/resources on
> >this?
> >
> >Thanks!
> >Peter
> >
> >
> >.
> >

Monday, February 13, 2012

App_Data new DB fails

Hello,

I'm trying to create a new SQL Express database in VWD, and I get the error: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

What is this error all about? I don't understand it.

Thanks.

The db when added in VS has no owner and is somehow bound to the profile. So many things can go wrong with that, so instead of guessing, wipe it out and let the IDE recreate it.

This blog saved me days of heartache. Unfortunately, I still wasted time till I came across it.

http://www.sqljunkies.com/WebLog/ktegels/archive/2005/11/15/17401.aspx

1. Disconnect all dbs, close down the IDE, close down SSMS(if any) on your machine.
2. Stop all services and processes related to sql server.
3. Rename(safer than deleting) the whole folder:
c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

4.restart the IDE/SSE.
5. this song-n-dance may not work the first time. Try more than once.

I am really disappointed at the flaky architecture of SSE and have stopped using it since.

HTH!

Apostrophe in comment confuses ADODB

We just encountered an odd failure in ADODB. If it gets an SQL query with a comment embedded in it, and the comment has an apostrophe as one of the characters, ADODB gets confused as it tries to plug in parameters for placeholders later in the query. For example:

SELECT id, name

FROM doc_type /* this won't work! */

WHERE name LIKE ?

If you take out the apostrophe ("this will not work!") or move the comment to follow the placeholder, the query works.

Is this a known bug (couldn't find it in the KB)?

Thanks,
Bob Kline

SQL Server interprets the embedded apostrophe as the end of string marker (since SQL Server uses apostrophes to delimit string data.

There are a couple of things to try.

The simplest one is to 'escape' the apostrophe -by replacing a single apostrophe with two single apostrophes.

The other possible solution requires that the server be configured in accordance. That one allows using double quotes as a string delimiter.

|||Thanks for your reply, Arnie. Actually, it's not SQL Server which is misbehaving in this case. If, for example, you do the same thing in, say ADO.Net, or JDBC, SQL Server treats the apostrophe inside the comment as just another character in the comment (as it should). So this bug is specific to ADODB.

So, once again, is this a known bug and I just haven't been using the right query terms to find it in the knowledge base? Or should I file a bug report with Microsoft?

Cheers,
Bob Kline

|||

You're right Bob, I meant to implicate ADODB as the culprit.

This is known behavior, I don't if it is considered a 'bug', or just an 'inconvenience'.

However, please post your comments and concerns about the behavior (in the feedback section). It will get to the 'right' parties, and if enough folks 'vote' and validate the issue, it will esculate and perhaps be 'fixed'.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||

An apostrophe in a VB query string is not a bug. rofl

Please do not report this as a bug.

Adamus

|||> This is known behavior ...

You wouldn't happen to have a KB number, would you?

> ... don't [know] if it is considered a 'bug' ....

I would respectfully suggest that anyone who doesn't recognize this as a defect doesn't understand the meaning of the phrase "ignores all characters within a comment" (from Microsoft's SQL Server documentation).

> ... please post your comments ... in the feedback section ....

Will do.

Thanks,
Bob

|||

Adamus Turner wrote:

An apostrophe in a VB query string is not a bug. rofl
Please do not report this as a bug.
Adamus

Hello, Adamus.

Are you trying to say that you think an apostrophe embedded within a double-quoted string constant is interpreted by Visual Basic as the beginning of a VB comment? After you pick yourself back up off the floor you might want to go back and re-read the VB docs.

Cheers,
Bob

|||

Bob Kline wrote:
> This is known behavior ...
You wouldn't happen to have a KB number, would you?
> ... don't [know] if it is considered a 'bug' ....
I would respectfully suggest that anyone who doesn't recognize this as a defect doesn't understand the meaning of the phrase "ignores all characters within a comment" (from Microsoft's SQL Server documentation).
> ... please post your comments ... in the feedback section ....
Will do.
Thanks,
Bob

Hi Bob,

To further elaborate:

First, It's not a (SQL) comment 'yet' while it's in the vb string. It's just a vb string.

Second, it's normal that INSERT/UPDATE commands have values that contain apostrophe's especially when you're dealing with varchar's and text

Third, it is not a bug, glitch, or a pin hole in the design. It's standard 101 programming.

Lastly, why on earth are you executing a SQL comment in a vb query string?

P.S. This should be posted in the VB Forums not SQL Server

Correction: This should be posted on Comedy Central

Adamus

|||

Adamus:

It's possible (though I realize unlikely) that one day you'll take a deep breath, go back and re-read this thread more carefully, and realize that you've missed some of the details. I'll give you a clue: your initial contribution to the thread assumed (without any basis of evidence from earlier postings in the thread) that we are using Visual Basic. This assumption was, in fact, not correct. That's one of the reasons I know that your idea about VB comment delimiters is a red herring.

Regards,

Bob

|||

Adamus Turner wrote:

Are you saying the query runs fine in ADO(.net) or runs fine with OLEDB?

Yes.

I'll try one more time. The apostrophe is inside an SQL comment. An SQL parser is required to ignore all characters inside an SQL comment (confirmed by Microsoft's own documentation). The query (as originally posted) runs fine when submitted from Query Analyzer, ADO.Net, JDBC, or any other data access layer except ADODB.

Cheers,

Bob

|||

Bob Kline wrote:

The query (as originally posted) runs fine when submitted from Query Analyzer, ADO.Net, JDBC, or any other data access layer except ADODB.

Correction:

When submitting the query in Query Analyzer, the placeholder is replaced with the literal string value. This doesn't affect the value of using Query Analyzer to demonstrate that an SQL query containing an embedded comment, which in turn contains an apostrophe character, is correct syntax.

Bob

|||

Bob Kline wrote:

Bob Kline wrote:

The query (as originally posted) runs fine when submitted from Query Analyzer, ADO.Net, JDBC, or any other data access layer except ADODB.

Correction:
When submitting the query in Query Analyzer, the placeholder is replaced with the literal string value. This doesn't affect the value of using Query Analyzer to demonstrate that an SQL query containing an embedded comment, which in turn contains an apostrophe character, is correct syntax.
Bob

Arnie has answered this a long time ago. Escape the apostrophe.

|||

Adamus Turner wrote:

Arnie has answered this a long time ago. Escape the apostrophe.

I tried his suggested workaround. It doesn't work. Even if it did, working around a bug is not the same thing as getting it fixed. A correct SQL parser ignores all characters inside an SQL comment, and the parser inside ADODB isn't behaving that way.

Regards,

Bob

|||

Bob Kline wrote:

Adamus Turner wrote:

Arnie has answered this a long time ago. Escape the apostrophe.

I tried his suggested workaround. It doesn't work. Even if it did, working around a bug is not the same thing as getting it fixed. A correct SQL parser ignores all characters inside an SQL comment, and the parser inside ADODB isn't behaving that way.
Regards,
Bob

Why do you think it's a workaround? Correct syntax is not a workaround. You have to escape a single quote in a query string. It does work. It will work. You are just coding it wrong. You need to swallow some pride and post your code before you lose any more integrity.

Adamus

|||

Bob Kline wrote:

Adamus Turner wrote:

Arnie has answered this a long time ago. Escape the apostrophe.

I tried his suggested workaround. It doesn't work. Even if it did, working around a bug is not the same thing as getting it fixed. A correct SQL parser ignores all characters inside an SQL comment, and the parser inside ADODB isn't behaving that way.

My apologies, I tested again and the workaround (doubling the apostrophe) does eliminate the failure.

I've opened a case with Microsoft, and they've confirmed that this behavior of ADODB is a bug.

Cheers,

Bob

Apostrophe in comment confuses ADODB

SELECT id, name

FROM doc_type /* this won't work! */

WHERE name LIKE ?

If you take out the apostrophe ("this will not work!") or move the comment to follow the placeholder, the query works.

Is this a known bug (couldn't find it in the KB)?

Thanks,
Bob Kline

SQL Server interprets the embedded apostrophe as the end of string marker (since SQL Server uses apostrophes to delimit string data.

There are a couple of things to try.

The simplest one is to 'escape' the apostrophe -by replacing a single apostrophe with two single apostrophes.

The other possible solution requires that the server be configured in accordance. That one allows using double quotes as a string delimiter.

So, once again, is this a known bug and I just haven't been using the right query terms to find it in the knowledge base? Or should I file a bug report with Microsoft?

Cheers,
Bob Kline

|||

You're right Bob, I meant to implicate ADODB as the culprit.

This is known behavior, I don't if it is considered a 'bug', or just an 'inconvenience'.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||

An apostrophe in a VB query string is not a bug. rofl

Please do not report this as a bug.

Adamus

|||> This is known behavior ...

You wouldn't happen to have a KB number, would you?

> ... don't [know] if it is considered a 'bug' ....

> ... please post your comments ... in the feedback section ....

Will do.

Thanks,
Bob

|||

Adamus Turner wrote:

An apostrophe in a VB query string is not a bug. rofl
Please do not report this as a bug.
Adamus

Hello, Adamus.

Cheers,
Bob

|||

Bob Kline wrote:
> This is known behavior ...
You wouldn't happen to have a KB number, would you?
> ... don't [know] if it is considered a 'bug' ....
I would respectfully suggest that anyone who doesn't recognize this as a defect doesn't understand the meaning of the phrase "ignores all characters within a comment" (from Microsoft's SQL Server documentation).
> ... please post your comments ... in the feedback section ....
Will do.
Thanks,
Bob

Hi Bob,

To further elaborate:

First, It's not a (SQL) comment 'yet' while it's in the vb string. It's just a vb string.

Second, it's normal that INSERT/UPDATE commands have values that contain apostrophe's especially when you're dealing with varchar's and text

Third, it is not a bug, glitch, or a pin hole in the design. It's standard 101 programming.

Lastly, why on earth are you executing a SQL comment in a vb query string?

P.S. This should be posted in the VB Forums not SQL Server

Correction: This should be posted on Comedy Central

Adamus

|||

Adamus:

Regards,

Bob

|||

Adamus Turner wrote:

Are you saying the query runs fine in ADO(.net) or runs fine with OLEDB?

Yes.

Cheers,

Bob

|||

Bob Kline wrote:

The query (as originally posted) runs fine when submitted from Query Analyzer, ADO.Net, JDBC, or any other data access layer except ADODB.

Correction:

Bob

|||

Bob Kline wrote:

Bob Kline wrote:

The query (as originally posted) runs fine when submitted from Query Analyzer, ADO.Net, JDBC, or any other data access layer except ADODB.

Correction:
When submitting the query in Query Analyzer, the placeholder is replaced with the literal string value. This doesn't affect the value of using Query Analyzer to demonstrate that an SQL query containing an embedded comment, which in turn contains an apostrophe character, is correct syntax.
Bob

Arnie has answered this a long time ago. Escape the apostrophe.

|||

Adamus Turner wrote:

Arnie has answered this a long time ago. Escape the apostrophe.

Regards,

Bob

|||

Bob Kline wrote:

Adamus Turner wrote:

Arnie has answered this a long time ago. Escape the apostrophe.

I tried his suggested workaround. It doesn't work. Even if it did, working around a bug is not the same thing as getting it fixed. A correct SQL parser ignores all characters inside an SQL comment, and the parser inside ADODB isn't behaving that way.
Regards,
Bob

Adamus

|||

Bob Kline wrote:

Adamus Turner wrote:

Arnie has answered this a long time ago. Escape the apostrophe.

I tried his suggested workaround. It doesn't work. Even if it did, working around a bug is not the same thing as getting it fixed. A correct SQL parser ignores all characters inside an SQL comment, and the parser inside ADODB isn't behaving that way.

My apologies, I tested again and the workaround (doubling the apostrophe) does eliminate the failure.

I've opened a case with Microsoft, and they've confirmed that this behavior of ADODB is a bug.

Cheers,

Bob

Monday, March 19, 2012

Applying SP2 for SQL 2005 Enterprise Edition (on 64 bit windows)

Applying SP2 for SQL 2005 Enterprise Edition (on 64 bit windows)

Sunday, March 11, 2012

Apply Log File Transactions

Apply Log File Transactions

Apply Log File Transactions

Friday, February 24, 2012

application failure due to sql upgrade

Monday, February 13, 2012

App_Data new DB fails

Apostrophe in comment confuses ADODB

Apostrophe in comment confuses ADODB

sql server tools

Blog Archive

About Me