error 1813 with xp_cmdshell

Hi all,
I am trying to launch a xp_cmdshell from a stored procedure to execute a BCP
.
This error occured when my sp is called :
System.Web.HttpUnhandledException: Exception of type
System.Web.HttpUnhandledException was thrown. -->
System.Data.SqlClient.SqlException: A severe error occurred on the current
command.
The results, if any, should be discarded. xp_cmdshell failed to execute,
error 1813 occured while executing CreateProcessAsUserW.
I use SQLServer 2000 SP4 and a system account to launch the xp_cmdshell. The
SQL server agent log on with this same account.
Any idea?
I will appreciate any help.
Regards.
LGHi,
xp_cmdshell executes with the security contect of the SQL Agent Proxy
account when executed by non symin users. This requires you to allow
non-symin users to execute xp_cmdshell (uncheck the 'Only users with
symin privileges...' checkbox under SQL Server Agent properties --> Job
System) and specify a Windows account for the SQL Agent proxy with the
permissions needed to run you application).
See the below URL:-
http://support.microsoft.com/defaul...b;en-us;Q264155
Thanks
Hari
SQL Server MVP
"Laurent G" <LaurentG@.discussions.microsoft.com> wrote in message
news:13FBAEA9-A146-49E0-A42C-1B939CC3DA06@.microsoft.com...
> Hi all,
> I am trying to launch a xp_cmdshell from a stored procedure to execute a
> BCP.
> This error occured when my sp is called :
> System.Web.HttpUnhandledException: Exception of type
> System.Web.HttpUnhandledException was thrown. -->
> System.Data.SqlClient.SqlException: A severe error occurred on the current
> command.
> The results, if any, should be discarded. xp_cmdshell failed to execute,
> error 1813 occured while executing CreateProcessAsUserW.
> I use SQLServer 2000 SP4 and a system account to launch the xp_cmdshell.
> The
> SQL server agent log on with this same account.
> Any idea?
> I will appreciate any help.
> Regards.
> LG|||Thanks a lot Hari, it works now.
"Hari Pra" wrote:

> Hi,
> xp_cmdshell executes with the security contect of the SQL Agent Proxy
> account when executed by non symin users. This requires you to allow
> non-symin users to execute xp_cmdshell (uncheck the 'Only users with
> symin privileges...' checkbox under SQL Server Agent properties --> Job
> System) and specify a Windows account for the SQL Agent proxy with the
> permissions needed to run you application).
> See the below URL:-
> http://support.microsoft.com/defaul...b;en-us;Q264155
>
> Thanks
> Hari
> SQL Server MVP
> "Laurent G" <LaurentG@.discussions.microsoft.com> wrote in message
> news:13FBAEA9-A146-49E0-A42C-1B939CC3DA06@.microsoft.com...
>
>

Error 17805 Invalid buffer received from Client when executing stored Procedure

Hello,
I am receiving this error when I try to execute a Stored Procedure in
SQL Server 2000 using SYBASE dblib.
I have been able to connect to connect to SQL Server from Unix, but
when I try to execute a Stored Procedure via dblib, I get the error:
"Error 17805 Invalid buffer received from Client"
My Stored Procedure does not receive parameters, so I don't know what
is happening.
If I execute a simple SQL Statement, it works fine
I have a SQL Server 2000 running SP 4.
anyone can help?
Thank you in advanced
Jose Manuel
Jose Manuel wrote:
> Hello,
> I am receiving this error when I try to execute a Stored Procedure in
> SQL Server 2000 using SYBASE dblib.
> I have been able to connect to connect to SQL Server from Unix, but
> when I try to execute a Stored Procedure via dblib, I get the error:
> "Error 17805 Invalid buffer received from Client"
> My Stored Procedure does not receive parameters, so I don't know what
> is happening.
> If I execute a simple SQL Statement, it works fine
> I have a SQL Server 2000 running SP 4.
> anyone can help?
> Thank you in advanced
> Jose Manuel
Just curious: Why are you executing SQL on SQL Server 2000 usin the
Sybase dbLib instead of a SQL Server client library?
David Gugick
Imceda Software
www.imceda.com
|||Jose Manuel,
I am having exactlly the same problem as you had.
Could you please tell me how did you solve it?
Thanks
creo que voy varios pasos atras de ti. ojal me puedas ayudar. tengo el
mismo problema.
te ha contactado alguien ?
lo pudiste resolver?
gracias.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Error 16916: A cursor with the name "tentative_cursor" does not exist

Hi,

I am creating a stored procedure which contains the declaration of a cursor, fetch the cursor, close the cursor, deallocate cursor. I tried executing this code in SQL analyzer and found working. But when I use the same code inside a create stored procedure, it throws the following error message.

Error 16916: A cursor with the name "tentative_cursor" does not exist

As a result, I am not able to proceed in creation of stored procedure with the above SQL statements.

Can any one help me where the problem is..

My SQL statements..
---
DECLARE tentative_cursor CURSOR
FOR select * from TbTentativeBookingTable where ExpTime < GETDATE()
GO
OPEN tentative_cursor
GO
DECLARE @.transactionno BIGINT
...
...
FETCH ...
CLOSE tentative_cursor
---

Thanks
-SudhakarThe GO keyword serves to terminate a SQL batch, so it implicitly ends a stored procedure declaration. There may be other problems too, but at the very least you need to remove the GO keyword(s).

-PatP

