Cannot use the OUTPUT option when passing a constant Stored Proced

I am having a very difficult problem.
I get the following error:
Microsoft SQL Native Client
Cannot use the OUTPUT option when passing a constant to a Stored Procedure
In SQL 2005 SP2, I check Profiler, here are my findings:
UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user exec
this sp as "dbo"
UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
exec this sp as "DOMAIN\UserName"
If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
that user exec this sp as "dbo".
What is going on? This started after updating SQL 2005 to SP2.
Thanks
StephenCan you tell us more about how exactly you are calling this sp? Can you
show the actual code?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
>I am having a very difficult problem.
> I get the following error:
> Microsoft SQL Native Client
> Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> In SQL 2005 SP2, I check Profiler, here are my findings:
> UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec
> this sp as "dbo"
> UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec this sp as "DOMAIN\UserName"
> If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> that user exec this sp as "dbo".
> What is going on? This started after updating SQL 2005 to SP2.
> Thanks
> Stephen
>|||> Cannot use the OUTPUT option when passing a constant to a Stored Procedure
I can reproduce this error message with the following test script:
EXEC dbo.test 1 OUT
So it would seem that your application is doing something similar; a
constant cannot be passed as an output parameter.
> In SQL 2005 SP2, I check Profiler, here are my findings:
> UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec
> this sp as "dbo"
> UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec this sp as "DOMAIN\UserName"
> If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> that user exec this sp as "dbo".
> What is going on? This started after updating SQL 2005 to SP2.
I don't see how this information is related to the subject of this post but
this is expected behavior. Administrators are SQL Server sysadmin role
members by default and are therefore the "dbo" user in all databases.
Non-sysadmin role members access databases under their normal database user
security context.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
>I am having a very difficult problem.
> I get the following error:
> Microsoft SQL Native Client
> Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> In SQL 2005 SP2, I check Profiler, here are my findings:
> UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec
> this sp as "dbo"
> UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec this sp as "DOMAIN\UserName"
> If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> that user exec this sp as "dbo".
> What is going on? This started after updating SQL 2005 to SP2.
> Thanks
> Stephen
>|||Yes, I will post it in about 2 hours.
Thanks
Stephen
"Andrew J. Kelly" wrote:
> Can you tell us more about how exactly you are calling this sp? Can you
> show the actual code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
> >I am having a very difficult problem.
> > I get the following error:
> > Microsoft SQL Native Client
> > Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> >
> > In SQL 2005 SP2, I check Profiler, here are my findings:
> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec
> > this sp as "dbo"
> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec this sp as "DOMAIN\UserName"
> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> > that user exec this sp as "dbo".
> > What is going on? This started after updating SQL 2005 to SP2.
> > Thanks
> > Stephen
> >
>|||Dan, I am sorry but I don't understand your reply.
I listed the information because that is all I know about how I can get this
sp to work... by giving my ADUsers "Domain Admin" rights.
So is it an sp issue or a permission problem? Sorry for not understanding
your reply.
Here is the sp:
USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cf_data_info]
@.table_name varchar(128),
@.column_name varchar(128),
@.data_type varchar(128) OUTPUT,
@.colprecision integer OUTPUT,
@.colscale integer OUTPUT
AS
SELECT @.data_type = systypes.name,
@.colprecision = syscolumns.prec,
@.colscale = syscolumns.scale
FROM sysobjects
LEFT OUTER JOIN syscolumns
ON syscolumns.id = sysobjects.id
LEFT OUTER JOIN systypes
ON systypes.xusertype = syscolumns.xusertype
INNER JOIN sysusers
ON sysusers.uid = sysobjects.uid
AND Upper(sysusers.name) = 'DBO'
WHERE sysobjects.name = @.table_name
AND syscolumns.name = @.column_name
Everything worked fine before upgrading to SQL 2005 SP2.
Thanks
Stephen
"Dan Guzman" wrote:
> > Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> I can reproduce this error message with the following test script:
> EXEC dbo.test 1 OUT
> So it would seem that your application is doing something similar; a
> constant cannot be passed as an output parameter.
> > In SQL 2005 SP2, I check Profiler, here are my findings:
> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec
> > this sp as "dbo"
> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec this sp as "DOMAIN\UserName"
> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> > that user exec this sp as "dbo".
> > What is going on? This started after updating SQL 2005 to SP2.
> I don't see how this information is related to the subject of this post but
> this is expected behavior. Administrators are SQL Server sysadmin role
> members by default and are therefore the "dbo" user in all databases.
> Non-sysadmin role members access databases under their normal database user
> security context.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
> >I am having a very difficult problem.
> > I get the following error:
> > Microsoft SQL Native Client
> > Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> >
> > In SQL 2005 SP2, I check Profiler, here are my findings:
> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec
> > this sp as "dbo"
> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec this sp as "DOMAIN\UserName"
> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> > that user exec this sp as "dbo".
> > What is going on? This started after updating SQL 2005 to SP2.
> > Thanks
> > Stephen
> >
>|||> So is it an sp issue or a permission problem? Sorry for not understanding
> your reply.
I think you are getting this error because the actual call to this procedure
is not formed correctly. For example, the code below attempts to pass
literals as OUTPUT parameters:
DECLARE
@.data_type sysname,
@.colprecision int,
@.colscale int
EXEC [dbo].[cf_data_info]
@.table_name = 'MyTable',
@.column_name = 'MyColumn',
@.data_type = 'test' OUTPUT,
@.colprecision = 4 OUTPUT,
@.colscale = 2 OUTPUT
I can't explain how permissions might come into play here. Does the
profiler trace show the execute statement?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:706C9BEF-9A37-4EBA-9FE2-7B58B5B781DA@.microsoft.com...
> Dan, I am sorry but I don't understand your reply.
> I listed the information because that is all I know about how I can get
> this
> sp to work... by giving my ADUsers "Domain Admin" rights.
> So is it an sp issue or a permission problem? Sorry for not understanding
> your reply.
> Here is the sp:
> USE [Database]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER PROCEDURE [dbo].[cf_data_info]
> @.table_name varchar(128),
> @.column_name varchar(128),
> @.data_type varchar(128) OUTPUT,
> @.colprecision integer OUTPUT,
> @.colscale integer OUTPUT
> AS
> SELECT @.data_type = systypes.name,
> @.colprecision = syscolumns.prec,
> @.colscale = syscolumns.scale
> FROM sysobjects
> LEFT OUTER JOIN syscolumns
> ON syscolumns.id = sysobjects.id
> LEFT OUTER JOIN systypes
> ON systypes.xusertype = syscolumns.xusertype
> INNER JOIN sysusers
> ON sysusers.uid = sysobjects.uid
> AND Upper(sysusers.name) = 'DBO'
> WHERE sysobjects.name = @.table_name
> AND syscolumns.name = @.column_name
> Everything worked fine before upgrading to SQL 2005 SP2.
> Thanks
> Stephen
> "Dan Guzman" wrote:
>> > Cannot use the OUTPUT option when passing a constant to a Stored
>> > Procedure
>> I can reproduce this error message with the following test script:
>> EXEC dbo.test 1 OUT
>> So it would seem that your application is doing something similar; a
>> constant cannot be passed as an output parameter.
>> > In SQL 2005 SP2, I check Profiler, here are my findings:
>> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
>> > exec
>> > this sp as "dbo"
>> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that
>> > user
>> > exec this sp as "DOMAIN\UserName"
>> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler
>> > reports
>> > that user exec this sp as "dbo".
>> > What is going on? This started after updating SQL 2005 to SP2.
>> I don't see how this information is related to the subject of this post
>> but
>> this is expected behavior. Administrators are SQL Server sysadmin role
>> members by default and are therefore the "dbo" user in all databases.
>> Non-sysadmin role members access databases under their normal database
>> user
>> security context.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
>> news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
>> >I am having a very difficult problem.
>> > I get the following error:
>> > Microsoft SQL Native Client
>> > Cannot use the OUTPUT option when passing a constant to a Stored
>> > Procedure
>> >
>> > In SQL 2005 SP2, I check Profiler, here are my findings:
>> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
>> > exec
>> > this sp as "dbo"
>> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that
>> > user
>> > exec this sp as "DOMAIN\UserName"
>> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler
>> > reports
>> > that user exec this sp as "dbo".
>> > What is going on? This started after updating SQL 2005 to SP2.
>> > Thanks
>> > Stephen
>> >|||The issue is that the reference to the output needs to be declared in the
execution.
The error is in your execute call "EXEC dbo.test 1 OUT"
Declare @.InAndOutParammeterName
Select @.InAndOutParammeterName=1
EXEC dbo.test @.InAndOutParammeterName OUT
@.InAndOutParammeterName now holds the reference in memory for the returned
value to reside.
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:706C9BEF-9A37-4EBA-9FE2-7B58B5B781DA@.microsoft.com...
> Dan, I am sorry but I don't understand your reply.
> I listed the information because that is all I know about how I can get
> this
> sp to work... by giving my ADUsers "Domain Admin" rights.
> So is it an sp issue or a permission problem? Sorry for not understanding
> your reply.
> Here is the sp:
> USE [Database]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER PROCEDURE [dbo].[cf_data_info]
> @.table_name varchar(128),
> @.column_name varchar(128),
> @.data_type varchar(128) OUTPUT,
> @.colprecision integer OUTPUT,
> @.colscale integer OUTPUT
> AS
> SELECT @.data_type = systypes.name,
> @.colprecision = syscolumns.prec,
> @.colscale = syscolumns.scale
> FROM sysobjects
> LEFT OUTER JOIN syscolumns
> ON syscolumns.id = sysobjects.id
> LEFT OUTER JOIN systypes
> ON systypes.xusertype = syscolumns.xusertype
> INNER JOIN sysusers
> ON sysusers.uid = sysobjects.uid
> AND Upper(sysusers.name) = 'DBO'
> WHERE sysobjects.name = @.table_name
> AND syscolumns.name = @.column_name
> Everything worked fine before upgrading to SQL 2005 SP2.
> Thanks
> Stephen
> "Dan Guzman" wrote:
>> > Cannot use the OUTPUT option when passing a constant to a Stored
>> > Procedure
>> I can reproduce this error message with the following test script:
>> EXEC dbo.test 1 OUT
>> So it would seem that your application is doing something similar; a
>> constant cannot be passed as an output parameter.
>> > In SQL 2005 SP2, I check Profiler, here are my findings:
>> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
>> > exec
>> > this sp as "dbo"
>> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that
>> > user
>> > exec this sp as "DOMAIN\UserName"
>> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler
>> > reports
>> > that user exec this sp as "dbo".
>> > What is going on? This started after updating SQL 2005 to SP2.
>> I don't see how this information is related to the subject of this post
>> but
>> this is expected behavior. Administrators are SQL Server sysadmin role
>> members by default and are therefore the "dbo" user in all databases.
>> Non-sysadmin role members access databases under their normal database
>> user
>> security context.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
>> news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
>> >I am having a very difficult problem.
>> > I get the following error:
>> > Microsoft SQL Native Client
>> > Cannot use the OUTPUT option when passing a constant to a Stored
>> > Procedure
>> >
>> > In SQL 2005 SP2, I check Profiler, here are my findings:
>> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
>> > exec
>> > this sp as "dbo"
>> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that
>> > user
>> > exec this sp as "DOMAIN\UserName"
>> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler
>> > reports
>> > that user exec this sp as "dbo".
>> > What is going on? This started after updating SQL 2005 to SP2.
>> > Thanks
>> > Stephen
>> >