Error in the PROCEDURE....

Hi,
i m trying to run a procedure...but everytime i run it...it gives me some error as:
-
Msg 468, Level 16, State 9, Procedure FIN_INFO__Get_Cash_Payments_Info, Line 46

Cannot resolve the collation conflict between "Cyrillic_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
--

Whats the meaning of "Cyrillic_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"
How can i change it?
if someone has some idea then please let me know...that will b great help to me...
thnks,
regards.

"Cyrillic_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" are the collations of the columns being compared. Here is an example:

create table col (col1 varchar(10) collate Cyrillic_General_CI_AS,

col2 varchar(10) collate SQL_Latin1_General_CP1_CI_AS)

select * from col

where col1 = col2

Results:

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
But if you explicitly convert the collation like in the following, the query will work:

select * from col

where col1 = col2 collate SQL_Latin1_General_CP1_CI_AS

More information can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7ory.asp

collate SQL_Latin1_General_CP1_CI_AS

|||Thanks for the reply...so that means that in the whole procedure i have to make this COLLATE command u suggested....for all the columns?ok..i will try it....n i hope it will work...thnks a lot once again
regards...|||You only need to use the COLLATE command it the collation of the columns can not be implicitly compared. If you need to do this all over, you might evaulate if you can change the column type to a more compatible collation.

Peter|||Hi,
i tried all the ways to use COLLATE...but still the same error...can you please go through the code and tell me...where exactly i have to use COLLATE...i will really appreciate any help...

ALTER procedure [dbo].[Get_Payments]

(

@.p_Personal_Account numeric(15,0), -- Personal_Account_Attributes.Personal_Account%type,

@.p_Quantity int, -- number := NULL,

@.p_Date_From datetime, -- date := NULL,

@.p_Date_To datetime -- date := SYSDATE,

)

as

begin

begin try

select @.p_Quantity = null

select @.p_Date_From = null

select @.p_Date_To = getdate()

IF (@.p_Quantity IS NULL)

select PD.Payment_Document_Id ,

PD.Document_Number collate SQL_Latin1_General_CP1_CI_AS,

(select isnull(sum(FT.Amount),0)

from Financial_Transaction FT

where FT.Personal_Account = @.p_Personal_Account

and FT.Payment_Document_id = PD.Payment_Document_Id collate SQL_Latin1_General_CP1_CI_AS

and FT.Operation_Type not in(select OL.Operation_Type

from Operation_Type_Group OG, Operation_Type_Group_Link OL

where OG.Group_Code = dbo.fnGetConstant('Collection_Constants', 'g_Sum_Differences_Group')

and OL.Operation_Type_Group_id = OG.Operation_Type_Group_id collate SQL_Latin1_General_CP1_CI_AS) ) Document_Amount,

round((select isnull(sum(FT.Amount),0)

from Financial_Transaction FT

where FT.Personal_Account = @.p_Personal_Account

and FT.Payment_Document_id = PD.Payment_Document_Id

and FT.Operation_Type not in(select OL.Operation_Type

from Operation_Type_Group OG, Operation_Type_Group_Link OL

where OG.Group_Code = dbo.fnGetConstant('Collection_Constants', 'g_Sum_Differences_Group')

and OL.Operation_Type_Group_id = OG.Operation_Type_Group_id)) * ER.Rate_Value / ER.Rate_Amount, convert(int, dbo.fnGetConstant('Collection_Constants', 'g_Round_Size'))) Internal_Amount,

PD.Currency_Code collate SQL_Latin1_General_CP1_CI_AS,

(select CR.Currency_Symbol

from Currency CR

where CR.Currency_Code = PD.Currency_Code collate SQL_Latin1_General_CP1_CI_AS) Currency_Symbol,

case

when PD.Spec_Detail_Table_Code = dbo.fnGetConstant('Collection_Constants', 'g_Spec_Detail_Table_EP')

then (select min(F.Date_Of_Transaction)

from Financial_Transaction F

where F.Personal_Account = @.p_Personal_Account

and F.Payment_Document_id = PD.Payment_Document_Id)

else PD.Date_of_Payment

end Date_of_Payment,

case

when PD.Spec_Detail_Table_Code = dbo.fnGetConstant('Collection_Constants', 'g_Spec_Detail_Table_EP')