Error 156 Creating View

I tried writing the following statement in a stored procedure, and I get an
Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'."
CREATE VIEW VW_TotalAccepted
AS
SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total]
FROM [DelDetail]
GROUP BY [DelDetail].[CustID], [DelDetail].[ReqNo]
What's wrong with the syntax? Isn't it the way to create a View?You cannot create a VIEW inside a SP
"wrytat" wrote:

> I tried writing the following statement in a stored procedure, and I get a
n
> Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'."
> CREATE VIEW VW_TotalAccepted
> AS
> SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total]
> FROM [DelDetail]
> GROUP BY [DelDetail].[CustID], [DelDetail].[ReqNo]
> What's wrong with the syntax? Isn't it the way to create a View?|||Then... Just ask a stupid question regarding View. If I add a new row or
change some values of some columns in a table that is included in a View
after creating the View, will the change be reflected in the View?
"Chandra" wrote:
> You cannot create a VIEW inside a SP
> "wrytat" wrote:
>|||Yes. A view doesn't store any data, it is only a "view" into the data stored
in your tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:B14BFAD9-CF63-4645-A61C-86AA8854F4D1@.microsoft.com...
> Then... Just ask a stupid question regarding View. If I add a new row or
> change some values of some columns in a table that is included in a View
> after creating the View, will the change be reflected in the View?
> "Chandra" wrote:
>|||Yes. View doesnt store any data. Selecting rows from a view is as good as
executing the query that created it.
We go for views because, it improves the performance of a query.
thanks and regards
Chandra
"wrytat" wrote:
> Then... Just ask a stupid question regarding View. If I add a new row or
> change some values of some columns in a table that is included in a View
> after creating the View, will the change be reflected in the View?
> "Chandra" wrote:
>

Error 15274: Access to the remote server is denied because the current security context is not t

Hi,

I have a stored procedure servicing a queue (sql 2005).

The stored procedure updates some data in a different server and database.

If the queue contains messages and I execute manually the stored procedure, I don't have any problem at all.

However, if I set the queue to use the same stored procedure, it gives me the following error:

Error 15274: Access to the remote server is denied because the current security context is not trusted.

Any ideas?

Thanks,

Aron

Please, see if the following link to a similar question on this forum has the answer for your particular scenario: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1064271&SiteID=1

If the answers in this thread do not help you to resolve the problem, please let us know and we will be glad to provide further assistance.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Error 1429: A server cursor cannot be opened...

Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too)..

We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed.

This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute.

Once in a while we will get a return from SQL ..

"OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor..."

The OLE error code is 1429. An OLE Exception code 3604 is also returned.

When this happens the SP will return the same error when executed for the same parameters over and over when called from VFP. When called directly from SQL management console it will normally work for the same parameters, although once in a while it will just hang (and not timeout apparently). In that case it will also hang from SQLCMD command line utility as well.

Wait a few hours and the SP will run fine for the same parameters in VFP. This happens even in the middle of the night when there is no possibility that data is being changed.

Here's the really fun part...

Open the SP source for modification (ALTER PROCEDURE) in management console and execute it (no changes at all, just let it recompile). Immediately it will work fine when called with the same parameters called from VFP or anywhere else (even if it was one of the rare instances where it hung in management console). This works EVERY TIME.

Sooo... I edited and executed the SP with the WITH RECOMPILE option assuming that that should do the trick (same as alter procedure/executing from management console right?). NOPE. Same problems. In order to work around the problem when the error occurs, I HAVE TO alter procedure and execute the code from management console.

Help?

Bill Kuhn - MCSE

The Kuhn Group, Inc.

http://www.kuhngroup.com

Did you deallocate your cursors in your code ? Would be nice to see the skeleton code, not the whole one, but the pure cursor code you implemented.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Yes - both cursors are closed and deallocated

Following is the entire stored procedure. The cursors are curs_Exams_pkeys and curs_Raw_CompData ..

ALTER procedure [dbo].[Comp_Data_for_Person]

@.persons_pkey int,

@.Latest_Date datetime

with recompile

as

SET NOCOUNT ON

declare @.ExamCount smallint

declare @.TopExam_pkey int

declare @.Exam1pkey int, @.Exam2pkey int, @.Exam3pkey int, @.Exam4pkey int, @.Exam5pkey int, @.Exam6pkey int, @.This_Exam_pkey int

declare @.ExamIndex tinyint

declare @.CurrentClients_pkey smallint

declare @.CurrentPanel smallint

-- following vars are used when FETCHing data from curs_RAW_CompData

declare @.Session_date datetime, @.TestNumber int, @.DataType tinyint, @.Result varchar(65),@.Description varchar(200), @.Class varchar(100), @.PrintFlag char(1), @.Formatted varchar(200),@.NewFlag char(1),@.CheckText varchar(200),@.PanelTestGroup varchar(200),@.PrintLevel int, @.Exams_pkey int

declare @.Current_TestNumber int, @.Current_Session_date datetime

if @.Latest_Date is null

set @.Latest_Date = '12/31/2099'

set transaction isolation level read uncommitted -- added by wsk 8/14/2006 to see if this helps

-- make table of exams that will print on this report

create table #ExamTable (session_date datetime,van smallint,name varchar(60),exams_pkey int,persons_pkey int, clients_pkey int,Questionnaire_Title varchar(254),panel smallint)

set @.ExamCount =

