Error inserting into Table Datatype with Identity Column

Hi
We are using the code below to simulate a cursor using the Table Datatype
but we are getting an error when inserting the records.
Server: Msg 8101, Level 16, State 1, Line 19
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 28
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Any help would be much appreciated
Thanks
B
CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Type
VARCHAR(55))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.Output VARCHAR(8000)
DECLARE @.Imports VARCHAR(355)
DECLARE @.Description VARCHAR(355)
DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
VARCHAR(355), RowId INT IDENTITY(1, 1))
DECLARE @.count INT
DECLARE @.iRow INT
SET @.Output = ''
IF @.Type = 'All Items'
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
ORDER BY Usr_Imports.ID
END
ELSE
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
WHERE EntityRef = @.Client AND MatterNo =
@.OrderNo
ORDER BY Usr_Imports.ID
END
/** Simulate Cursor
****************************************
****************************/
SET @.count = @.@.ROWCOUNT
SET @.iRow = 1
WHILE @.iRow <= @.count
BEGIN
SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
FROM @.tblImports
WHERE RowId = @.iRow
SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
SET @.iRow = @.iRow + 1
END
RETURN @.Output
ENDAlways specify the column list in an INSERT statement:
INSERT @.tblImports (dm_ImportDesc, [Description])
SELECT dm_ImportDesc, [Description]
FROM ...
I recommend that you don't use ORDER BY in the INSERT statement. It is
not necessarily guaranteed that the IDENTITY column will be populated
in the order you specify.
If you explain your requirement I'm sure someone can suggest something
better. Why do concatentation and formatting in the database anyway?
David Portas
SQL Server MVP
--|||Ben
error clearly states that you are trying to update an identity column in the
function( not in cucrsor)
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
Post DDL
--
Regards
R.D
--Knowledge gets doubled when shared
"Ben" wrote:

> Hi
>
> We are using the code below to simulate a cursor using the Table Datatype
> but we are getting an error when inserting the records.
>
> Server: Msg 8101, Level 16, State 1, Line 19
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
> Server: Msg 8101, Level 16, State 1, Line 28
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
>
> Any help would be much appreciated
>
> Thanks
> B
>
> CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Ty
pe
> VARCHAR(55))
> RETURNS VARCHAR(8000) AS
> BEGIN
>
> DECLARE @.Output VARCHAR(8000)
> DECLARE @.Imports VARCHAR(355)
> DECLARE @.Description VARCHAR(355)
> DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
> VARCHAR(355), RowId INT IDENTITY(1, 1))
> DECLARE @.count INT
> DECLARE @.iRow INT
> SET @.Output = ''
>
> IF @.Type = 'All Items'
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> ORDER BY Usr_Imports.ID
> END
> ELSE
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> WHERE EntityRef = @.Client AND MatterNo
=
> @.OrderNo
> ORDER BY Usr_Imports.ID
> END
>
> /** Simulate Cursor
> ****************************************
****************************/
> SET @.count = @.@.ROWCOUNT
> SET @.iRow = 1
> WHILE @.iRow <= @.count
> BEGIN
> SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
> FROM @.tblImports
> WHERE RowId = @.iRow
>
> SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
> Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
> SET @.iRow = @.iRow + 1
> END
>
> RETURN @.Output
> END
>
>|||David,
It's with yukon.
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
-oj
"David Portas"
> I recommend that you don't use ORDER BY in the INSERT statement. It is
> not necessarily guaranteed that the IDENTITY column will be populated
> in the order you specify.|||Thank you both for your input, it works perfectly now.
Regards
B
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6FEF31BA-887B-49DA-A0DD-2EA4B07C5A71@.microsoft.com...
> Ben
> error clearly states that you are trying to update an identity column in
the
> function( not in cucrsor)
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
> Post DDL
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ben" wrote:
>
Datatype
only be
only be
@.Type
database_default
database_default
MatterNo =
[Description]
'All|||> INSERT queries that use SELECT with ORDER BY to populate rows
> guarantees how identity values are computed but not the order
> in which the rows are inserted
Possibly, but the same claim is documented in a KB for 2000 and in that
case it is inaccurate. See the following link for Gert-Jan's repro.
Haven't tested this on 2005 but even if it works I'm not sure how
confident I would be about it, given the history and the kludgy nature
of this "feature".:
http://groups.google.co.uk/group/mi...bfd47d975aca778
Now that we have RECORD_NUMBER() the ORDER BY in an INSERT is
redundant. RECORD_NUMBER() should be preferred IMO.
David Portas
SQL Server MVP
--sql

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 server 2005 setup on win2k sp4