then PD.Date_Of_Payment

else PD.Date_of_Collection

end Date_of_Collection,

(select min(FT.Date_of_Transaction)

from Financial_Transaction FT

where FT.Payment_Document_Id = PD.Payment_Document_Id collate SQL_Latin1_General_CP1_CI_AS

and FT.Personal_Account = @.p_Personal_Account) Date_Of_Transaction,

PD.Pay_Form,

(select PF.Pay_Form_Name

from Pay_Form PF

where PF.Pay_Form = PD.Pay_Form collate SQL_Latin1_General_CP1_CI_AS) Pay_Form_Name,

case

when PD.Spec_Detail_Table_Code = dbo.fnGetConstant('Collection_Constants', 'g_Spec_Detail_Table_EP')

then (select EPC_Serial_Number

from EPC_Payment EP

where EP.Payment_Document_Id = PD.Payment_Document_id collate SQL_Latin1_General_CP1_CI_AS)

else PD.Pay_Form_Number

end Pay_Form_Number,

PD.Customer_Data,

PD.Commentary,

PD.Spec_Detail_Table_Code,

(select PP.Payment_Place_Type

from Payment_Packet PP

where PD.Pay_Packet_id = PP.Pay_Packet_id) Payment_Place_Type,

(select PPT.Payment_Place_Type_Name

from Payment_Place_Type PPT

where PPT.Payment_Place_Type = (select PP.Payment_Place_Type

from Payment_Packet PP

where PD.Pay_Packet_id = PP.Pay_Packet_id)) Payment_Place_Type_Name,

PD.Date_of_Change,

PD.User_id_of_Change ,

(select ER1.Rate_Value/ER1.Rate_Amount

from Exchange_Rate_List ER1

where PD.Date_Of_Collection between ER1.Start_Date and isnull(ER1.End_Date, dbo.fnGetConstant('Collection_Constants', 'g_Max_Date'))

and ER1.From_Currency_Code = dbo.fnGetConstant('Collection_Constants', 'g_Currency_Code_Internal')

and ER1.To_Currency_Code = dbo.fnGetConstant('Collection_Constants', 'g_Currency_Code_National')

and ER1.Exchange_List_Type = dbo.fnGetConstant('Collection_Constants', 'g_Exchange_List_Type')) Course

from Payment_Document PD, Exchange_Rate_List ER

where PD.Payment_Document_id in (select F.Payment_Document_id

from Financial_Transaction F

where F.Personal_Account = @.p_Personal_Account

and F.Date_Of_Transaction between isnull(@.p_Date_From, dbo.fnGetConstant('Collection_Constants', 'g_Min_Date'))

and isnull(@.p_Date_To, dbo.fnGetConstant('Collection_Constants', 'g_Max_Date')))

and (select min(F.Date_of_Transaction)

from Financial_Transaction F

where F.Personal_Account = @.p_Personal_Account

and F.Payment_Document_id = PD.Payment_Document_id) between isnull(@.p_Date_From, dbo.fnGetConstant('Collection_Constants', 'g_Min_Date'))

and isnull(@.p_Date_To, dbo.fnGetConstant('Collection_Constants', 'g_Max_Date'))

and ER.From_Currency_Code = PD.Currency_Code

and ER.To_Currency_Code = dbo.fnGetConstant('Collection_Constants', 'g_Currency_Code_Internal')

and ER.Exchange_List_Type = dbo.fnGetConstant('Collection_Constants', 'g_Exchange_List_Type')

and PD.Date_Of_Collection between ER.Start_Date and isnull(ER.End_Date, dbo.fnGetConstant('Collection_Constants', 'g_Max_Date'))

order by Date_of_Payment desc;

THANKS IN ADVANCE....PLEASEEEEEEEEEE CHANGE THIS CODE N TEL ME WHERE I HAVE TO WRITE THIS collate....coz the changes i made..they show the same error as b4
Regards,
Shuchi.

|||Without access to database it would be hard to tell exactly where to add "collate". As a general rule, any place we you are comparing columns that have different collations many need this keyword. I see in your code you have collate in the selected columns, this is not necessary. When you get an error compiling, just click on the error and it should take you to the offending statement.

Peter

Error in Stored Procedure Debugger