(select count(*) from exams join testsession on exams.testsession_pkey=testsession.pkey where persons_pkey=@.persons_pkey and session_date<=@.Latest_Date)

if @.ExamCount <7

insert into #ExamTable

select testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

(select top 1 isnull(description,'') from translationcodes where testnumber=1000000000 and value=(select top 1 isnull(cast(result as int),0) from documentdata where exams_pkey=exams.pkey and testnumber=1000000000 and datatype=11)) as Questionnaire_Title, exams.panel

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date desc

else

insert into #ExamTable

select * from (select top 5 testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

(select top 1 isnull(description,'') from translationcodes where testnumber=1000000000 and value=(select top 1 isnull(cast(result as int),0) from documentdata where exams_pkey=exams.pkey and testnumber=1000000000 and datatype=11)) as Questionnaire_Title, exams.panel

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date desc) as d1

union

select * from (select top 1 testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

(select top 1 isnull(description,'') from translationcodes where testnumber=1000000000 and value=(select top 1 isnull(cast(result as int),0) from documentdata where exams_pkey=exams.pkey and testnumber=1000000000 and datatype=11)) as Questionnaire_Title, exams.panel

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date) as d2

order by session_date desc

-- output list of exams in this data

select * from #ExamTable

--@.TopExam_pkey is key to latest exam for this person - this exam governs what testnumbers are printed in compdata