error msg in summary.txt

Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows 2000 Server Service Pack 4 (Build 2195)
Time : Wed Feb 14 03:31:27 2007
LDINTSQL1 : There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information.

SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt.

Time : Wed Feb 14 03:39:30 2007

List of log files:
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_Core.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_Datastore.xml
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_.NET Framework 2.0.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_.NET Framework 2.0 LangPack.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_.NET Framework Upgrade Advisor.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_.NET Framework Upgrade Advisor LangPack.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_.NET Framework Windows Installer.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_.NET Framework Windows Installer LangPack.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_SNAC.log
C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_Support.log

error msg in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_LDINTSQL1_Core.log

Microsoft SQL Server 2005 Setup beginning at Wed Feb 14 03:31:27 2007
Process ID : 2176
\\ldnas1\msdn versions\Server Applications\SQL Server 2005 std\Servers\setup.exe Version: 2005.90.1399.0
Running: LoadResourcesAction at: 2007/1/14 3:31:27
Complete: LoadResourcesAction at: 2007/1/14 3:31:27, returned true
Running: ParseBootstrapOptionsAction at: 2007/1/14 3:31:27
Loaded DLL:\\ldnas1\msdn versions\Server Applications\SQL Server 2005 std\Servers\xmlrw.dll Version:2.0.3604.0
Complete: ParseBootstrapOptionsAction at: 2007/1/14 3:31:27, returned true
Running: ValidateWinNTAction at: 2007/1/14 3:31:27
Complete: ValidateWinNTAction at: 2007/1/14 3:31:27, returned true
Running: ValidateMinOSAction at: 2007/1/14 3:31:27
Complete: ValidateMinOSAction at: 2007/1/14 3:31:27, returned true
Running: PerformSCCAction at: 2007/1/14 3:31:27
Complete: PerformSCCAction at: 2007/1/14 3:31:27, returned true
Running: ActivateLoggingAction at: 2007/1/14 3:31:27
Complete: ActivateLoggingAction at: 2007/1/14 3:31:27, returned true
Delay load of action "DetectPatchedBootstrapAction" returned nothing. No action will occur as a result.
Action "LaunchPatchedBootstrapAction" will be skipped due to the following restrictions:
Condition "EventCondition: __STP_LaunchPatchedBootstrap__2176" returned false.
Running: PerformSCCAction2 at: 2007/1/14 3:31:27
Loaded DLL:C:\WINNT\system32\msi.dll Version:3.1.4000.2435
Loaded DLL:C:\WINNT\system32\msi.dll Version:3.1.4000.2435
Complete: PerformSCCAction2 at: 2007/1/14 3:31:27, returned true
Running: PerformDotNetCheck at: 2007/1/14 3:31:27
Complete: PerformDotNetCheck at: 2007/1/14 3:31:27, returned true
Running: ComponentUpdateAction at: 2007/1/14 3:31:27
Complete: ComponentUpdateAction at: 2007/1/14 3:31:54, returned true
Delay load of action "DetectLocalBootstrapAction" returned nothing. No action will occur as a result.
Action "LaunchLocalBootstrapAction" will be skipped due to the following restrictions:
Condition "EventCondition: __STP_LaunchLocalBootstrap__2176" returned false.
Running: PerformDotNetCheck2 at: 2007/1/14 3:31:54
Complete: PerformDotNetCheck2 at: 2007/1/14 3:31:54, returned true
Running: InvokeSqlSetupDllAction at: 2007/1/14 3:31:54
Error: Action "InvokeSqlSetupDllAction" threw an exception during execution.
Failed to determine path to bootstrap files : 2
Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.