Hi,
SP debugger on my machine has suddenly stopped running
with the following message
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug,
Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to
connect to debugger on SWINDEV-SQL-01\DEV1 (Error =
0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on L000646.
Debugging disabled for connection 73.
I am running on SP3 and have also run exec
sp_sdidebug 'legacy_on' . It still gives this error.
Any more ideas any one ?
Have you checked out this link:
http://msdn.microsoft.com/library/de...tools_5cfm.asp
Cheers,
James Goodman
"Anoop Agarwal" <agarwala@.halcrow.com> wrote in message
news:f92301c43e55$7e541b40$a601280a@.phx.gbl...
> Hi,
> SP debugger on my machine has suddenly stopped running
> with the following message
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug,
> Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to
> connect to debugger on SWINDEV-SQL-01\DEV1 (Error =
> 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on L000646.
> Debugging disabled for connection 73.
> I am running on SP3 and have also run exec
> sp_sdidebug 'legacy_on' . It still gives this error.
> Any more ideas any one ?
|||I have tried all the things listed in the Article and still get the same
error.
Anoop
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Error in sql 2000: Incorrect syntax near ')'.

I get this error:
Server: Msg 102, Level 15, State 1, Line 62
Incorrect syntax near ')'.
Here is the request that I put inside my cursor:

exec ('sp_grantdbaccess @.loginame=' + @.newLoginParam )
set @.sqlGrant='grant update on mosaikdb741.dbo.loginlistInput to ' + @.newLoginParam
EXEC (@.sqlGrant)

Is there any thing wrong?
thank you

Replace the [EXEC] with [PRINT] and examine the statement. I think you will then see the issue.|||

Would you be able to provide the value of @.newLoginParam that is causing the problem?

Chris

|||As I indicated earlier, if you were to PRINT and examine the value of @.sqlGrant, you would readily see that there 'may' be a problem with quotes.|||

exec ('sp_grantdbaccess @.loginame= ''' + @.newLoginParam + '''')

...but work only if you dont have ' into @.newLoginParam

My suggestion is to use sp_executesql with separate parameters settings.

Mauro

Error in sql 2000: Incorrect syntax near ')'.

I get this error:
Server: Msg 102, Level 15, State 1, Line 62
Incorrect syntax near ')'.
Here is the request that I put inside my cursor:

exec ('sp_grantdbaccess @.loginame=' + @.newLoginParam )
set @.sqlGrant='grant update on mosaikdb741.dbo.loginlistInput to ' + @.newLoginParam
EXEC (@.sqlGrant)

Is there any thing wrong?
thank you

Replace the [EXEC] with [PRINT] and examine the statement. I think you will then see the issue.|||

Would you be able to provide the value of @.newLoginParam that is causing the problem?

Chris

|||As I indicated earlier, if you were to PRINT and examine the value of @.sqlGrant, you would readily see that there 'may' be a problem with quotes.|||

exec ('sp_grantdbaccess @.loginame= ''' + @.newLoginParam + '''')

...but work only if you dont have ' into @.newLoginParam

My suggestion is to use sp_executesql with separate parameters settings.

Mauro

error in Pivot

Hi,
I've been trying to get the pivot command to work just so. I've almost got it the way I need it but I'm getting Msg 102, Level 15, State 1, Line 14 Incorrect syntax near ')'.

This is my code:

INSERT INTO dbo.tmpProjExpendFY

SELECT ProjNo, TaskCode, [1] AS P1, [2] AS P2, [3] AS P3, [4] AS P4, [5] AS P5, AS P6,

[7] AS P7, AS P8, [9] AS P9, [10] AS P10, [11] AS P11, [12] AS P12

FROM (SELECT s.ProjNo, tblkpTask.TaskCode

FROM tblProjSched AS s CROSS JOIN

tblkpTask

WHERE (s.DeptCode = 'SWM')) AS dm LEFT OUTER JOIN

(SELECT SUM(e.ActualAmt) AS PYears, e.ProjNo, e.TaskCode

FROM tblActualExpend AS e INNER JOIN

tblBudgetConfig ON e.FiscalYear < tblBudgetConfig.CurrentBudgetYear

GROUP BY e.ProjNo, e.TaskCode) AS dp ON dm.ProjNo = dp.ProjNo AND dm.TaskCode = dp.TaskCode

ORDER BY dm.ProjNo, dm.TaskCode

)p

PIVOT

(

SUM(ActualAmt)

FOR FiscalPeriod IN

( [1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12])

)AS pvt

ORDER BY pvt.ProjNo, pvt.TaskCode;

My end result needs to include all taskcodes for each project regardless of weither it has an expense:

ProjNo TaskCode P1 P2 P3 P4 ect
64BRD PLN 10 23 null 5 ect
there should be 9 total taskcodes per project.

Thansk in advanced for any help.

use the following query..

SELECT
ProjNo
,TaskCode
,[1] AS P1
,[2] AS P2
,[3] AS P3
,[4] AS P4
,[5] AS P5
, AS P6
,[7] AS P7
, AS P8
,[9] AS P9
,[10] AS P10
,[11] AS P11
,[12] AS P12
From
(
Select
dm.ProjNo
,dm.TaskCode
,dp.FiscalPeriod
,dp.ActualAmt ActualAmt
FROM (
SELECT
s.ProjNo
,tblkpTask.TaskCode
FROM tblProjSched AS s
CROSS JOIN tblkpTask tblkpTask
) AS dm
LEFT OUTER JOIN
(
SELECT
e.ActualAmt
,e.ProjNo
,e.TaskCode
,e.FiscalPeriod
FROM
tblActualExpend AS e
) AS dp
ON dm.ProjNo = dp.ProjNo
AND dm.TaskCode = dp.TaskCode

) as Data
PIVOT (SUM(ActualAmt) FOR FiscalPeriod IN ([1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12]))AS pvt
ORDER BY
pvt.ProjNo
,pvt.TaskCode

|||Thanks ManiD that does the trick!

error in executing exec xp_cmdshell

Hello,
I am getting this error
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
when I try to execute this statement using sql user login
who is not having sysamin rights.
exec xp_cmdshell "copy D:\File1.txt E:\File1.txt"
I have configured the Proxy account for SQL Agent, but
still getting this error.
Can any help me ?
Regds,
ManojDoes the ID have rights to Execute xp_cmdshell? Did you check the NT id for
Proxy and make sure it has adequate rights for your copy? Read from the
root of D and write to the root of E. To check for sure make the Id a
temporary local admin for the windows box and rerun your query. Double
check by creating 2 folders and giving the ID full control of both folders
and change your query to copy to and from the folders instead.
Jeff Duncan
MCDBA, MCSE+I
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:889301c43299$96196bd0$a601280a@.phx.gbl...
> Hello,
> I am getting this error
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
> when I try to execute this statement using sql user login
> who is not having sysamin rights.
> exec xp_cmdshell "copy D:\File1.txt E:\File1.txt"
> I have configured the Proxy account for SQL Agent, but
> still getting this error.
> Can any help me ?
> Regds,
> Manoj|||Make sure the SQL Server startup account has the necessary rights:
- Act as part of the operating system.
- Increase quotas.
- replace process level token.
- Log on as a batch job.
Having increase quotas missing has been a cause of this problem.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The loging wich I am using is a member of Local and Domain
admin group.

>--Original Message--
>Does the ID have rights to Execute xp_cmdshell? Did you
check the NT id for
>Proxy and make sure it has adequate rights for your
copy? Read from the
>root of D and write to the root of E. To check for sure
make the Id a
>temporary local admin for the windows box and rerun your
query. Double
>check by creating 2 folders and giving the ID full
control of both folders
>and change your query to copy to and from the folders
instead.
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
>news:889301c43299$96196bd0$a601280a@.phx.gbl...
636[vbcol=seagreen]
login[vbcol=seagreen]
>
>.
>|||> The loging wich I am using is a member of Local and Domain
> admin group.
Did you assign the SQL Server service account the advanced user rights
detailed in this thread by Rand? The rights are needed so that SQL Server
can change security context to the proxy account. The permissions are set
automatically when you specify the SQL Server service account during
installation or change it from Enterprise manager. However, these are not
set when you change the account directly.
Output from command Windows command NET HELPMSG 1314:
A required privilege is not held by the client.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:9a2301c433ea$81364d80$a001280a@.phx.gbl...[vbcol=seagreen]
> The loging wich I am using is a member of Local and Domain
> admin group.
>
> check the NT id for
> copy? Read from the
> make the Id a
> query. Double
> control of both folders
> instead.
> 636
> login|||The problem got solved, The login user was not having the
Increase quotas on the server, which after setting worked
out
Thanks,
Manoj
>--Original Message--
Domain[vbcol=seagreen]
>Did you assign the SQL Server service account the
advanced user rights
>detailed in this thread by Rand? The rights are needed
so that SQL Server
>can change security context to the proxy account. The
permissions are set
>automatically when you specify the SQL Server service
account during
>installation or change it from Enterprise manager.
However, these are not
>set when you change the account directly.
>Output from command Windows command NET HELPMSG 1314:
> A required privilege is not held by the client.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
>news:9a2301c433ea$81364d80$a001280a@.phx.gbl...
Domain[vbcol=seagreen]
you[vbcol=seagreen]
sure[vbcol=seagreen]
your[vbcol=seagreen]
message[vbcol=seagreen]
line[vbcol=seagreen]
but[vbcol=seagreen]
>
>.
>|||Hi,
One quick question - If I want to have my SQL service startup using a system
account, can I still set it to have "Increase Quotas on the server"? If so,
could you let me know where I can set that?
Thanks!
/ec
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:ac9201c4368c$aa05ba80$a001280a@.phx.gbl...[vbcol=seagreen]
> The problem got solved, The login user was not having the
> Increase quotas on the server, which after setting worked
> out
> Thanks,
> Manoj
> Domain
> advanced user rights
> so that SQL Server
> permissions are set
> account during
> However, these are not
> Domain
> you
> sure
> your
> message
> line
> but

Error in DELETE trigger

Hi,
I get an error when I try to delete multiple rows from a table.
Server: Msg 512, Level 16, State 1, Procedure tr_Documents_Delete, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I guess it is because of my trigger. The trigger looks like this:
ALTER TRIGGER tr_Documents_Delete
ON DocumentTrans
AFTER DELETE
AS
if @.@.rowcount = 0
return
DECLARE @.count INT
SET @.count = (SELECT COUNT(*) FROM DocumentTrans INNER JOIN Deleted
ON DocumentTrans.TableReference = Deleted.TableReference
AND DocumentTrans.RowNumberReference = Deleted.RowNumberReference)
IF @.count > 0
return
DECLARE @.TableName VARCHAR(100)
SET @.TableName = (SELECT TableReference FROM Deleted)
DECLARE @.SQLString VARCHAR(1000)
SET @.SQLString = 'UPDATE ' + @.TableName +
' SET DocumentsExists = NULL WHERE ' +
@.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
Deleted) AS VARCHAR(100))
EXEC(@.SQLString)
I have tried with a loop. But it also gives me an error
WHILE (SELECT TableReference FROM Deleted) IS NOT NULL
BEGIN
DECLARE @.TableName VARCHAR(100)
SET @.TableName = (Select TableReference FROM Deleted)
DECLARE @.SQLString VARCHAR(1000)
SET @.SQLString = 'UPDATE ' + @.TableName +
' SET DocumentsExists = NULL WHERE ' +
@.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
Deleted) AS VARCHAR(100))
EXEC(@.SQLString)
END
Any ideas?
Best regards
HenrikHi
The offending line could be:
SET @.TableName = (SELECT TableReference FROM Deleted)
A trigger fires once per batch, and not once per row deleted. The assumption
that only 1 row is being deleted fails on that line. The select is returning
more than one row, but the variable can not hold more than one row's value.
Either look at the way you process the row, or disallow more than one row to
be deleted at the same time with another check earlier on the in trigger.
Regards
Mike
"Henrik Skak Pedersen" wrote:

> Hi,
> I get an error when I try to delete multiple rows from a table.
> Server: Msg 512, Level 16, State 1, Procedure tr_Documents_Delete, Line 16
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <= , >, >= or when the subquery is used as an expression
.
> The statement has been terminated.
> I guess it is because of my trigger. The trigger looks like this:
> ALTER TRIGGER tr_Documents_Delete
> ON DocumentTrans
> AFTER DELETE
> AS
> if @.@.rowcount = 0
> return
> DECLARE @.count INT
> SET @.count = (SELECT COUNT(*) FROM DocumentTrans INNER JOIN Deleted
> ON DocumentTrans.TableReference = Deleted.TableReference
> AND DocumentTrans.RowNumberReference = Deleted.RowNumberReference)
> IF @.count > 0
> return
> DECLARE @.TableName VARCHAR(100)
> SET @.TableName = (SELECT TableReference FROM Deleted)
> DECLARE @.SQLString VARCHAR(1000)
> SET @.SQLString = 'UPDATE ' + @.TableName +
> ' SET DocumentsExists = NULL WHERE ' +
> @.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
> Deleted) AS VARCHAR(100))
> EXEC(@.SQLString)
> I have tried with a loop. But it also gives me an error
> WHILE (SELECT TableReference FROM Deleted) IS NOT NULL
> BEGIN
> DECLARE @.TableName VARCHAR(100)
> SET @.TableName = (Select TableReference FROM Deleted)
> DECLARE @.SQLString VARCHAR(1000)
> SET @.SQLString = 'UPDATE ' + @.TableName +
> ' SET DocumentsExists = NULL WHERE ' +
> @.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
> Deleted) AS VARCHAR(100))
> EXEC(@.SQLString)
> END
> Any ideas?
> Best regards
> Henrik
>
>

Error in dbcc From Osql

Hi,
i get this error when i run Dbcc on one of my production server database
From Osql Utility.
Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, ARITHABORT'.NULL
But when i run Dbcc from QA Utility i dont get any error and the Dbbc Gives
me CHECKDB found 0 allocation errors and 0 consistency errors in database I
tried to set
SET ARITHABORT Off
SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
ThanksHi,
> SET ARITHABORT Off
> SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
TRY
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2005
"dines" <dines@.discussions.microsoft.com> wrote in message
news:CDB35652-383C-4A62-953B-2386CAA5A8BF@.microsoft.com...
> Hi,
> i get this error when i run Dbcc on one of my production server database
> From Osql Utility.
> Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER, ARITHABORT'.NULL
> But when i run Dbcc from QA Utility i dont get any error and the Dbbc
Gives
> me CHECKDB found 0 allocation errors and 0 consistency errors in database
I
> tried to set
> SET ARITHABORT Off
> SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
> Thanks
>|||Hi,
I tried that too but still i get the same error
Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, ARITHABORT'.NULL
thanks
"Shinichi Yoneda" wrote:
> Hi,
> > SET ARITHABORT Off
> > SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
> TRY
> SET ARITHABORT ON
> SET QUOTED_IDENTIFIER ON
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2005
> "dines" <dines@.discussions.microsoft.com> wrote in message
> news:CDB35652-383C-4A62-953B-2386CAA5A8BF@.microsoft.com...
> > Hi,
> >
> > i get this error when i run Dbcc on one of my production server database
> > From Osql Utility.
> >
> > Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
> > DBCC failed because the following SET options have incorrect settings:
> > 'QUOTED_IDENTIFIER, ARITHABORT'.NULL
> >
> > But when i run Dbcc from QA Utility i dont get any error and the Dbbc
> Gives
> > me CHECKDB found 0 allocation errors and 0 consistency errors in database
> I
> > tried to set
> > SET ARITHABORT Off
> > SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
> >
> > Thanks
> >
>

Error in dbcc From Osql

Hi,
i get this error when i run Dbcc on one of my production server database
From Osql Utility.
Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, ARITHABORT'.NULL
But when i run Dbcc from QA Utility i dont get any error and the Dbbc Gives
me CHECKDB found 0 allocation errors and 0 consistency errors in database I
tried to set
SET ARITHABORT Off
SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
Thanks
Hi,

> SET ARITHABORT Off
> SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
TRY
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2005
"dines" <dines@.discussions.microsoft.com> wrote in message
news:CDB35652-383C-4A62-953B-2386CAA5A8BF@.microsoft.com...
> Hi,
> i get this error when i run Dbcc on one of my production server database
> From Osql Utility.
> Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER, ARITHABORT'.NULL
> But when i run Dbcc from QA Utility i dont get any error and the Dbbc
Gives
> me CHECKDB found 0 allocation errors and 0 consistency errors in database
I
> tried to set
> SET ARITHABORT Off
> SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
> Thanks
>
|||Hi,
I tried that too but still i get the same error
Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, ARITHABORT'.NULL
thanks
"Shinichi Yoneda" wrote:

> Hi,
> TRY
> SET ARITHABORT ON
> SET QUOTED_IDENTIFIER ON
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2005
> "dines" <dines@.discussions.microsoft.com> wrote in message
> news:CDB35652-383C-4A62-953B-2386CAA5A8BF@.microsoft.com...
> Gives
> I
>
|||Try to execute the following statement in both QA and with OSQL:
DBCC USEROPTIONS
Then compare the two. This command will tell you the current settings under each API. QA sets many environment values quite differently than other APIs.
Sincerely,
Anthony Thomas

"dines" <dines@.discussions.microsoft.com> wrote in message news:5A9AE886-FA9B-440E-8A38-CCF3AF110083@.microsoft.com...
Hi,
I tried that too but still i get the same error
Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, ARITHABORT'.NULL
thanks
"Shinichi Yoneda" wrote:

> Hi,
>
> TRY
> SET ARITHABORT ON
> SET QUOTED_IDENTIFIER ON
>
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2005
>
> "dines" <dines@.discussions.microsoft.com> wrote in message
> news:CDB35652-383C-4A62-953B-2386CAA5A8BF@.microsoft.com...
> Gives
> I
>
>

Error in dbcc From Osql

Hi,
i get this error when i run Dbcc on one of my production server database
From Osql Utility.
Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, ARITHABORT'.NULL
But when i run Dbcc from QA Utility i dont get any error and the Dbbc Gives
me CHECKDB found 0 allocation errors and 0 consistency errors in database I
tried to set
SET ARITHABORT Off
SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
ThanksHi,

> SET ARITHABORT Off
> SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
TRY
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2005
"dines" <dines@.discussions.microsoft.com> wrote in message
news:CDB35652-383C-4A62-953B-2386CAA5A8BF@.microsoft.com...
> Hi,
> i get this error when i run Dbcc on one of my production server database
> From Osql Utility.
> Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER, ARITHABORT'.NULL
> But when i run Dbcc from QA Utility i dont get any error and the Dbbc
Gives
> me CHECKDB found 0 allocation errors and 0 consistency errors in database
I
> tried to set
> SET ARITHABORT Off
> SET QUOTED_IDENTIFIER Off in my osql comand but still i get the same error
> Thanks
>|||Hi,
I tried that too but still i get the same error
Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, ARITHABORT'.NULL
thanks
"Shinichi Yoneda" wrote:

> Hi,
>
> TRY
> SET ARITHABORT ON
> SET QUOTED_IDENTIFIER ON
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2005
> "dines" <dines@.discussions.microsoft.com> wrote in message
> news:CDB35652-383C-4A62-953B-2386CAA5A8BF@.microsoft.com...
> Gives
> I
>|||Try to execute the following statement in both QA and with OSQL:
DBCC USEROPTIONS
Then compare the two. This command will tell you the current settings under
each API. QA sets many environment values quite differently than other API
s.
Sincerely,
Anthony Thomas
--
"dines" <dines@.discussions.microsoft.com> wrote in message news:5A9AE886-F
A9B-440E-8A38-CCF3AF110083@.microsoft.com...
Hi,
I tried that too but still i get the same error
Msg 1934, Level 16, State 1, Server JorSRv3, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER, ARITHABORT'.NULL
thanks
"Shinichi Yoneda" wrote:

> Hi,
>
> TRY
> SET ARITHABORT ON
> SET QUOTED_IDENTIFIER ON
>
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2005
>
> "dines" <dines@.discussions.microsoft.com> wrote in message
> news:CDB35652-383C-4A62-953B-2386CAA5A8BF@.microsoft.com...
> Gives
> I
>
>

Error in cross tab statement

Hi,

I have a coding which state as below:

CREATE PROCEDURE [dbo].[crossTab]
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100)
AS

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE ' + @.pivot + ' Is Not Null')

SELECT @.sql='' , @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN ' + @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)
SET ANSI_WARNINGS ON
GO

I just woud like why it come out error stated that

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.

when i key in

exec crossTab 'select dbo,fgo,rgt from Table A', 'sum(fgo)', 'rgt', 'Table A'

Please help me

ThanxYet another attempt at a universal cross-tab function...

Does your code error out if you comment out the "EXEC (@.select)" line? If not, then try replacing it with "SELECT @.select" or "PRINT @.select" to see exactly what code is being executed that is causing the error.