Weird SQL 2005 Problem...

[AZRedhawk44]

This one's odd and I'm hoping someone else knows of an easy solution to it...

I've got some stored procedures I've written as part of an interface for work.

They look something like this in the text file they are saved in and run from:

Code: [Select]

CREATE PROCEDURE my_procedure
AS

SELECT * FROM SOMETABLE
WHERE somecol = 'sometext'

GO

And when I right click in management studio and click "modify" I get something along the lines of the following:

Code: [Select]

USE [MyDB]
GO
/****** Object:  StoredProcedure [dbo].[my_procedure]    Script Date: 07/14/2009 11:59:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[my_procedure]
as

SELECT * FROM SOMETABLE
WHERE somecol = 'sometext'

GO

But... at a client's site that I am beta-testing this interface, any procedure I right-click on to modify, I get something more like:

Code: [Select]

USE [MyDB]
GO
/****** Object:  StoredProcedure [dbo].[my_procedure]    Script Date: 07/14/2009 11:59:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[my_procedure]
as

declare @SQLCode nvarchar(4000)

set @SQLCode = N'SELECT * FROM SOMETABLE
WHERE somecol = ''sometext'''

exec sp_executesql @SQLCode
GO

It goes and ruins my perfectly wonderful SQL statement and turns it into some nasty dynamic SQL.

Only on this client's server.  I cannot find a way to get one of my test servers to act like this.

My client's server is over in the UK... could some sort of UK locality have anything to do with this?

I can't have this proc doing this... it's not as simple as the above basic SQL statement.  Execution cache is important in this application, and Dynamic SQL has to rebuild execution cache every time it's run.  One of the key benefits of stored procedures is the fact that they remain precompiled as long as the underlying database structure is intact. 

I've never seen a proc "disassemble" itself like this before and it has me tearing my hair out.

Rev?  Nick?  Anyone else have ideas?

[Brad Johnson]

I've never seen a proc "disassemble" itself like this before and it has me tearing my hair out.

NO DISASSEMBLE!  Number Five is alive!

Brad

[Nick1911]

I'm sorry - I don't have any clue on this one.  :|

At work, we run exclusively Oracle DB's.  At home I use MySQL and (infrequently) Postgres.

Somehow, SQL Server has decided that your procedure should be some kind of TSQL dynamic.... thing.

[GigaBuist]

My client's server is over in the UK... could some sort of UK locality have anything to do with this?

Yeah.  You're switching charsets between installs and it's freaking out about that.

set @SQLCode = N'SELECT * FROM SOMETABLE
WHERE somecol = ''sometext'''

That N'' quoting stuff there is turning it into a Unicode string.  My guess is that your client (SSMS) install is using a different codepage than the server when it does that.

I'd offer to do some experiments but I'm pretty slammed right now.  I'll try and follow the thread though, and try to help, because this is something I'm likely to run into someday.

[AZRedhawk44]

I hear ya about unicode and can contribute this:

To execute dynamic SQL (I have pretty extensive experience with the stuff), you have to store the command you're about to execute in Unicode.  Using a standard varchar won't work, so you have to use an nvarchar.  SP_EXECUTESQL just won't work unless the supplied final string is unicode.

So, it just makes sense that if SQL Server is going to panic and run to dynamic SQL for a stored proc, that it would declare a unicode variable and then store it as such.

I'm working around this for two reasons:
1.  The execution cache issue;
2.  nvarchar is capped at 4000 characters in SQL 2000.  SQL 2005 has a new nvarchar(max) variable that is supposedly good for 2GB-length text strings on 64 bit installations.

I've run into the 4000 character cap in the past, but am unable to depricate the nvarchar(4000) variable in favor of nvarchar(max) and mandate SQL 2005 due to customer support and political reasons.  Have to maintain SQL 2000 compatibility for at least the next couple years.

That issue is for an unrelated product to this issue though, and is the deliberate reason why I've avoided dynamic SQL and instead used variable driven logic branching and completely separate queries in the procs based on those variable options.

My txt files that store the CREATE PROCEDURE arguments for my VB front end don't have an ounce of unicode in them.  I'm feeding them into SQL Server via this method, more or less:
(VB Code)

Code: [Select]

Try
            fsSQLFileReader = My.Computer.FileSystem.OpenTextFileReader(strScriptDirectory & "my_procedure.sql")
            strSQLString = fsSQLFileReader.ReadToEnd
            ProgressBar1.Increment(1)
            srvSQL.ConnectionContext.ExecuteNonQuery(strSQLString, ExecutionTypes.Default)
        Catch ex As Exception
            Me.TextBox1.AppendText("ERROR:  File not found or invalid syntax in file!" & vbCrLf)
            Exit Sub
End Try

Never raises an exception during execution when it installs the proc...  Just installs nice and clean.

[GigaBuist]

Makes sense.  I'd bet dollars to donuts that somewhere in this:

fsSQLFileReader = My.Computer.FileSystem.OpenTextFileReader(strScriptDirectory & "my_procedure.sql")
strSQLString = fsSQLFileReader.ReadToEnd

You're getting a BOM (Byte Order Mark) being introduced.  Last winter I saw some ASP.Net code do just that to me which played havok with the string when written back out to disk and letting a Java based process munch on it.  Chopping off the first two bytes (where the BOM is) solved it.

Removing the BOM will let the destination SQL Server process it like a native string.  Just a hunch.  Of course the only way you'd see it work on local machines if they were set to use something like UTF-8 as the local codepage which I find doubtful, so I might be entirely off base.

[AZRedhawk44]

Makes sense.  I'd bet dollars to donuts that somewhere in this:
You're getting a BOM (Byte Order Mark) being introduced.  Last winter I saw some ASP.Net code do just that to me which played havok with the string when written back out to disk and letting a Java based process munch on it.  Chopping off the first two bytes (where the BOM is) solved it.

Removing the BOM will let the destination SQL Server process it like a native string.  Just a hunch.  Of course the only way you'd see it work on local machines if they were set to use something like UTF-8 as the local codepage which I find doubtful, so I might be entirely off base.

Except... when I right click on the proc in SQL management studio at the client site and modify the proc (resulting in the weird unicode dynamic sql situation)... I can take the time to eliminate the @SQLCode variable and the sp_executesql command, turning the proc back to its normal form... run the alter proc statement... and then go back to modify again:  I get the same dynamic SQL results.

Also, on 3 different 2000 and 2005 test SQL Servers I get as-expected behavior from the VB code.

Can you explain this BOM error in more detail?  If I look at my SQL/TXT files in a hex editor like XVI32, the very first two bytes are 2F 2A... which are /*.  The beginning of my comment block at the top of the proc.

Now... one thing I HAVE come across in the past is a tendency for SQL Management Studio to save new line characters when saving a script to a text file purely as LF (0A in hex), rather than the more DOS conventional CR/LF (0D 0A).

Can you see that as being an issue in this case?

[GigaBuist]

The BOM would be FF EF or FF FE, something like that, so I was off base with that line of thinking.

This is really freaking weird.  Just curious, but are you on SP3 w/ the installs that are doing whacky things?

[AZRedhawk44]

I dunno... I will have to check later.