Source File Name: setup\sqlsetupactions.cpp
Compiler Timestamp: Thu Sep 1 22:23:04 2005
Function Name: sqls::InvokeSqlSetupDllAction::perform
Source Line Number: 1663

any ideas?

thanks

Does your server have MDAC 2.8 SP1 installed (It is a pre-req for SQL Server 2005 setup)? To see the version of MDAC installed, you can do the following.

1. Start ->Run and type "regedit".

2. In the registry tree in the left panel, drill down to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Version. The version key should have "2.81.1117.0".

If MDAC version on the machine is less than 2.8 SP1, then you should go to download center and installing Mdac 2.8 sp1 redist from there.

If you have Mdac 2.8 sp1 installed, then can you provide the following info?

1. Are you an admin on the box?

2. What is the OS language although it shouldn't matter.

3. What is the language of SQL Server 2005 you are installing?

4. How are you installing it: Double-clicking setup.exe or through command line parameters passed to setup.exe?

5. Have you done previous installs of SQL Server 2005 on the machine?

6. Is the SQL Server 2005 you are installing an RTM release, if so which SKU?

---

This posting is provided "AS IS" with no warranties, and confers no rights.

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 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 Creating Stored Procedure from VS 2005

When I create a stored procedure in VS 2005 using C# and deploy it to the server I can't execute it there and here is the error message:

Msg 6522, Level 16, State 1, Procedure GetAll, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'GetAll':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.GetAll()

and the code for creating the stored procedure is:

