Sql-server – Execute Stored Procedure that cannot be manually stopped

A common problem when developers involves the schema that SQL Server assumes when creating or altering an object. This is especially likely in the following circumstances:

1. The developer has recently been removed from the db_owner role The developer is working on a server that they normally don't.
2. They are likely to see the problem on a production server (where their permissions are tightly controlled) rather than a development server (where they might be a member of db_owner or even sysadmin).

This problem is often described by developers as "SQL Server is using the old version of my proc". What really happens is that the developer is modifying a copy of the procedure and executing a different copy of the procedure.

The quick way out of this is to ensure that you are providing a schema for the stored procedure with your drop, create, alter and execute statements. In SQL Server, the vast majority of the time, that schema is "dbo". In other words, prefer this:

alter procedure dbo.foo
as
select 1
go 
exec dbo.foo

and avoid this:

alter procedure foo
as
select 1
go 
exec foo

line 1, character 38, unable to switch the encoding

I ran in to this a while back sql server wants the source string type to match the utf code.

    SELECT CAST('<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST('<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO

You should see only the first and forth query have results. This is where the xml declared utf-"bits per character" matches the sql datatype.

line 9, character 9, unexpected end of input

I would run this problem from a different direction.

1. have the procedure output the received xml
2. check for permissions issues with the service accounts (difference between sql server and agent services)