Anyone is aware of this SSIS Error.

Hi Folks,

I developed my ssis package against my local database and i would like to test it against remote server database.For that i have modified all of my connection manager settings still i am getting error on oledb destination and Execute T-Sql task.The below is the error.

[OLE DB Destination [11665]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK__tblPr__532FC765'. Cannot insert duplicate key in object 'dbo.tblPr'.".

Can anyone suggest me what would be the problem and how to resolve this.

Thanks in Advance.

Violation of PRIMARY KEY constraint 'PK__tblPr__532FC765'. Cannot insert duplicate key in object 'dbo.tblPr'.

This means you are trying to insert a key which is already in the table tblPr. Primary Keys have to be unique.. to identify a specific row.

for example (ID is your Primary Key):

ID | SomeColumns
1 | test
2 | testasdf

and now you are trying to insert

1 | anothertest

the column ID (which is your primary key) has already a row with 1 so you can't insert a second row with ID 1

this is just an explanation of your problem .. but i can't help you without any further information... maybe you can fix it on your own when you understand it

- paul
|||

Hi Paul,

I made the changes It works now.Thanks.

I am getting one more error when i am executing Execute T-sql Task.I am using identity column in my tsql statement.

Below is the error.

"User 'guest' does not have permission to run DBCC CHECKIDENT for object '#TempPr__000000000ACC'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Please let me know what is wrong if you know..

Thanks so much.

|||

A search on the Internet will yield very helpful results. This is a permissions issue, just as the error says.

Read up on DBCC CHECKIDENT.

http://msdn2.microsoft.com/en-us/library/ms176057.aspx

Anyone helps me ? I cant attach Database.

When I Attach Database it always rise up Error ( see image). how to do it ? anyone helps me thanks :o :oThe datafile is corrupt, and can not be attached. Perhaps it was not properly detached from the original SQL Server?|||I would restore this from the last good backup, rather than try and attach it. How was it detached in the first place?|||As explained you have to search for a good backup in this case, also review what kind of hardware issues you have on that server. Error 823 KBA http://support.microsoft.com/kb/828339 fyi.

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
Paul G
Software engineer.
On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:

>I have a query that contains a sub query. The problem is that the sub query
>returns more than 1 value so it fails with the error (Subquery returned more
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column 3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.
|||thanks that is what I was looking for!
Paul G
Software engineer.
"JXStern" wrote:

> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
--
Paul G
Software engineer.On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>I have a query that contains a sub query. The problem is that the sub query
>returns more than 1 value so it fails with the error (Subquery returned more
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column 3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.|||thanks that is what I was looking for!
--
Paul G
Software engineer.
"JXStern" wrote:
> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I have a query that contains a sub query. The problem is that the sub query
> >returns more than 1 value so it fails with the error (Subquery returned more
> >than 1 value). Here is a simplified version of how it is setup.
> >
> >Select * from table1 where column2 = (select col2 from table2 where column 3
> >=4)
> >So for the example below it should return rows 1 and 3 from table 1 since a
> >and c were returned in the subquery.
> >
> >table 1
> >col1 col2
> >1 a
> >2 b
> >3 c
> >
> >table 2
> >col1 col2 col3
> >1 a 4
> >2 b 2
> >3 c 4
> >thanks.
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
--
Paul G
Software engineer.On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:

>I have a query that contains a sub query. The problem is that the sub quer
y
>returns more than 1 value so it fails with the error (Subquery returned mor
e
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column
3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.|||thanks that is what I was looking for!
--
Paul G
Software engineer.
"JXStern" wrote:

> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>sql

Anyone else had this error situation?

So, for the past two weeks or so every morning Reporting services has come up
with the report error "Sorting cannot be performed on table1". For some odd
reason though it does the error on all reports with any sorting whatsoever.
I've checked and double checked my sorting expressions
(=iif(Parameters!Direction.Value = "Ascending",
Fields(Parameters!SortBy.Value).Value, 0) and =iif(Parameters!Direction.Value
= "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
Direction is a drop down param which is either Ascending or Descending and
SortBy is a drop down param that contains all table columns) and have had
others look at it as well and the logic is good for it. What ultimately
remedies it is a restart of IIS on the server we run the reports on.
So has anyone else experienced this problem? If so I could use any
information as to the possible cause or fix to this.Please try this - note the CInt() function call:
=iif(Parameters!Direction.Value = "Ascending",
CInt(Fields(Parameters!SortBy.Value).Value), 0)
=iif(Parameters!Direction.Value = "Descending",
CInt(Fields(Parameters!SortBy.Value).Value), 0)
etc.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <Timm@.discussions.microsoft.com> wrote in message
news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
> So, for the past two weeks or so every morning Reporting services has come
> up
> with the report error "Sorting cannot be performed on table1". For some
> odd
> reason though it does the error on all reports with any sorting
> whatsoever.
> I've checked and double checked my sorting expressions
> (=iif(Parameters!Direction.Value = "Ascending",
> Fields(Parameters!SortBy.Value).Value, 0) and
> =iif(Parameters!Direction.Value
> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
> Direction is a drop down param which is either Ascending or Descending and
> SortBy is a drop down param that contains all table columns) and have had
> others look at it as well and the logic is good for it. What ultimately
> remedies it is a restart of IIS on the server we run the reports on.
> So has anyone else experienced this problem? If so I could use any
> information as to the possible cause or fix to this.|||The column names in use are string values (non-numeric) so using CInt throws
back a
different error that will cause the report to not work 100% of the time. I'm
toying with
an idea that this is something to do with the reporting services and not the
reports
themselves since restarting IIS resolves the issue for a day (and the next
day the error
occurs again).
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Please try this - note the CInt() function call:
> =iif(Parameters!Direction.Value = "Ascending",
> CInt(Fields(Parameters!SortBy.Value).Value), 0)
> =iif(Parameters!Direction.Value = "Descending",
> CInt(Fields(Parameters!SortBy.Value).Value), 0)
> etc.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Timm" <Timm@.discussions.microsoft.com> wrote in message
> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>|||If they are strings, then this explains why it does not work. Use the
following expression then - note that 0 is replaced with an empty string:
=iif(Parameters!Direction.Value = "Ascending",
Fields(Parameters!SortBy.Value).Value, "")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <tim@.gumdropbooks.com> wrote in message
news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
> The column names in use are string values (non-numeric) so using CInt
> throws back a
> different error that will cause the report to not work 100% of the time.
> I'm toying with
> an idea that this is something to do with the reporting services and not
> the reports
> themselves since restarting IIS resolves the issue for a day (and the next
> day the error
> occurs again).
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>|||I did that change and this morning we got that error still. After restarting
IIS it now works again.
Is this a known issue or is this something that may be unique to our system?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23ggV6FvZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> If they are strings, then this explains why it does not work. Use the
> following expression then - note that 0 is replaced with an empty string:
> =iif(Parameters!Direction.Value = "Ascending",
> Fields(Parameters!SortBy.Value).Value, "")
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Timm" <tim@.gumdropbooks.com> wrote in message
> news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
>> The column names in use are string values (non-numeric) so using CInt
>> throws back a
>> different error that will cause the report to not work 100% of the time.
>> I'm toying with
>> an idea that this is something to do with the reporting services and not
>> the reports
>> themselves since restarting IIS resolves the issue for a day (and the
>> next day the error
>> occurs again).
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>>
>|||I haven't seen this before. Are you sure you don't have any report execution
caching set up?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <tim@.gumdropbooks.com> wrote in message
news:eHjfzC3ZFHA.1040@.TK2MSFTNGP10.phx.gbl...
>I did that change and this morning we got that error still. After
>restarting IIS it now works again.
> Is this a known issue or is this something that may be unique to our
> system?
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23ggV6FvZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> If they are strings, then this explains why it does not work. Use the
>> following expression then - note that 0 is replaced with an empty string:
>> =iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, "")
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <tim@.gumdropbooks.com> wrote in message
>> news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
>> The column names in use are string values (non-numeric) so using CInt
>> throws back a
>> different error that will cause the report to not work 100% of the time.
>> I'm toying with
>> an idea that this is something to do with the reporting services and not
>> the reports
>> themselves since restarting IIS resolves the issue for a day (and the
>> next day the error
>> occurs again).
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For
>> some odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What
>> ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>>
>>
>|||I am new to reporting services, so my answer may not be correct.
I had same error, when 2nd parameter was changed to cint, error went
away.
Something like this -
=iif(Parameters!Direction.Value = "Ascending",
CInt(Fields(Parameters!SortBy.Value).Value), cint(0))
=iif(Parameters!Direction.Value = "Descending",
CInt(Fields(Parameters!SortBy.Value).Value), cint(0))

anyone can give solution for this problem

2004-08-21 11:52:38.64 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:52:38.64 spid52 language_exec: Process 52
generated an access violation. SQL Server is terminating
this process..
2004-08-21 11:56:12.93 spid52 Using 'sqlimage.dll'
version '4.0.5'
Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQL00062.dmp
2004-08-21 11:56:12.94 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:56:12.94 spid52 SqlDumpExceptionHandler:
Process 52 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process..
************************************************** *********
********************
*
* BEGIN STACK DUMP:
* 08/21/04 11:56:12 spid 52
*
* Exception Address = 0055AB2F
(COpArg::DeriveNormalizedGroupProperties(class CTreeHandle
*) + 0000001E Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* Input Buffer 4088 bytes -
* SELECT glmedium.module_id,
glmedium.vouchertype,
* glmedium.vouchernumber,
glmedium.voucherdate,
* glmedium.accountcode,
glmedium.amountdebit,
* glmedium.amountcredit,
glmedium.jobid, cstcon
* tract.ContractName,
glaccounts.AccountName , gljobwisep
* rofit.groupcode ,
glmedium.accountremarks,
glmedium.partycode,
* arcustomer.customername,
contractvalue,provisionamt FROM glm
* edium, cstcontract,
glaccounts, gljobwisep
* rofit, arcustomer WHERE (
glmedium.company_id = cstcontract.Co
* mpany_id ) and ( glmedium.jobid =
cstcontract.ContractNo ) a
* nd ( glmedium.company_id =
glaccounts.Company_id ) and
* ( glmedium.accountcode =
glaccounts.AccountCode ) AND VOUCHER
* TYPE NOT IN ('GI','SR','DN','DJ') and
glmedium.accountcode = gljobwi
* seprofit.accountcode and
glmedium.company_id = arcustomer.company_i
* d and partycode =
arcustomer.customercode and
glmedium.company_i
* d = '05' and
glmedium.voucherdate >= '1-JAN-2000' and
glmedium.
* voucherdate <= '31-DEC-2004' and
glmedium.jobid >= '0' and
glmed
* ium.jobid <= 'ZZZZZZZZ' UNION SELECT
glmedium.module_id,
* glmedium.vouchertype,
glmedium.vouchernumber,
* glmedium.voucherdate,
glmedium.accountcode,
* glmedium.amountdebit, 0,
glmedium.jobid,
* cstcontract.ContractName,
glaccounts.AccountName
* , 'ZI' ,
glmedium.accountremarks,
glmedium.partycode,
* arcustomer.customername,
contractvalue,provisionamt FROM glmedi
* um, cstcontract, glaccounts,
arcustomer
* WHERE ( glmedium.company_id =
cstcontract.Company_id ) and
* ( glmedium.jobid = cstcontract.ContractNo )
and ( glmed
* ium.company_id = glaccounts.Company_id )
and ( glmedium.acco
* untcode = glaccounts.Account
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B19FFF
0071a000
* ntdll 77F80000 77FFCFFF
0007d000
* KERNEL32 7C570000 7C627FFF
000b8000
* ADVAPI32 7C2D0000 7C331FFF
00062000
* RPCRT4 77D30000 77DA0FFF
00071000
* USER32 77E10000 77E74FFF
00065000
* GDI32 77F40000 77F7DFFF
0003e000
* OPENDS60 41060000 41065FFF
00006000
* MSVCRT 78000000 78044FFF
00045000
* UMS 41070000 4107CFFF
0000d000
* SQLSORT 42AE0000 42B6FFFF
00090000
* MSVCIRT 780A0000 780B1FFF
00012000
* sqlevn70 41080000 41086FFF
00007000
* NETAPI32 75170000 751BEFFF
0004f000
* Secur32 7C340000 7C34EFFF
0000f000
* NTDSAPI 77BF0000 77C00FFF
00011000
* DNSAPI 77980000 779A3FFF
00024000
* WSOCK32 75050000 75057FFF
00008000
* WS2_32 75030000 75043FFF
00014000
* WS2HELP 75020000 75027FFF
00008000
* WLDAP32 77950000 77979FFF
0002a000
* NETRAP 751C0000 751C5FFF
00006000
* SAMLIB 75150000 7515EFFF
0000f000
* wmi 76110000 76113FFF
00004000
* SSNETLIB 42CF0000 42D05FFF
00016000
* SSNMPN70 410D0000 410D5FFF
00006000
* security 75500000 75503FFF
00004000
* crypt32 7C740000 7C7C6FFF
00087000
* MSASN1 77430000 7743FFFF
00010000
* userenv 7C0F0000 7C150FFF
00061000
* rnr20 782C0000 782CBFFF
0000c000
* iphlpapi 77340000 77352FFF
00013000
* ICMP 77520000 77524FFF
00005000
* MPRAPI 77320000 77336FFF
00017000
* OLE32 77A50000 77B3EFFF
000ef000
* OLEAUT32 779B0000 77A4AFFF
0009b000
* ACTIVEDS 773B0000 773DEFFF
0002f000
* ADSLDPC 77380000 773A2FFF
00023000
* RTUTILS 77830000 7783DFFF
0000e000
* SETUPAPI 77880000 7790DFFF
0008e000
* RASAPI32 774E0000 77512FFF
00033000
* RASMAN 774C0000 774D0FFF
00011000
* TAPI32 77530000 77551FFF
00022000
* COMCTL32 71710000 71793FFF
00084000
* SHLWAPI 70A70000 70AD4FFF
00065000
* DHCPCSVC 77360000 77378FFF
00019000
* winrnr 777E0000 777E7FFF
00008000
* rasadhlp 777F0000 777F4FFF
00005000
* msafd 74FD0000 74FEDFFF
0001e000
* wshtcpip 75010000 75016FFF
00007000
* SSmsLPCn 42CD0000 42CD6FFF
00007000
* SQLFTQRY 41020000 4103CFFF
0001d000
* CLBCATQ 775A0000 7762FFFF
00090000
* SQLOLEDB 75370000 753E7FFF
00078000
* MSDART 2AD60000 2AD82FFF
00023000
* VERSION 77820000 77826FFF
00007000
* LZ32 759B0000 759B5FFF
00006000
* comdlg32 76B30000 76B6DFFF
0003e000
* SHELL32 782F0000 78534FFF
00245000
* MSDATL3 2AD90000 2ADA5FFF
00016000
* oledb32 2B0B0000 2B11EFFF
0006f000
* OLEDB32R 2B120000 2B130FFF
00011000
* rsabase 7CA00000 7CA22FFF
00023000
* xpstar 410F0000 41133FFF
00044000
* SQLUNIRL 41090000 410BCFFF
0002d000
* WINSPOOL 77800000 7781DFFF
0001e000
* MPR 76620000 7662FFFF
00010000
* SQLRESLD 42AC0000 42AC6FFF
00007000
* SQLSVC 42C40000 42C56FFF
00017000
* ODBC32 2B150000 2B184FFF
00035000
* odbcbcp 41150000 41156FFF
00007000
* W95SCM 41140000 4114BFFF
0000c000
* NDDEAPI 769A0000 769A6FFF
00007000
* odbcint 2B290000 2B2A5FFF
00016000
* clusapi 73930000 7393FFFF
00010000
* resutils 689D0000 689DCFFF
0000d000
* SQLSVC 43970000 43975FFF
00006000
* xpstar 439E0000 439EBFFF
0000c000
* msv1_0 2B600000 2B620FFF
00021000
* srchadm 60000000 60042FFF
00043000
* mssws 2B850000 2B858FFF
00009000
* athprxy 2BBB0000 2BBB7FFF
00008000
* DBGHELP 2BCF0000 2BD02FFF
00013000
* msdbi 6BE90000 6BEABFFF
0001c000
* sqlimage 4A400000 4A40CFFF
0000d000
*
* Edi: 00000000:
* Esi: 1D9F6B50: 00982848 00000002 209B8030
1DA15268 1DA12690 00000000
* Eax: 00000000:
* Ebx: 00000000:
* Ecx: 00000000:
* Edx: 2A88C228: 00000124 20940168 2090D5D0
208FE358 1D9B9838 20940168
* Eip: 0055AB2F: 50FF038B D8F74810 8940C01B
1375EC45 5FF44D8B 5B5EC38B
* Ebp: 2A88BD5C: 2A88BED0 0055A7BB 2A88BD74
00000000 1D9B9A48 00000000
* SegCs: 0000001B:
* EFlags: 00010246: 0061006A 00610076 0063005C
0061006C 00730073 00730065
* Esp: 2A88BD1C: 00000000 1D9F6B50 00000000
2A88BD38 0040204E 209B7400
* SegSs: 00000023:
************************************************** *********
********************
Short Stack Dump
0055AB2F Module(sqlservr+0015AB2F)
(COpArg::DeriveNormalizedGroupProperties(class CTreeHandle
*)+0000001E)
0055A7BB Module(sqlservr+0015A7BB)
(COptExpr::DeriveGroupProperties(unsigned long)+000000B3)
0055A765 Module(sqlservr+0015A765)
(COptExpr::DeriveGroupProperties(unsigned long)+0000005D)
0048BEA2 Module(sqlservr+0008BEA2)
(CImpRuleBaseJoinToIdxLookup::BuildSubstitutes(cla ss
COptExpr *,class CRuleContext *,class CRuleReturn *)
+00000ECC)
00589104 Module(sqlservr+00189104)
(CTask_ApplyRule::Perform(int)+00000268)
0058C939 Module(sqlservr+0018C939) (CMemo::ExecuteTasks
(class COptTask *,int,int)+0000014D)
0058CF2D Module(sqlservr+0018CF2D) (CMemo::OptimizeQuery
(class CQuery *,class COptExpr *,double *,int,int,struct
s_OptimPlans *)+0000051A)
0058CADF Module(sqlservr+0018CADF)
(COptContext::PexprSearchPlan(class COptExpr *)+00000155)
0055E100 Module(sqlservr+0015E100)
(COptContext::PcxteOptimizeQuery(class COptExpr *,class
DRgCId *)+00000B7A)
0055FFBB Module(sqlservr+0015FFBB) (CQuery::Optimize(void)
+00000416)
0055FD54 Module(sqlservr+0015FD54) (CQuery::Optimize
(unsigned long)+00000030)
005642C6 Module(sqlservr+001642C6) (CCvtTree::PqryFromTree
(class TREE *,class IMemObj *,class CRangeCollection
*,unsigned long,class CCompPlan *)+000002C4)
00564019 Module(sqlservr+00164019) (BuildQueryFromTree
(class TREE *,class IMemObj *,class IMemObj *,class
IQueryObj * *,class CRangeCollection *,unsigned long,class
CCompPlan *)+00000046)
00563F78 Module(sqlservr+00163F78) (CStmtQuery::InitQuery
(class CAlgStmt *,class CCompPlan *,unsigned long)
+0000014B)
0049DA48 Module(sqlservr+0009DA48) (CStmtSelect::Init
(class CAlgStmt *,class CCompPlan *,class IBrowseMode *)
+00000091)
00447078 Module(sqlservr+00047078) (CCompPlan::FCompileStep
(class CAlgStmt *,class CStatement * *)+00000AE7)
004510FE Module(sqlservr+000510FE) (CProchdr::FCompile
(class CCompPlan *,class CParamExchange *)+00000D15)
00415080 Module(sqlservr+00015080) (CSQLSource::FTransform
(class CParamExchange *)+0000037C)
004592CE Module(sqlservr+000592CE) (CSQLStrings::FTransform
(class CParamExchange *)+000001A8)
0041534F Module(sqlservr+0001534F) (CSQLSource::Execute
(class CParamExchange *)+00000176)
00459A54 Module(sqlservr+00059A54) (language_exec(struct
srv_proc *)+000003C8)
004175D8 Module(sqlservr+000175D8) (process_commands
(struct srv_proc *)+000000E0)
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class
UmsWorkQueue *)+00000264)
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)
+000000BC)
78008454 Module(MSVCRT+00008454) (_endthread+000000C1)
7C57438B Module(KERNEL32+0000438B) (TlsSetValue+000000F0)
2004-08-21 11:56:16.43 spid52 Error: 0, Severity: 19,
State: 0
2004-08-21 11:56:16.43 spid52 language_exec: Process 52
generated an access violation. SQL Server is terminating
this process..
You may want to contact PSS with details on how this occured:
http://support.microsoft.com/default...s;Prodoffer41a
( If this is due to a bug, you may get your money refunded )
Anith

anynone seen this error

Hello,
Does anyone seen this error before?
I've searched through the web, books online, and googles groups. NOTHING.
Event ID 17052, Category 2
Error 50501, severity 16
State 1
DBE_add_event: Invalid taskbinder (taskbinder id=0, rows=0)
This event occurs very often, like each 4-5 minutes....
ThanksIs this error from SQL Server as source? If so, then it is a user-defined error (as it has > 50000
as error number). I suggest you check this with your application vendor.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dany" <dany_langlois@.yahoo.com> wrote in message
news:4080a045.0312160725.2f6df8db@.posting.google.com...
> Hello,
> Does anyone seen this error before?
> I've searched through the web, books online, and googles groups. NOTHING.
>
> Event ID 17052, Category 2
> Error 50501, severity 16
> State 1
> DBE_add_event: Invalid taskbinder (taskbinder id=0, rows=0)
>
> This event occurs very often, like each 4-5 minutes....
> Thankssql

Anybody seen this error? - 37000 - Storage Allocation record not available

Hi all,
I have a problem with a SQL Server database throwing an error I have
never seen before, and cannot remember ever seeing anywhere else either
(even a google search doesn't throw anything up).
Here's the exact text...
37000
[Microsoft][ODBC SQL Server Driver][SQL Server]Storage Allocation
record not available
The error was originally caused by the database server running out of
space on the system volume (containing the log files), but that has now
been rectified, and we have confirmed that the growth rate of the log
files are smaller than the amount of free space.
What on earth does it mean anyway - "storage allocation record not
available" ?Hi Jonathan
I am not familiar with the error message, but is there any more information
in the SQL Server error log. Does Stopping/Starting SQL server help? Have you
tried defragmenting the disc? Are you using a fixed value for file growth on
all the databases (including tempdb).
John
"jonathan.beckett" wrote:
> Hi all,
> I have a problem with a SQL Server database throwing an error I have
> never seen before, and cannot remember ever seeing anywhere else either
> (even a google search doesn't throw anything up).
> Here's the exact text...
> 37000
> [Microsoft][ODBC SQL Server Driver][SQL Server]Storage Allocation
> record not available
> The error was originally caused by the database server running out of
> space on the system volume (containing the log files), but that has now
> been rectified, and we have confirmed that the growth rate of the log
> files are smaller than the amount of free space.
> What on earth does it mean anyway - "storage allocation record not
> available" ?
>|||First, what version of SQL server are you using?
2nd, is the the error still recurring?
I never seen it before but i expect it has to do wit the SQL engine- which
generates this message when the log was full.
Greetz,
Fling Dutch men.|||> I am not familiar with the error message, but is there any more information
> in the SQL Server error log. Does Stopping/Starting SQL server help? Have you
> tried defragmenting the disc? Are you using a fixed value for file growth on
> all the databases (including tempdb).
> John
It's SQL Server 7 on NT.
Stopping and starting SQL Server does not resolve the problem.
Defragmentation is not an issue either. We're really stumped, and have
opened a call with the makers of the software that is reporting the
error from SQL Server.
> "jonathan.beckett" wrote:
> > Hi all,
> >
> > I have a problem with a SQL Server database throwing an error I have
> > never seen before, and cannot remember ever seeing anywhere else either
> > (even a google search doesn't throw anything up).
> >
> > Here's the exact text...
> > 37000
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Storage Allocation
> > record not available
> >
> > The error was originally caused by the database server running out of
> > space on the system volume (containing the log files), but that has now
> > been rectified, and we have confirmed that the growth rate of the log
> > files are smaller than the amount of free space.
> >
> > What on earth does it mean anyway - "storage allocation record not
> > available" ?
> >
> >|||Hate_orphaned_users wrote:
> First, what version of SQL server are you using?
> 2nd, is the the error still recurring?
> I never seen it before but i expect it has to do wit the SQL engine- which
> generates this message when the log was full.
SQL Server 7 on NT
The error is still recurring, but only for a specific operation within
the software that uses the database. Everything else appears to be
fine.
It is very, very strange - we have opened a call with the makers of the
software that is reporting the error (annoyingly it does not appear in
their knowledgebase, and Microsoft have no information on this specific
error from SQL Server either).|||Hi Jonathan,
This is a memory storage allocation error. You can get more details by
running the DBCC MEMORYSTATUS command. For more info go to:
http://support.microsoft.com/kb/907877/en-us
The first I'd check is to make sure you haven't run out if space on
TempDB.
On 27 Dec 2006 03:08:11 -0800, "jonathan.beckett"
<jonathan.beckett@.gmail.com> wrote:
>Hi all,
>I have a problem with a SQL Server database throwing an error I have
>never seen before, and cannot remember ever seeing anywhere else either
>(even a google search doesn't throw anything up).
>Here's the exact text...
>37000
>[Microsoft][ODBC SQL Server Driver][SQL Server]Storage Allocation
>record not available
>The error was originally caused by the database server running out of
>space on the system volume (containing the log files), but that has now
>been rectified, and we have confirmed that the growth rate of the log
>files are smaller than the amount of free space.
>What on earth does it mean anyway - "storage allocation record not
>available" ?

Anybody seen this error? - 37000 - Storage Allocation record not a

> I am not familiar with the error message, but is there any more information
> in the SQL Server error log. Does Stopping/Starting SQL server help? Have you
> tried defragmenting the disc? Are you using a fixed value for file growth on
> all the databases (including tempdb).
> John
It's SQL Server 7 on NT.
Stopping and starting SQL Server does not resolve the problem.
Defragmentation is not an issue either. We're really stumped, and have
opened a call with the makers of the software that is reporting the
error from SQL Server.
[vbcol=seagreen]
> "jonathan.beckett" wrote:
Hi Jonathan
You should be able to run SQL profiler to find out what commands are being
sent to SQL Server and possibly track down what is causing it.
John
"jonathan.beckett" wrote:

>
> It's SQL Server 7 on NT.
> Stopping and starting SQL Server does not resolve the problem.
> Defragmentation is not an issue either. We're really stumped, and have
> opened a call with the makers of the software that is reporting the
> error from SQL Server.
>
>
>

Anybody know how to solve this error when accessing a linked server that isn't a SQL data

TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476[ /url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=7311&LinkId=20476[/url]
BUTTONS:
OK
The links take you to the "We're sorry page," which doesn't help.
Thanks,
Tony
Tony
Could be a bug
http://connect.microsoft.com/SQLServer/feedback/Validation.aspx?FeedbackID=261012
"Tony" <tsheehan@.tji-holdings.com> wrote in message news:ekk$S$cbIHA.4172@.TK2MSFTNGP02.phx.gbl...
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476[ /url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .3042&EvtSrc=MSSQLServer&EvtID=7311&LinkId=20476[/url]
BUTTONS:
OK
The links take you to the "We're sorry page," which doesn't help.
Thanks,
Tony

Any way to suppress ADO error from stored proc?

Hi,
I have a stored procedure that can insert multiple rows but it does them in
a loop so the inserts happen one at a time. There is a unique constraint on
the table and sometimes the insert violates this and the constraint
violation gets returned as an error. What I want to do is to trap the error
report it in another table and continue processing all of the inserts and
not have the unique constraint error returned to the calling application.
The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
procedure processing all the inserts and then I just do a RETURN(0) at the
end. Unfortunately this still returns the unique constraint violation even
though I am doing the RETURN(0). There doesn't seem to be any way to keep
the unique constraint from going through to ADO and reporting back an error.
The procedure has now been changed to check for the unique problem before
performing the insert but that seems ineffecient so I would rather just
suppress the error if possible. Thanks in advance for any ideas.
Wayne AntinoreHi Wayne.
Unfortunately, T-SQL offers no way to either suppress errors or provide
run-time inspection of full error messages.
You will have to implement exception management in the client to ignore
error message passed back up to ADO if you really want to take the approach
you've described.
Many people take the approach you've described & perform the distinct query
before performing the insert, but even this is not fool-proof in a high
concurrency environment.
fwiw - this has been bitched about for years & we've got some better error
handling tools coming in the next version of SQL Server. For now though,
you're stuck with this problem as you've described.
Regards,
Greg Linwood
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Wayne
Look at SET XACT_ABORT on BOL
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Hi Uri.
I think this won't help Wayne.
SET XACT_ABORT ON will attempt to rollback an entire transaction on any
error. The way I read Wayne's post, he wants his transaction to continue
processing other rows, even after a constraint violation, so it would have
the opposite effect from what he's after.
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> Wayne
> Look at SET XACT_ABORT on BOL
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I have a stored procedure that can insert multiple rows but it does them
> in
> > a loop so the inserts happen one at a time. There is a unique
constraint
> on
> > the table and sometimes the insert violates this and the constraint
> > violation gets returned as an error. What I want to do is to trap the
> error
> > report it in another table and continue processing all of the inserts
and
> > not have the unique constraint error returned to the calling
application.
> > The procedure was changed to check @.@.ERROR and use a CONTINUE to keep
the
> > procedure processing all the inserts and then I just do a RETURN(0) at
the
> > end. Unfortunately this still returns the unique constraint violation
> even
> > though I am doing the RETURN(0). There doesn't seem to be any way to
keep
> > the unique constraint from going through to ADO and reporting back an
> error.
> > The procedure has now been changed to check for the unique problem
before
> > performing the insert but that seems ineffecient so I would rather just
> > suppress the error if possible. Thanks in advance for any ideas.
> >
> > Wayne Antinore
> >
> >
>|||Hi,Greg
>The way I read Wayne's post, he wants his transaction to continue
>processing other rows, even after a constraint violation, so it would have
>the opposite effect from what he's after.
So ,he can use
This is an example from BOL
CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO
/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back, but
XACT_ABORT was OFF and rest of transaction
succeeded.
*/
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Hi Uri.
> I think this won't help Wayne.
> SET XACT_ABORT ON will attempt to rollback an entire transaction on any
> error. The way I read Wayne's post, he wants his transaction to continue
> processing other rows, even after a constraint violation, so it would have
> the opposite effect from what he's after.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > Wayne
> > Look at SET XACT_ABORT on BOL
> >
> > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > > I have a stored procedure that can insert multiple rows but it does
them
> > in
> > > a loop so the inserts happen one at a time. There is a unique
> constraint
> > on
> > > the table and sometimes the insert violates this and the constraint
> > > violation gets returned as an error. What I want to do is to trap the
> > error
> > > report it in another table and continue processing all of the inserts
> and
> > > not have the unique constraint error returned to the calling
> application.
> > > The procedure was changed to check @.@.ERROR and use a CONTINUE to keep
> the
> > > procedure processing all the inserts and then I just do a RETURN(0) at
> the
> > > end. Unfortunately this still returns the unique constraint violation
> > even
> > > though I am doing the RETURN(0). There doesn't seem to be any way to
> keep
> > > the unique constraint from going through to ADO and reporting back an
> > error.
> > > The procedure has now been changed to check for the unique problem
> before
> > > performing the insert but that seems ineffecient so I would rather
just
> > > suppress the error if possible. Thanks in advance for any ideas.
> > >
> > > Wayne Antinore
> > >
> > >
> >
> >
>|||Hi Uri.
I now see you intended to set it OFF rather than ON.
However - Wayne's problem still remains that errors will be thrown through
ADO to his client, regardless of how XACT_ABORT is set.
Whether you set XACT_ABORT either ON / OFF won't solve his problem..
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Hi,Greg
> >The way I read Wayne's post, he wants his transaction to continue
> >processing other rows, even after a constraint violation, so it would
have
> >the opposite effect from what he's after.
>
> So ,he can use
> This is an example from BOL
> CREATE TABLE t1 (a int PRIMARY KEY)
> CREATE TABLE t2 (a int REFERENCES t1(a))
> GO
> INSERT INTO t1 VALUES (1)
> INSERT INTO t1 VALUES (3)
> INSERT INTO t1 VALUES (4)
> INSERT INTO t1 VALUES (6)
> GO
> SET XACT_ABORT OFF
> GO
> BEGIN TRAN
> INSERT INTO t2 VALUES (1)
> INSERT INTO t2 VALUES (2) /* Foreign key error */
> INSERT INTO t2 VALUES (3)
> COMMIT TRAN
> GO
> /* Select shows only keys 1 and 3 added.
> Key 2 insert failed and was rolled back, but
> XACT_ABORT was OFF and rest of transaction
> succeeded.
> */
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Hi Uri.
> >
> > I think this won't help Wayne.
> >
> > SET XACT_ABORT ON will attempt to rollback an entire transaction on any
> > error. The way I read Wayne's post, he wants his transaction to continue
> > processing other rows, even after a constraint violation, so it would
have
> > the opposite effect from what he's after.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > Wayne
> > > Look at SET XACT_ABORT on BOL
> > >
> > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > > I have a stored procedure that can insert multiple rows but it does
> them
> > > in
> > > > a loop so the inserts happen one at a time. There is a unique
> > constraint
> > > on
> > > > the table and sometimes the insert violates this and the constraint
> > > > violation gets returned as an error. What I want to do is to trap
the
> > > error
> > > > report it in another table and continue processing all of the
inserts
> > and
> > > > not have the unique constraint error returned to the calling
> > application.
> > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
keep
> > the
> > > > procedure processing all the inserts and then I just do a RETURN(0)
at
> > the
> > > > end. Unfortunately this still returns the unique constraint
violation
> > > even
> > > > though I am doing the RETURN(0). There doesn't seem to be any way
to
> > keep
> > > > the unique constraint from going through to ADO and reporting back
an
> > > error.
> > > > The procedure has now been changed to check for the unique problem
> > before
> > > > performing the insert but that seems ineffecient so I would rather
> just
> > > > suppress the error if possible. Thanks in advance for any ideas.
> > > >
> > > > Wayne Antinore
> > > >
> > > >
> > >
> > >
> >
> >
>|||THere is no way to suppress the Constraint violation error from the stored
procedure ( on the back end.).
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Greg
I hope it will be possible with "Yukon" to use begin try catch ( I don't
remember exactly what is a syntax)
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:#2qpMoNuDHA.2508@.TK2MSFTNGP12.phx.gbl...
> Hi Uri.
> I now see you intended to set it OFF rather than ON.
> However - Wayne's problem still remains that errors will be thrown through
> ADO to his client, regardless of how XACT_ABORT is set.
> Whether you set XACT_ABORT either ON / OFF won't solve his problem..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Hi,Greg
> > >The way I read Wayne's post, he wants his transaction to continue
> > >processing other rows, even after a constraint violation, so it would
> have
> > >the opposite effect from what he's after.
> >
> >
> > So ,he can use
> > This is an example from BOL
> >
> > CREATE TABLE t1 (a int PRIMARY KEY)
> > CREATE TABLE t2 (a int REFERENCES t1(a))
> > GO
> > INSERT INTO t1 VALUES (1)
> > INSERT INTO t1 VALUES (3)
> > INSERT INTO t1 VALUES (4)
> > INSERT INTO t1 VALUES (6)
> > GO
> > SET XACT_ABORT OFF
> > GO
> > BEGIN TRAN
> > INSERT INTO t2 VALUES (1)
> > INSERT INTO t2 VALUES (2) /* Foreign key error */
> > INSERT INTO t2 VALUES (3)
> > COMMIT TRAN
> > GO
> >
> > /* Select shows only keys 1 and 3 added.
> > Key 2 insert failed and was rolled back, but
> > XACT_ABORT was OFF and rest of transaction
> > succeeded.
> > */
> >
> > "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote
in
> > message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Hi Uri.
> > >
> > > I think this won't help Wayne.
> > >
> > > SET XACT_ABORT ON will attempt to rollback an entire transaction on
any
> > > error. The way I read Wayne's post, he wants his transaction to
continue
> > > processing other rows, even after a constraint violation, so it would
> have
> > > the opposite effect from what he's after.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > > Wayne
> > > > Look at SET XACT_ABORT on BOL
> > > >
> > > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > Hi,
> > > > > I have a stored procedure that can insert multiple rows but it
does
> > them
> > > > in
> > > > > a loop so the inserts happen one at a time. There is a unique
> > > constraint
> > > > on
> > > > > the table and sometimes the insert violates this and the
constraint
> > > > > violation gets returned as an error. What I want to do is to trap
> the
> > > > error
> > > > > report it in another table and continue processing all of the
> inserts
> > > and
> > > > > not have the unique constraint error returned to the calling
> > > application.
> > > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
> keep
> > > the
> > > > > procedure processing all the inserts and then I just do a
RETURN(0)
> at
> > > the
> > > > > end. Unfortunately this still returns the unique constraint
> violation
> > > > even
> > > > > though I am doing the RETURN(0). There doesn't seem to be any way
> to
> > > keep
> > > > > the unique constraint from going through to ADO and reporting back
> an
> > > > error.
> > > > > The procedure has now been changed to check for the unique problem
> > > before
> > > > > performing the insert but that seems ineffecient so I would rather
> > just
> > > > > suppress the error if possible. Thanks in advance for any ideas.
> > > > >
> > > > > Wayne Antinore
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yep - Try / Catch will be good in Yukon.
I'm not sure if it allows error suppression, but I sure hope so!
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#yR3yTOuDHA.1596@.TK2MSFTNGP10.phx.gbl...
> Greg
> I hope it will be possible with "Yukon" to use begin try catch ( I don't
> remember exactly what is a syntax)
>
>
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:#2qpMoNuDHA.2508@.TK2MSFTNGP12.phx.gbl...
> > Hi Uri.
> >
> > I now see you intended to set it OFF rather than ON.
> >
> > However - Wayne's problem still remains that errors will be thrown
through
> > ADO to his client, regardless of how XACT_ABORT is set.
> >
> > Whether you set XACT_ABORT either ON / OFF won't solve his problem..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Hi,Greg
> > > >The way I read Wayne's post, he wants his transaction to continue
> > > >processing other rows, even after a constraint violation, so it would
> > have
> > > >the opposite effect from what he's after.
> > >
> > >
> > > So ,he can use
> > > This is an example from BOL
> > >
> > > CREATE TABLE t1 (a int PRIMARY KEY)
> > > CREATE TABLE t2 (a int REFERENCES t1(a))
> > > GO
> > > INSERT INTO t1 VALUES (1)
> > > INSERT INTO t1 VALUES (3)
> > > INSERT INTO t1 VALUES (4)
> > > INSERT INTO t1 VALUES (6)
> > > GO
> > > SET XACT_ABORT OFF
> > > GO
> > > BEGIN TRAN
> > > INSERT INTO t2 VALUES (1)
> > > INSERT INTO t2 VALUES (2) /* Foreign key error */
> > > INSERT INTO t2 VALUES (3)
> > > COMMIT TRAN
> > > GO
> > >
> > > /* Select shows only keys 1 and 3 added.
> > > Key 2 insert failed and was rolled back, but
> > > XACT_ABORT was OFF and rest of transaction
> > > succeeded.
> > > */
> > >
> > > "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote
> in
> > > message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > Hi Uri.
> > > >
> > > > I think this won't help Wayne.
> > > >
> > > > SET XACT_ABORT ON will attempt to rollback an entire transaction on
> any
> > > > error. The way I read Wayne's post, he wants his transaction to
> continue
> > > > processing other rows, even after a constraint violation, so it
would
> > have
> > > > the opposite effect from what he's after.
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > > > Wayne
> > > > > Look at SET XACT_ABORT on BOL
> > > > >
> > > > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > Hi,
> > > > > > I have a stored procedure that can insert multiple rows but it
> does
> > > them
> > > > > in
> > > > > > a loop so the inserts happen one at a time. There is a unique
> > > > constraint
> > > > > on
> > > > > > the table and sometimes the insert violates this and the
> constraint
> > > > > > violation gets returned as an error. What I want to do is to
trap
> > the
> > > > > error
> > > > > > report it in another table and continue processing all of the
> > inserts
> > > > and
> > > > > > not have the unique constraint error returned to the calling
> > > > application.
> > > > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
> > keep
> > > > the
> > > > > > procedure processing all the inserts and then I just do a
> RETURN(0)
> > at
> > > > the
> > > > > > end. Unfortunately this still returns the unique constraint
> > violation
> > > > > even
> > > > > > though I am doing the RETURN(0). There doesn't seem to be any
way
> > to
> > > > keep
> > > > > > the unique constraint from going through to ADO and reporting
back
> > an
> > > > > error.
> > > > > > The procedure has now been changed to check for the unique
problem
> > > > before
> > > > > > performing the insert but that seems ineffecient so I would
rather
> > > just
> > > > > > suppress the error if possible. Thanks in advance for any
ideas.
> > > > > >
> > > > > > Wayne Antinore
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Uri, Greg, and Wayne for your replies. I didn't think there was any
way to do this currently but I'm glad you confirmed it for me. This will be
a very useful feature in Yukon
Thanks again,
Wayne
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>

Any way to repair the damage backup file?

Maybe post to wrong group, I am sorry.
If my backup file has error so that the restore is failed, is there any way
to repair the damage backup file?
(The restore procedure of 2005 report there is an error on page 65535:-1)
Thx.
To the best of my knowledge, there's no repair option for a backup. But in 2005, you have the
CONTINUE_AFTER_ERROR option for the RESTORE command. Of could, you will then have a database win
some corruption which you need to handle.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:%23VMMehtDGHA.644@.TK2MSFTNGP09.phx.gbl...
> Maybe post to wrong group, I am sorry.
> If my backup file has error so that the restore is failed, is there any way to repair the damage
> backup file?
> (The restore procedure of 2005 report there is an error on page 65535:-1)
> Thx.
>
|||I see. Thanks. Good news for me.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
??:eWH4mgwDGHA.2924@.tk2msftngp13.phx.gbl...
> To the best of my knowledge, there's no repair option for a backup. But in
> 2005, you have the CONTINUE_AFTER_ERROR option for the RESTORE command. Of
> could, you will then have a database win some corruption which you need to
> handle.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:%23VMMehtDGHA.644@.TK2MSFTNGP09.phx.gbl...
>

Any way to repair the damage backup file?

Maybe post to wrong group, I am sorry.
If my backup file has error so that the restore is failed, is there any way
to repair the damage backup file?
(The restore procedure of 2005 report there is an error on page 65535:-1)
Thx.To the best of my knowledge, there's no repair option for a backup. But in 2005, you have the
CONTINUE_AFTER_ERROR option for the RESTORE command. Of could, you will then have a database win
some corruption which you need to handle.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:%23VMMehtDGHA.644@.TK2MSFTNGP09.phx.gbl...
> Maybe post to wrong group, I am sorry.
> If my backup file has error so that the restore is failed, is there any way to repair the damage
> backup file?
> (The restore procedure of 2005 report there is an error on page 65535:-1)
> Thx.
>|||I see. Thanks. Good news for me.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
'?:eWH4mgwDGHA.2924@.tk2msftngp13.phx.gbl...
> To the best of my knowledge, there's no repair option for a backup. But in
> 2005, you have the CONTINUE_AFTER_ERROR option for the RESTORE command. Of
> could, you will then have a database win some corruption which you need to
> handle.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:%23VMMehtDGHA.644@.TK2MSFTNGP09.phx.gbl...
>> Maybe post to wrong group, I am sorry.
>> If my backup file has error so that the restore is failed, is there any
>> way to repair the damage backup file?
>> (The restore procedure of 2005 report there is an error on page 65535:-1)
>> Thx.
>

Any way to repair the damage backup file?

Maybe post to wrong group, I am sorry.
If my backup file has error so that the restore is failed, is there any way
to repair the damage backup file?
(The restore procedure of 2005 report there is an error on page 65535:-1)
Thx.To the best of my knowledge, there's no repair option for a backup. But in 2
005, you have the
CONTINUE_AFTER_ERROR option for the RESTORE command. Of could, you will then
have a database win
some corruption which you need to handle.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:%23VMMehtDGHA.644@.TK2MSFTNGP09.phx.gb
l...
> Maybe post to wrong group, I am sorry.
> If my backup file has error so that the restore is failed, is there any wa
y to repair the damage
> backup file?
> (The restore procedure of 2005 report there is an error on page 65535:-1)
> Thx.
>|||I see. Thanks. Good news for me.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
'?:eWH4mgwDGHA.2924@.tk2msftngp13.phx.gbl...
> To the best of my knowledge, there's no repair option for a backup. But in
> 2005, you have the CONTINUE_AFTER_ERROR option for the RESTORE command. Of
> could, you will then have a database win some corruption which you need to
> handle.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:%23VMMehtDGHA.644@.TK2MSFTNGP09.phx.gbl...
>sql

any way to attach a printscreen

Hi ,
Am i able to attach print screens as the error message given by SQL is quite
long
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
On Mon, 20 Mar 2006 10:04:29 GMT, maxzsim via droptable.com wrote:

>Hi ,
> Am i able to attach print screens as the error message given by SQL is quite
>long
Hi maxzsim,
Depends on the front-end you are using toa ccess the groups. I see in
the headers and in the added ad that you use SQLMonster. This is nothing
but a portal to the groups - they are not hosted by SQLMonster, but they
are Usenet groups. There are more portals to these groups, similar to
SQLMonster, that are used by other users. And many users use a
newsreader and a subscription to a usenet provider to access the groups
directly.
Though some of the portals would allow you to attach a screenprint, you
really shouldn't. In usenet, all binary content should be in one of the
many "binaries" groups. This group is "text only". Not all usenet
providers respect that unwritten law of usenet - but many do, and they
would either strip the attachment off of your message before propagating
it, or block the message completely.
The best way to get a long error message here is to run the offending
query in Query Analyzer, then use copy and paste to get the error
message from the QA output screen to the message.
And if you ever do need to post a screenshot, post it somewhere on a web
site where you have access, and post a link (plus a description of what
you link to) in the message.
Hugo Kornelis, SQL Server MVP
|||Hi Hugo ,
tk you very much for ur explanation and advice
rdgs
Hugo Kornelis wrote:
>Hi maxzsim,
>Depends on the front-end you are using toa ccess the groups. I see in
>the headers and in the added ad that you use SQLMonster. This is nothing
>but a portal to the groups - they are not hosted by SQLMonster, but they
>are Usenet groups. There are more portals to these groups, similar to
>SQLMonster, that are used by other users. And many users use a
>newsreader and a subscription to a usenet provider to access the groups
>directly.
>Though some of the portals would allow you to attach a screenprint, you
>really shouldn't. In usenet, all binary content should be in one of the
>many "binaries" groups. This group is "text only". Not all usenet
>providers respect that unwritten law of usenet - but many do, and they
>would either strip the attachment off of your message before propagating
>it, or block the message completely.
>The best way to get a long error message here is to run the offending
>query in Query Analyzer, then use copy and paste to get the error
>message from the QA output screen to the message.
>And if you ever do need to post a screenshot, post it somewhere on a web
>site where you have access, and post a link (plus a description of what
>you link to) in the message.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1

any way to attach a printscreen

Hi ,
Am i able to attach print screens as the error message given by SQL is quite
long
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1On Mon, 20 Mar 2006 10:04:29 GMT, maxzsim via droptable.com wrote:

>Hi ,
> Am i able to attach print screens as the error message given by SQL is qui
te
>long
Hi maxzsim,
Depends on the front-end you are using toa ccess the groups. I see in
the headers and in the added ad that you use SQLMonster. This is nothing
but a portal to the groups - they are not hosted by SQLMonster, but they
are Usenet groups. There are more portals to these groups, similar to
SQLMonster, that are used by other users. And many users use a
newsreader and a subscription to a usenet provider to access the groups
directly.
Though some of the portals would allow you to attach a screenprint, you
really shouldn't. In usenet, all binary content should be in one of the
many "binaries" groups. This group is "text only". Not all usenet
providers respect that unwritten law of usenet - but many do, and they
would either strip the attachment off of your message before propagating
it, or block the message completely.
The best way to get a long error message here is to run the offending
query in Query Analyzer, then use copy and paste to get the error
message from the QA output screen to the message.
And if you ever do need to post a screenshot, post it somewhere on a web
site where you have access, and post a link (plus a description of what
you link to) in the message.
Hugo Kornelis, SQL Server MVP|||Hi Hugo ,
tk you very much for ur explanation and advice
rdgs
Hugo Kornelis wrote:
>Hi maxzsim,
>Depends on the front-end you are using toa ccess the groups. I see in
>the headers and in the added ad that you use SQLMonster. This is nothing
>but a portal to the groups - they are not hosted by SQLMonster, but they
>are Usenet groups. There are more portals to these groups, similar to
>SQLMonster, that are used by other users. And many users use a
>newsreader and a subscription to a usenet provider to access the groups
>directly.
>Though some of the portals would allow you to attach a screenprint, you
>really shouldn't. In usenet, all binary content should be in one of the
>many "binaries" groups. This group is "text only". Not all usenet
>providers respect that unwritten law of usenet - but many do, and they
>would either strip the attachment off of your message before propagating
>it, or block the message completely.
>The best way to get a long error message here is to run the offending
>query in Query Analyzer, then use copy and paste to get the error
>message from the QA output screen to the message.
>And if you ever do need to post a screenshot, post it somewhere on a web
>site where you have access, and post a link (plus a description of what
>you link to) in the message.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1

any way to attach a printscreen

Hi ,
Am i able to attach print screens as the error message given by SQL is quite
long
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1On Mon, 20 Mar 2006 10:04:29 GMT, maxzsim via SQLMonster.com wrote:
>Hi ,
> Am i able to attach print screens as the error message given by SQL is quite
>long
Hi maxzsim,
Depends on the front-end you are using toa ccess the groups. I see in
the headers and in the added ad that you use SQLMonster. This is nothing
but a portal to the groups - they are not hosted by SQLMonster, but they
are Usenet groups. There are more portals to these groups, similar to
SQLMonster, that are used by other users. And many users use a
newsreader and a subscription to a usenet provider to access the groups
directly.
Though some of the portals would allow you to attach a screenprint, you
really shouldn't. In usenet, all binary content should be in one of the
many "binaries" groups. This group is "text only". Not all usenet
providers respect that unwritten law of usenet - but many do, and they
would either strip the attachment off of your message before propagating
it, or block the message completely.
The best way to get a long error message here is to run the offending
query in Query Analyzer, then use copy and paste to get the error
message from the QA output screen to the message.
And if you ever do need to post a screenshot, post it somewhere on a web
site where you have access, and post a link (plus a description of what
you link to) in the message.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo ,
tk you very much for ur explanation and advice
rdgs
Hugo Kornelis wrote:
>>Hi ,
>> Am i able to attach print screens as the error message given by SQL is quite
>>long
>Hi maxzsim,
>Depends on the front-end you are using toa ccess the groups. I see in
>the headers and in the added ad that you use SQLMonster. This is nothing
>but a portal to the groups - they are not hosted by SQLMonster, but they
>are Usenet groups. There are more portals to these groups, similar to
>SQLMonster, that are used by other users. And many users use a
>newsreader and a subscription to a usenet provider to access the groups
>directly.
>Though some of the portals would allow you to attach a screenprint, you
>really shouldn't. In usenet, all binary content should be in one of the
>many "binaries" groups. This group is "text only". Not all usenet
>providers respect that unwritten law of usenet - but many do, and they
>would either strip the attachment off of your message before propagating
>it, or block the message completely.
>The best way to get a long error message here is to run the offending
>query in Query Analyzer, then use copy and paste to get the error
>message from the QA output screen to the message.
>And if you ever do need to post a screenshot, post it somewhere on a web
>site where you have access, and post a link (plus a description of what
>you link to) in the message.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1

Any way around this error

I get the error (in red) below when I run this stored procedure (I'm not running in my app but in Query Analyzer) -- Please help me fix this

CREATE PROCEDURE dbo.sp_Employee_GetEmployeeLNameFNameEmpID
(
@.facilityID nvarchar(2),
@.companyID nvarchar(2),
@.deptID nvarchar(20),
@.Period int
)
AS
SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullName FROM Employee e
WHERE e.DeptID = @.deptID AND e.FacilityID = @.facilityID AND e.CompanyID = @.companyID AND e.EmployeeID <> (SELECT ev.EmployeeID FROM EmployeeEval ev
WHERE PeriodID= @.Period)
GO

Server: Msg 512, Level 16, State 1, Procedure sp_Employee_GetEmployeeLNameFNameEmpID, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

WHICH employee do you want from the ev table?
It looks to me like you want a NOT IN instead:
SELECT
e.LastName + ','+ e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
WHEREe.DeptID = @.deptID
AND e.FacilityID = @.facilityID
AND e.CompanyID =@.companyID
AND e.EmployeeID NOT IN
(SELECT ev.EmployeeID
FROMEmployeeEval ev
WHERE PeriodID= @.Period)
|||

Sweet worked perfectly -- my book showed the IN part but I didn't know about the NOT IN.
Thanks a million.

Any suggestions on this error when trying to connect SQL Server 2000?

SQL State: 08001 Native Error: 17
State: 1 Severity: 16
SQL Server Message: [DBNETLIB][ConnectionOpen (Connect()).]
SQL Server does not exist or access denied.Hi,
Possibilities for this error are:-
1. SQL Server service may be down or hung
2. Network issue
3. Need to create an Alias using "Client network utility" specifying IP
address and port number. Use this alias to connect.
Thanks
Hari
MCDBA
"zenDebra" <anonymous@.discussions.microsoft.com> wrote in message
news:addd01c436ac$ba6088e0$a601280a@.phx.gbl...
> SQL State: 08001 Native Error: 17
> State: 1 Severity: 16
> SQL Server Message: [DBNETLIB][ConnectionOpen (Connect()).]
> SQLeat Server does not exist or access denied.|||Have a look at
Potential causes of the "SQL Server Does Not Exist or Access Denied" error
message
http://support.microsoft.com/defaul...b;en-us;Q328306
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"zenDebra" <anonymous@.discussions.microsoft.com> wrote in message
news:addd01c436ac$ba6088e0$a601280a@.phx.gbl...
> SQL State: 08001 Native Error: 17
> State: 1 Severity: 16
> SQL Server Message: [DBNETLIB][ConnectionOpen (Connect()).]
> SQL Server does not exist or access denied.|||Try creating an ODBC DSN to the server and post the entire Error message.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.