SqlConnection connDB = new SqlConnection(@."Initial Catalog=MDB;Data Source=Server1;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = connDB;

cmd.CommandText = "SELECT * FROM Modifier";

connDB.Open();

SqlDataReader rdr = cmd.ExecuteReader();

SqlContext.Pipe.Send(rdr);

rdr.Close();

connDB.Close();

your help is appreciated...

What is the permission_set that you assigned for the assembly? This is the one in the CREATE ASSEMBLY. You need to set it to EXTERNAL_ACCESS due to use of SqlConnection that is accessing remote resource. Additionally, you will have to enable TRUST_WORTHY bit (use with care) or do the recommended key based login creation & assign external access assembly permission to it and use that user as owner of the assembly. If you download the new version of SQL Server 2005 Books Online it should contain updated topics that show how to do this. If you need some examples, please post back and I will try to locate a sample for you.|||

I am getting the same error when trying to debug my stored procedure. How do I get around int?

Here's my code:

Try

Dim conn As SqlConnection = New SqlConnection

conn.ConnectionString = "Data Source=XXX-XXXX\SQLSERVER2005;Initial Catalog=MotorFleetConversion;User ID=xxxx;password=xxxx"

conn.Open()

command = New SqlCommand(sqlAction)

'command.Parameters.AddWithValue("@.rating", rating)

command.Connection = conn

' Execute the command and send the results directly to the client

'SqlContext.Pipe.ExecuteAndSend(command)

Dim drUnitCode As SqlDataReader = command.ExecuteReader()

While drUnitCode.Read

If drUnitCode.Item("CompanyCode").ToString <> prevCompanyCode Then

agencySysNo = 0

If drUnitCode.Item("CompanyCode").ToString <> "" Then

agencySysNo = InsertAgency(drUnitCode.Item("CompanyCode").ToString, drUnitCode.Item("UC_DEPARTMENT_DESC").ToString, _

Convert.ToBoolean(drUnitCode.Item("NCAS")), drUnitCode.Item("UC_BILLING_CODE").ToString)

End If

End If

If agencySysNo > 0 Then

InsertDivision(agencySysNo, drUnitCode.Item("UC_DIVISION_DESC").ToString, drUnitCode.Item("UC_SHORT_DEPT_DIV").ToString, _

drUnitCode.Item("UC_CODE_NUMBER").ToString)

End If

prevCompanyCode = drUnitCode.Item("CompanyCode").ToString

End While

Catch ex As Exception

End Try

Thanks!

Error in Creating Stored Procedure from VS 2005

When I create a stored procedure in VS 2005 using C# and deploy it to the server I can't execute it there and here is the error message:

Msg 6522, Level 16, State 1, Procedure GetAll, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'GetAll':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.GetAll()

and the code for creating the stored procedure is:

SqlConnection connDB = new SqlConnection(@."Initial Catalog=MDB;Data Source=Server1;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = connDB;

cmd.CommandText = "SELECT * FROM Modifier";

connDB.Open();

SqlDataReader rdr = cmd.ExecuteReader();

SqlContext.Pipe.Send(rdr);

rdr.Close();

connDB.Close();

your help is appreciated...

What is the permission_set that you assigned for the assembly? This is the one in the CREATE ASSEMBLY. You need to set it to EXTERNAL_ACCESS due to use of SqlConnection that is accessing remote resource. Additionally, you will have to enable TRUST_WORTHY bit (use with care) or do the recommended key based login creation & assign external access assembly permission to it and use that user as owner of the assembly. If you download the new version of SQL Server 2005 Books Online it should contain updated topics that show how to do this. If you need some examples, please post back and I will try to locate a sample for you.|||

I am getting the same error when trying to debug my stored procedure. How do I get around int?

Here's my code:

Try

Dim conn As SqlConnection = New SqlConnection

conn.ConnectionString = "Data Source=XXX-XXXX\SQLSERVER2005;Initial Catalog=MotorFleetConversion;User ID=xxxx;password=xxxx"

conn.Open()

command = New SqlCommand(sqlAction)

'command.Parameters.AddWithValue("@.rating", rating)

command.Connection = conn

' Execute the command and send the results directly to the client

'SqlContext.Pipe.ExecuteAndSend(command)

Dim drUnitCode As SqlDataReader = command.ExecuteReader()

While drUnitCode.Read

If drUnitCode.Item("CompanyCode").ToString <> prevCompanyCode Then

agencySysNo = 0

If drUnitCode.Item("CompanyCode").ToString <> ""Then

agencySysNo = InsertAgency(drUnitCode.Item("CompanyCode").ToString, drUnitCode.Item("UC_DEPARTMENT_DESC").ToString, _

Convert.ToBoolean(drUnitCode.Item("NCAS")), drUnitCode.Item("UC_BILLING_CODE").ToString)

EndIf

EndIf

If agencySysNo > 0 Then

InsertDivision(agencySysNo, drUnitCode.Item("UC_DIVISION_DESC").ToString, drUnitCode.Item("UC_SHORT_DEPT_DIV").ToString, _

drUnitCode.Item("UC_CODE_NUMBER").ToString)

EndIf

prevCompanyCode = drUnitCode.Item("CompanyCode").ToString

EndWhile

Catch ex As Exception

EndTry

Thanks!

Error in : MSmerge_tombstone

I when i am try to update data in one table having replication, it gives me the error msg below;

cannot insert duplicate key row in object 'MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'

why this happens? pls. advise how i can get rid of this message.

I discovered that the table MSmerge_tombstone on subscriber is having triggers. I removed this trigger and the problem doenot reapper. Is it advisable to do that? Pls. advise.

Thanks,

I do not think removing triggers is good idea, MSmerge_tombstone table is used by replication process to track the rows that have been deleted on the subscriber
|||Could you pls. advise how can i get rid of this error?