set @.TopExam_pkey = (select top 1 exams_pkey from #ExamTable order by session_date desc)

set @.CurrentClients_pkey = (select top 1 clients_pkey from #ExamTable order by session_date desc)

set @.CurrentPanel = (select top 1 panel from #ExamTable order by session_date desc)

-- get session_dates for all exams that will be reported on

set @.ExamIndex = 1

declare curs_Exams_pkeys cursor STATIC for select exams_pkey from #ExamTable order by session_date

open curs_Exams_pkeys

fetch next from curs_Exams_pkeys into @.This_Exam_pkey

while @.@.FETCH_STATUS = 0

begin

if @.ExamIndex = 1

set @.Exam1pkey = @.This_Exam_pkey

else if @.ExamIndex = 2

set @.Exam2pkey = @.This_Exam_pkey

else if @.ExamIndex = 3

set @.Exam3pkey = @.This_Exam_pkey

else if @.ExamIndex = 4

set @.Exam4pkey = @.This_Exam_pkey

else if @.ExamIndex = 5

set @.Exam5pkey = @.This_Exam_pkey

else if @.ExamIndex = 6

set @.Exam6pkey = @.This_Exam_pkey

set @.ExamIndex = @.ExamIndex + 1

fetch next from curs_Exams_pkeys into @.This_Exam_pkey

end

close curs_Exams_pkeys

deallocate curs_Exams_pkeys

-- output report header info

select dbo.fullnamenormalatexamdate(ex.pkey) as completename,dbo.ssnatexamdate(ex.pkey) as ssn,dbo.justdate(ts.session_date) as testdate,ts.van,ex.pid,ex.panel,

dbo.addressatexamdate(ex.pkey) as address,dbo.citystatezipatexamdate(ex.pkey) as citystatezip,adm.telephone,dbo.justdate(p.dob) as DOB, left(isnull(adm.employment,' '),2) as employmentyears,substring(isnull(adm.employment,' '),3,2) as employmentmonths,

isnull(cladm.payrollnum,'') as payrollnum,isnull(cladm.payrollnumber,'') as payrollnumber,isnull(cladm.jobcode,'') as jobcode,cl.client,cl.clientname,

clloc.location,clloc.description as locationdescription,dbo.genderatexamdate(ex.pkey) as gender,dbo.ageatexamdate(ex.pkey) as age,

isnull(cladm.memberssn,'') as memberssn, isnull(cladm.employeeid,'') as employeeid,

dbo.lastnameatexamdate(ex.pkey) as lastname,

dbo.firstnameatexamdate(ex.pkey) as firstname,

dbo.middlenameatexamdate(ex.pkey) as middlename

from exams ex join testsession ts on ex.testsession_pkey=ts.pkey

join clients cl on cl.pkey=ex.clients_pkey

join clientlocations clloc on ts.clientlocations_pkey=clloc.pkey

join clientadmin cladm on cladm.exams_pkey=ex.pkey

join administrative adm on adm.exams_pkey = ex.pkey

join persons p on ex.persons_pkey=p.pkey

where ex.pkey=@.TopExam_Pkey

create table #ReportTests (testnumber int)

insert into #ReportTests (testnumber)

select distinct medicaldata.testnumber from medicaldata where medicaldata.exams_pkey=@.TopExam_pkey and dbo.get_printflag(medicaldata.testnumber,medicaldata.datatype,medicaldata.result) in ('P','S')

union

select distinct labdata.testnumber from labdata where labdata.exams_pkey=@.TopExam_pkey and dbo.get_printflag(labdata.testnumber,labdata.datatype,labdata.result) in ('P','S')

create table #Final_CompData (TestNumber int, DataType tinyint, Description varchar(200), Class varchar(100), PrintFlag char(1), NewFlag char(1), CheckText varchar(200), PanelTestGroup varchar(200), PrintLevel int, Exam1Result varchar(2000),Exam2Result varchar(2000), Exam3Result varchar(2000), Exam4Result varchar(2000), Exam5Result varchar(2000), Exam6Result varchar(2000))

create index [testnumber_datatype] on #Final_CompData (testnumber,datatype)

declare curs_Raw_CompData cursor STATIC FOR

-- output medical compdata

select ts.session_date,

d1.testnumber,d1.datatype,d1.result,

dbo.testdescription(testnumber) as description,d1.class,dbo.get_printflag(d1.testnumber,d1.datatype,d1.result) as printflag,

rtrim(dbo.formatresult_new(testnumber,datatype,result))+' '+rtrim(dbo.testunits_by_datatype(testnumber,datatype)) as formatted,

d1.checkflag as newflag, d1.checktext,

(select top 1 ptg.testgroup from paneltestgroups ptg join clientpanels cp on ptg.clientpanels_pkey=cp.pkey

join testgroups tg on ptg.testgroup = tg.testgroup_descr

where tg.testnumber=d1.testnumber and cp.clients_pkey=@.CurrentClients_pkey and cp.panel=@.CurrentPanel) as paneltestgroup,

d1.printlevel,d1.exams_pkey

from

((select 'MEDICALDATA' as datatable,m.pkey as datatable_pkey,exams_pkey,m.testnumber,m.datatype,result,

dbo.referencerangecheck(e.pkey,m.testnumber,m.datatype,m.result) as checkflag,

dbo.referencerangetext(e.pkey,m.testnumber,m.datatype,m.result) as checktext,m.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join medicaldata m on m.exams_pkey=e.pkey

join tests on m.testnumber=tests.testnumber and m.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber in (select testnumber from #ReportTests)

)

union

(select 'LABDATA' as datatable,l.pkey as datatable_pkey,exams_pkey,l.testnumber,l.datatype,result,

dbo.referencerangecheck(e.pkey,l.testnumber,l.datatype,l.result) as checkflag,

dbo.referencerangetext(e.pkey,l.testnumber,l.datatype,l.result) as checktext,l.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join labdata l on l.exams_pkey=e.pkey

join tests on l.testnumber=tests.testnumber and l.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber in (select testnumber from #ReportTests)

))

as d1

join testsession ts on ts.pkey=d1.testsession_pkey

order by printlevel,testnumber,session_date -- session_date,van,pid,name,testnumber

open curs_Raw_CompData

fetch next from curs_Raw_CompData into @.Session_date, @.TestNumber, @.DataType, @.Result, @.Description , @.Class, @.PrintFlag, @.Formatted, @.NewFlag,@.CheckText, @.PanelTestGroup, @.PrintLevel, @.Exams_pkey

while @.@.FETCH_STATUS = 0

BEGIN

if @.Exams_pkey = @.TopExam_pkey -- use class,description,checktext,printflag,etc from this one

begin

if exists (select testnumber from #Final_CompData where testnumber=@.TestNumber and datatype = @.Datatype)

begin

update #Final_CompData

set Description = @.Description,Class = @.Class,PrintFlag = @.PrintFlag,NewFlag = @.NewFlag,CheckText = @.CheckText,PanelTestGroup = @.PanelTestGroup,PrintLevel = @.PrintLevel

where testnumber = @.TestNumber and datatype=@.DataType

end

else

begin

insert into #Final_CompData (testnumber,datatype,description,class,printflag,newflag,checktext,paneltestgroup,printlevel)

values (@.testnumber,@.datatype,@.description,@.class,@.printflag,@.newflag,@.checktext,@.paneltestgroup,@.printlevel)

end

end

else -- @.Exams_pkey is not = @.TopExam_pkey - only carry testnumber, datatype, and result info

begin

if not exists (select testnumber from #Final_CompData where testnumber=@.TestNumber and datatype = @.Datatype)

begin

insert into #Final_CompData (testnumber,datatype) values (@.TestNumber,@.DataType)

end

end

-- update correct Exam?Result

if @.Exams_pkey = @.Exam1pkey

begin

update #Final_CompData set Exam1Result = (select rtrim(isnull(exam1result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam1Result = (select rtrim(isnull(exam1result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam1Result = (select rtrim(isnull(exam1result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam2pkey

begin

update #Final_CompData set Exam2Result = (select rtrim(isnull(exam2result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam2Result = (select rtrim(isnull(exam2result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam2Result = (select rtrim(isnull(exam2result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam3pkey

begin

update #Final_CompData set Exam3Result = (select rtrim(isnull(exam3result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam3Result = (select rtrim(isnull(exam3result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam3Result = (select rtrim(isnull(exam3result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam4pkey

begin

update #Final_CompData set Exam4Result = (select rtrim(isnull(exam4result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam4Result = (select rtrim(isnull(exam4result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam4Result = (select rtrim(isnull(exam4result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam5pkey

begin

update #Final_CompData set Exam5Result = (select rtrim(isnull(exam5result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam5Result = (select rtrim(isnull(exam5result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam5Result = (select rtrim(isnull(exam5result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam6pkey

begin

update #Final_CompData set Exam6Result = (select rtrim(isnull(exam6result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam6Result = (select rtrim(isnull(exam6result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam6Result = (select rtrim(isnull(exam6result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

fetch next from curs_Raw_CompData into @.Session_date, @.TestNumber, @.DataType, @.Result, @.Description , @.Class, @.PrintFlag, @.Formatted, @.NewFlag, @.CheckText, @.PanelTestGroup, @.PrintLevel, @.Exams_pkey

END

close curs_Raw_CompData

deallocate curs_Raw_CompData

-- knock off preceeding CR's (ugly way to do it but it works for the moment)

update #Final_CompData

set Exam1Result=substring(Exam1Result,2,len(Exam1Result)-1),

Exam2Result=substring(Exam2Result,2,len(Exam2Result)-1),

Exam3Result=substring(Exam3Result,2,len(Exam3Result)-1),

Exam4Result=substring(Exam4Result,2,len(Exam4Result)-1),

Exam5Result=substring(Exam5Result,2,len(Exam5Result)-1),

Exam6Result=substring(Exam6Result,2,len(Exam6Result)-1)

select * from #Final_CompData where printflag in ('P','S') order by printlevel,testnumber

drop table #Final_CompData

-- output history data

exec dbo.Historydataforexam @.TopExam_pkey

-- output any custom field info from clientadmin table

exec dbo.Get_Custom_ClientAdmin_Fields_TextBlock @.TopExam_pkey

-- get ILO info for ILO report

select ts.session_date,ts.van as van,

dbo.pidAtExamDate(exams_pkey) as pid,

dbo.fullnamenormalatexamdate(exams_pkey) as name,

dbo.genderatexamdate(exams_pkey) as gender,dbo.ageatexamdate(exams_pkey) as age,

d1.testnumber,d1.datatype,

dbo.testdescription(testnumber) as description,d1.class,dbo.get_printflag(d1.testnumber,d1.datatype,d1.result) as printflag,

d1.result,

rtrim(dbo.formatresult_new(testnumber,datatype,result))+' '+rtrim(dbo.testunits_by_datatype(testnumber,datatype)) as formatted,

d1.oldflag,d1.checkflag as newflag, d1.checktext,datatable,datatable_pkey,d1.exams_pkey,

(select top 1 ptg.testgroup from paneltestgroups ptg join clientpanels cp on ptg.clientpanels_pkey=cp.pkey

join testgroups tg on ptg.testgroup = tg.testgroup_descr

where tg.testnumber=d1.testnumber and cp.clients_pkey=@.CurrentClients_pkey and cp.panel=@.CurrentPanel) as paneltestgroup,

d1.printlevel

from

(select 'MEDICALDATA' as datatable,m.pkey as datatable_pkey,exams_pkey,m.testnumber,m.datatype,result,

dbo.referencerangecheck(e.pkey,m.testnumber,m.datatype,m.result) as checkflag,

dbo.referencerangetext(e.pkey,m.testnumber,m.datatype,m.result) as checktext,m.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join medicaldata m on m.exams_pkey=e.pkey

join tests on m.testnumber=tests.testnumber and m.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber between 2261800 and 2264999

)

as d1

join testsession ts on ts.pkey=d1.testsession_pkey

order by printlevel,testnumber,session_date -- session_date,van,pid,name,testnumber

drop table #ExamTable -- should not be needed

drop table #ReportTests -- should not be needed

Error 14053: Cannot load the DLL replincrementlsn extended procedure...

I am setting up a transactional replication between two MSSBS2K servers, both running MSSQL2K. Both are on different domains, but are currently in the same network (until all data is replicated, then the subscribe will move to a remote site and pull changes periodically).

Everything is working on the publisher/distributor side, but when I attempt to subscribe to the distributor, I get the following message:

Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
The subscription could not be updated at this time.
The subscription could not be created.

Has anyone experienced this? I have scoured the Internet for this error, and have come up basically empty. All I found either suggest checking for the existence of the sp_replincrementlsn and sp_replincrementlsn_internal (both of which exist) or suggest an incomplete SP install. Neither of these is the case.

Personally, I tend to suspect a permissions issue when trying to launch the appropriate DLLs is generating this less-than-intuitive error message, but I cannot determine how to go about resolving this. I am using SQL authentication to connect, and both servers use local (to their domain) non-system accounts to log in. This works fine for launching queries across the servers. Could there be a deeper permissions issue at play here?

I'm at a dead end, and I need to get this issue resolved ASAP, as this server needs to be deployed yesterday. Any help you can provide will be most appreciated!Did you have any options on the install of SQL Server, or was it all a big black box install courtesy of Microsoft? On the surface, I would suspect that replication was not installed on the SQL Server. Do a search on your box for xprepl.dll. Let us know what comes back.|||Yes, xprepl.dll is installed in the C:\Program Files\Microsoft SQL Server\MSSQL\Binn folder.

This install is a Windows Small Business Server 2000 install that I performed personally. I installed MS SQL based on the standard defaults. Just to be sure, though, I just now re-ran the installer and verified that the "Replication" checkbox is checked by default.

Did you have any options on the install of SQL Server, or was it all a big black box install courtesy of Microsoft? On the surface, I would suspect that replication was not installed on the SQL Server. Do a search on your box for xprepl.dll. Let us know what comes back.|||Update: Never could get past that error. Went with merge replication instead of transactional replication and it works fine. Only caveat is that this did require I move the new server under the same domain as the publisher/distributor server (rather than as a separate domain in the same forest). Small price to pay for getting this replication up and going.

error 1012: the correlation name % has the same exposed name as table %.

Im trying to find the error in this statement:

CREATE PROCEDURE STP_selectmain

AS

select a.inventoryid, b.firstname, b.lastname, art.title, art.medium,
a.cost, a.inventoryid, a.receivedate, a.dimensions,
a.reference, art.provenance, sum(c.restorationcost),
sum(d.framingcost), sum(e.cost)
from art as a left outer join artist as b on a.artistid =
b.artistid,
a left outer join restoration as c on a.inventoryid =
c.inventoryid,
a left outer join outframing as d on a.inventoryid =
d.inventoryid,
a left outer join basiccosts as e on a.inventoryid =
e.inventoryid

group by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,
a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,
a.provenance
order by a.inventoryid desc
GO

eveytime I do a syntax check on it I get this error.

error 1012: the correlation name 'a' has the same exposed name as
table 'a'.

Whats the syntax to fix this?

thanks

-JimJim (jim.ferris@.motorola.com) writes:
> select a.inventoryid, b.firstname, b.lastname, art.title, art.medium,
> a.cost, a.inventoryid, a.receivedate, a.dimensions,
> a.reference, art.provenance, sum(c.restorationcost),
> sum(d.framingcost), sum(e.cost)
> from art as a left outer join artist as b on a.artistid =
> b.artistid,
> a left outer join restoration as c on a.inventoryid =
> c.inventoryid,
> a left outer join outframing as d on a.inventoryid =
> d.inventoryid,
> a left outer join basiccosts as e on a.inventoryid =
> e.inventoryid
> group by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,
> a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,
> a.provenance
> order by a.inventoryid desc
> GO
>...
> error 1012: the correlation name 'a' has the same exposed name as
> table 'a'.
> Whats the syntax to fix this?

You have a mix of old and new FROM syntax, and I would suppose that
you mean:

FROM art AS a
LEFT JOIN artist AS b ON a.artistid = b.artistid
LEFT JOIN restoration AS c ON a.inventoryid = c.inventoryid
LEFT JOIN outframing AS d ON a.inventoryid = d.inventoryid
LEFT JOIN asiccosts AS e on a.inventoryid = e.inventoryid

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Error 0: Syntax error or Access violation

Hi,
I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.

CREATE PROC CabsSchedule_Insert
{
@.JulianDatesmallint,
@.SiteCodesmallint,
@.CalendarDaysmallint,
@.BillPeriodsmallint,
@.WorkDaysmallint,
@.CalDayBillRcvd varchar(30),
@.Remarksvarchar(50)
}
 AS
INSERT INTO CabsSchedule
(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@.JulianDate, @.SiteCode, @.CalendarDay, @.BillPeriod, @.WorkDay, @.CalDayBillRcvd, @.Remarks)

Thanks,check the datatypes of the columns and the values that you are trying to put into them. other than that i dont see anything else. you might also want tocheck this if you havent already.

hth|||Hi Dinakar,
Thanks for the reply. I got this thing to work. I'm not really sure how SQL Server behaves if I had an extra space inbetween my proc but removing the flower brackets ({) and
adjusting the CabsSchedule in the insert statement right beside the field names did the trick.

CREATE PROC CabsSchedule_Insert
@.JulianDatesmallint,
@.SiteCodesmallint,
@.CalendarDaysmallint,
@.BillPeriodsmallint,
@.WorkDaysmallint,
@.CalDayBillRcvd varchar(30),
@.Remarksvarchar(50)
 AS
INSERT INTO
CabsSchedule(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@.JulianDate, @.SiteCode, @.CalendarDay, @.BillPeriod, @.WorkDay, @.CalDayBillRcvd, @.Remarks)

Thanks again!

Error : "Parallel worker query thread was involved in a deadlock" in SQL Se

We are running four session of application and each session is running
the same stored procedure against SQL Server database running SP3
patch.
All these four session are doing data intense operation and after some
time three of the process are getting completed correctly and the
fourth give us an DeadLock error message.
The error message which is displayed in the application is normal SQL
Server deadlock message.
But in the profiler we are seeing the below mentioned error message
//
Lock : Deadlock Chain : Parallel worker query thread was involved in a
deadlock" in profiler
//
We know that this error is cause by SQL Server optimization
methodology, we know it has to do with
parallel threads deadlocking each other.
Now we are looking for any documentation or information from the
Microsoft about this, any info will be appreciated.
Regards
Basharat<basharatw@.hotmail.com> wrote in message
news:1115667066.094653.87860@.f14g2000cwb.googlegroups.com...
> We are running four session of application and each session is running
> the same stored procedure against SQL Server database running SP3
> patch.
> All these four session are doing data intense operation and after some
> time three of the process are getting completed correctly and the
> fourth give us an DeadLock error message.
> The error message which is displayed in the application is normal SQL
> Server deadlock message.
> But in the profiler we are seeing the below mentioned error message
> //
> Lock : Deadlock Chain : Parallel worker query thread was involved in a
> deadlock" in profiler
> //
> We know that this error is cause by SQL Server optimization
> methodology, we know it has to do with
> parallel threads deadlocking each other.
> Now we are looking for any documentation or information from the
> Microsoft about this, any info will be appreciated.
> Regards
> Basharat
>
Take a look at the "max degree of parallelism" and "maxdop" query hints in
the books online.
I don't know if it will help in this situation or not.
Rick Sawtell
MCT, MCSD, MCDBA|||> We are running four session of application and each session is running
> the same stored procedure against SQL Server database running SP3
> patch.
> All these four session are doing data intense operation and after some
> time three of the process are getting completed correctly and the
> fourth give us an DeadLock error message.
> The error message which is displayed in the application is normal SQL
> Server deadlock message.
> But in the profiler we are seeing the below mentioned error message
> //
> Lock : Deadlock Chain : Parallel worker query thread was involved in a
> deadlock" in profiler
> //
> We know that this error is cause by SQL Server optimization
> methodology, we know it has to do with
> parallel threads deadlocking each other.
That sounds exactly l ike the problem I was having about two months ago on a
2-cpu server.
There is a MAXDOP option you can specify to set the "maximum degree of
parallelism" on a given SQL statement. I can't remember the sp that we had
to tweak that way, and I couldn't find it in the help index. I believe I
found it by dowing a full text search of BOL. For us, the solution was to
provide the MAXDOP hint and set it to 1 (I think).
If you can get to a source with more than a 3 month history of this NG, you
could search for posts containing MAXDOP from a thread that I started
between 2 & three months ago.
Perhaps someone else can chime in with something more precise.
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei
<basharatw@.hotmail.com> wrote in message
news:1115667066.094653.87860@.f14g2000cwb.googlegroups.com...
> Now we are looking for any documentation or information from the
> Microsoft about this, any info will be appreciated.
> Regards
> Basharat
>

Error - procedure expects parameter, which was not supplied

Hi,

I created a stored procedure in the sql server. I try to insert a record from the aspx page. But I keep getting this error,
"procedure expects parameter <@.firstname>, which was not supplied". This is what I am doing.

cmd.CommandText = "proc_insertuser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.firstname", SqlDbType.VarChar, 50);
cmd.Parameters["@.firstname"].Value = txtFirstName.Text
...other parameters

I checked the parameter names, it matches correctly. The parameter does get added to the collection. I checked it using cmd.Parameters.Contains("@.firstname") and the value is also correct. Using query analyzer I executed the procedure, I am able to insert a record.

Can anyone tell me what could be the problem.
TAICan you use SQL Profiler? Perhaps that will help diagnose the problem. Is there some other parameter that is missing and being mis-reported?|||if you are using VB.NET I would try replacing your brackets surrounding your parameter name w/ parenthesis instead.

 cmd.Parameters("@.firstname").Value = txtFirstName.Text

HTH

Sandy

Error - help

Hi,
Something is wrong with this:
CREATE PROCEDURE Admin_LogError
AS
BEGIN
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
INSERT INTO Admin_Errors
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
END
Error:
Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
The name "ErrorNumber" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.This is not how TSQL work. You first do a SELECT of what the function returns, returning that as a
result set to the client, then you refer to just the word ERRORNUMBER in the insert statement, where
that word has no correlation to the prior SELECT. Skip the SELECT, hand do the INSERT like:
INSERT INTO tblname(col1, col2, col3...) --Always specify column names
VALUES(ERRORNUMBER(), ERRORSEVERITY(), ...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>|||Your error is here:
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
Hint: that should be ERROR_NUMBER() not ErrorNumber
The select statement does not assign values, it selects them.
If you need to store the error information so that you can use it later you
will need to do things a bit differently within your stored procedure
DECLARE @.errNum int
SELECT @.errNum = ERROR_NUMBER()
Now you can use @.errNum at other parts of the stored procedure.
--
Keith Kratochvil
<tootsuite@.gmail.com> wrote in message
news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>|||Thanks all - this was an oversight on my part - I borrowed this code
from somewhere else - wrong context
Keith Kratochvil wrote:
> Your error is here:
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> Hint: that should be ERROR_NUMBER() not ErrorNumber
> The select statement does not assign values, it selects them.
> If you need to store the error information so that you can use it later you
> will need to do things a bit differently within your stored procedure
> DECLARE @.errNum int
> SELECT @.errNum = ERROR_NUMBER()
> Now you can use @.errNum at other parts of the stored procedure.
> --
> Keith Kratochvil
>
> <tootsuite@.gmail.com> wrote in message
> news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> > Hi,
> >
> > Something is wrong with this:
> >
> > CREATE PROCEDURE Admin_LogError
> > AS
> > BEGIN
> >
> > SELECT
> > ERROR_NUMBER() AS ErrorNumber,
> > ERROR_SEVERITY() AS ErrorSeverity,
> > ERROR_STATE() as ErrorState,
> > ERROR_PROCEDURE() as ErrorProcedure,
> > ERROR_LINE() as ErrorLine,
> > ERROR_MESSAGE() as ErrorMessage;
> >
> > INSERT INTO Admin_Errors
> > VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> > ErrorLine, ErrorMessage, GETDATE())
> >
> > END
> >
> > Error:
> >
> > Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> > The name "ErrorNumber" is not permitted in this context. Valid
> > expressions are constants, constant expressions, and (in some contexts)
> > variables. Column names are not permitted.
> >

Error - help

Hi,
Something is wrong with this:
CREATE PROCEDURE Admin_LogError
AS
BEGIN
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
INSERT INTO Admin_Errors
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
END
Error:
Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
The name "ErrorNumber" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.This is not how TSQL work. You first do a SELECT of what the function return
s, returning that as a
result set to the client, then you refer to just the word ERRORNUMBER in the
insert statement, where
that word has no correlation to the prior SELECT. Skip the SELECT, hand do t
he INSERT like:
INSERT INTO tblname(col1, col2, col3...) --Always specify column names
VALUES(ERRORNUMBER(), ERRORSEVERITY(), ...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1160071938.581188.140530@.k70g2000cwa.googlegroup
s.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>|||Your error is here:
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
Hint: that should be ERROR_NUMBER() not ErrorNumber
The select statement does not assign values, it selects them.
If you need to store the error information so that you can use it later you
will need to do things a bit differently within your stored procedure
DECLARE @.errNum int
SELECT @.errNum = ERROR_NUMBER()
Now you can use @.errNum at other parts of the stored procedure.
Keith Kratochvil
<tootsuite@.gmail.com> wrote in message
news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>|||Thanks all - this was an oversight on my part - I borrowed this code
from somewhere else - wrong context
Keith Kratochvil wrote:[vbcol=seagreen]
> Your error is here:
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> Hint: that should be ERROR_NUMBER() not ErrorNumber
> The select statement does not assign values, it selects them.
> If you need to store the error information so that you can use it later yo
u
> will need to do things a bit differently within your stored procedure
> DECLARE @.errNum int
> SELECT @.errNum = ERROR_NUMBER()
> Now you can use @.errNum at other parts of the stored procedure.
> --
> Keith Kratochvil
>
> <tootsuite@.gmail.com> wrote in message
> news:1160071938.581188.140530@.k70g2000cwa.googlegroups.com...

Error - help

Hi,
Something is wrong with this:
CREATE PROCEDURE Admin_LogError
AS
BEGIN
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
INSERT INTO Admin_Errors
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
END
Error:
Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
The name "ErrorNumber" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.
This is not how TSQL work. You first do a SELECT of what the function returns, returning that as a
result set to the client, then you refer to just the word ERRORNUMBER in the insert statement, where
that word has no correlation to the prior SELECT. Skip the SELECT, hand do the INSERT like:
INSERT INTO tblname(col1, col2, col3...) --Always specify column names
VALUES(ERRORNUMBER(), ERRORSEVERITY(), ...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1160071938.581188.140530@.k70g2000cwa.googlegr oups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>
|||Your error is here:
VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
ErrorLine, ErrorMessage, GETDATE())
Hint: that should be ERROR_NUMBER() not ErrorNumber
The select statement does not assign values, it selects them.
If you need to store the error information so that you can use it later you
will need to do things a bit differently within your stored procedure
DECLARE @.errNum int
SELECT @.errNum = ERROR_NUMBER()
Now you can use @.errNum at other parts of the stored procedure.
Keith Kratochvil
<tootsuite@.gmail.com> wrote in message
news:1160071938.581188.140530@.k70g2000cwa.googlegr oups.com...
> Hi,
> Something is wrong with this:
> CREATE PROCEDURE Admin_LogError
> AS
> BEGIN
> SELECT
> ERROR_NUMBER() AS ErrorNumber,
> ERROR_SEVERITY() AS ErrorSeverity,
> ERROR_STATE() as ErrorState,
> ERROR_PROCEDURE() as ErrorProcedure,
> ERROR_LINE() as ErrorLine,
> ERROR_MESSAGE() as ErrorMessage;
> INSERT INTO Admin_Errors
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> END
> Error:
> Msg 128, Level 15, State 1, Procedure Admin_LogError, Line 13
> The name "ErrorNumber" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
>
|||Thanks all - this was an oversight on my part - I borrowed this code
from somewhere else - wrong context
Keith Kratochvil wrote:[vbcol=seagreen]
> Your error is here:
> VALUES (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure,
> ErrorLine, ErrorMessage, GETDATE())
> Hint: that should be ERROR_NUMBER() not ErrorNumber
> The select statement does not assign values, it selects them.
> If you need to store the error information so that you can use it later you
> will need to do things a bit differently within your stored procedure
> DECLARE @.errNum int
> SELECT @.errNum = ERROR_NUMBER()
> Now you can use @.errNum at other parts of the stored procedure.
> --
> Keith Kratochvil
>
> <tootsuite@.gmail.com> wrote in message
> news:1160071938.581188.140530@.k70g2000cwa.googlegr oups.com...

Error

I get this error message when I alter my stored procedure. Can someone help me out plsAdd parentheses around the string.

-PatP|||Why aren't you using the templates I showed you?

In any event, post the code not a picture...

But switch to

IF @.@.ERROR <> 0

But my guess is it's before that|||I'm 99% sure that the problem is just needing parentheses around the string after RAISERROR.

She probably got the 0 <> @.@.error from some of my code. Is there anybody that can guess why I almost always put constants on the left?

-PatP|||No clue...I thought that wasn't a good thing....|||The problem goes back to the days of cruddy compilers, and poor language syntax. This is just an example, but you can interpret the construct:SELECT @.a = 0to mean two very different things. You can only interpret the construct:SELECT 0 = @.aone way. Once upon a time, Transact-SQL could get confused, and many other tools still have the same problem. By always using an expression that can't be an l-value on the left side of a comparison, there is almost no chance that the code can be mis-interpreted.

There was actually one mainframe language that would let you assign new values to constants. That one confounded even me, but it was such a weird thing that I'm not too defensive about it! I doubt that it will happen again, but it was weird being allowed to redefine the value of zero!

-PatP|||I did get the @.@.Error from pats coding and I looked it up on the internet after I did a post here and that helped me to determine how to better use it and troubleshoot. I do love Google..sometime Books online does not give you enough information. Also you guys those Transact SQL books youi refered me to are awesome. they answer alot of question and are a wonderful source of info on programming (for those of us new to the programming concept)|||works great guys thanks again :):):)