Error Reporting from Stored Procedures

How do you report errors from a stored procedure? For example, if the stored
procedure is trying to insert something into a table and the database is
full, how can the stored procedure return an error string to the caller?Hi Swami,
This article might help...
http://www.sqljunkies.com/Article/5...3B6D84BF94.scuk
"Swami" wrote:

> How do you report errors from a stored procedure? For example, if the stor
ed
> procedure is trying to insert something into a table and the database is
> full, how can the stored procedure return an error string to the caller?
>|||Thanks--great article.
"Ryan Randall" wrote:
> Hi Swami,
> This article might help...
> [url]http://www.sqljunkies.com/Article/564F5D77-2F7E-41FB-91C7-353B6D84BF94.scuk[/url
]
>
> "Swami" wrote:
>

Error report: Interactive sorting loses the image reference

I have a table using the interactive sort feature and an image on each row.
On the report server when I sort the column the images are lost and a broken
link is displayed. In the Designer preview window the images are not lost.
Anyone else share my experience, and does anyone know how to work around
this issue?
Thanks,
Steve MunLeeuwIf I switch the image to embedded then it works as expected.
Thanks,
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:O$osPQxtGHA.3392@.TK2MSFTNGP04.phx.gbl...
>I have a table using the interactive sort feature and an image on each row.
>On the report server when I sort the column the images are lost and a
>broken link is displayed. In the Designer preview window the images are
>not lost. Anyone else share my experience, and does anyone know how to work
>around this issue?
> Thanks,
> Steve MunLeeuw
>

Error rendering subreport in table

Hello there.

First some informations:
The Report shows informations about server from our customers. There will be one report per customer. One Customer can have 1 or more servers. I get all servers from a customer with DataSet1. The result is one column with one or more rows (example "Server1", "Server2"...).

Its a really big report so i put all objects in subreport and in the "toplevel"-report i repeat the subreport for each row i get and set the column as parameter (which is defined in subreport).

Heres my problem:
if i put the subreport direct into the report and set a fix value for the server-parameter it works fine. if i put the subreport in table i get the followin error:
"[rsErrorExecutingSubreport] An error occurred while executing the subreport ‘subreport13’: Object reference not set to an instance of an object."

There must be a problem using the table. Funny thing is in this subreport i do the same procedure with another subreport and it works fine. I've "copy&past" the table from the subreport to "toplevel"-report. No chance.

Can someone help me?

Thanks in advance, Tobi

Is this RS 2005 RTM? This sounds like an issue which was solved in RS 2005 SP1 (http://www.microsoft.com/sql/sp1.mspx).

-- Robert

|||

Hi,

We still have the same problem after applying the SP1 on server and client.

Thnx for an yhelp.

Sbastien

|||

If that's the case, then most likely you have a list or table in the main report which contains a subreport. The subreport has not datasets at all and references another subreport.

If this is the case, then try the following workaround: Add a dataset to the subreport. The dataset query can just be as simple as "select 1 as id" - and you don't need to use the dataset anywhere in the subreport. Then delete main and sub reports from the report server and republish - it should work then.

-- Robert

|||Installing the SP1 is not the solution.|||

Please read my previous posting in this thread with a suggested temporary workaround.

-- Robert

|||Now I've found the problem.

I've got two DataSets in the subreport. Both use an period in where-statement (DateFrom and DateThru). The second DataSet caused the parameters to be set as string-parameter.

I can't comprehend why. The fields in the table are declared as DateTime.
I've no idea.

I solved the problem by doing a UNION in the first DataSet. Both have two columns and I use a third colum for identifiing the queries. There are maximum 100 rows in the result set so performance is no problem.

Hope that helps.

Tobi

Error rendering subreport in table

Hello there.

First some informations:
The Report shows informations about server from our customers. There will be one report per customer. One Customer can have 1 or more servers. I get all servers from a customer with DataSet1. The result is one column with one or more rows (example "Server1", "Server2"...).

Its a really big report so i put all objects in subreport and in the "toplevel"-report i repeat the subreport for each row i get and set the column as parameter (which is defined in subreport).

Heres my problem:
if i put the subreport direct into the report and set a fix value for the server-parameter it works fine. if i put the subreport in table i get the followin error:
"[rsErrorExecutingSubreport] An error occurred while executing the subreport ‘subreport13’: Object reference not set to an instance of an object."

There must be a problem using the table. Funny thing is in this subreport i do the same procedure with another subreport and it works fine. I've "copy&past" the table from the subreport to "toplevel"-report. No chance.

Can someone help me?

Thanks in advance, Tobi

Is this RS 2005 RTM? This sounds like an issue which was solved in RS 2005 SP1 (http://www.microsoft.com/sql/sp1.mspx).

-- Robert

|||

Hi,

We still have the same problem after applying the SP1 on server and client.

Thnx for an yhelp.

Sbastien

|||

If that's the case, then most likely you have a list or table in the main report which contains a subreport. The subreport has not datasets at all and references another subreport.

If this is the case, then try the following workaround: Add a dataset to the subreport. The dataset query can just be as simple as "select 1 as id" - and you don't need to use the dataset anywhere in the subreport. Then delete main and sub reports from the report server and republish - it should work then.

-- Robert

|||Installing the SP1 is not the solution.|||

Please read my previous posting in this thread with a suggested temporary workaround.

-- Robert

|||Now I've found the problem.

I've got two DataSets in the subreport. Both use an period in where-statement (DateFrom and DateThru). The second DataSet caused the parameters to be set as string-parameter.

I can't comprehend why. The fields in the table are declared as DateTime.
I've no idea.

I solved the problem by doing a UNION in the first DataSet. Both have two columns and I use a third colum for identifiing the queries. There are maximum 100 rows in the result set so performance is no problem.

Hope that helps.

Tobi

Error Relational and OLAP Engine

Hi Edward,

What is wrong with my cube?

The dbo.Prescription table is in the data source view.

Please advice

Ronald

You are receiving a message during processing of one of your partitions: 'Prescrition'

The error indicates your server cannot find the source table for your partition.

In the previous post I suggested you modify source table for partition 'Prescrition'.

Have you tried it? Did it work?

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

error rebuild index with dbcc

I have an error like this, every time I reindex this table
via db maintenance plan.
Rebuilding indexes for table 'tbJOTrStatusbyWC'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What does it means this error ?
How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
thx.You most probably have an index on a computed columns or an indexed view. And unfortunately, the
maint plan doesn't execute the needed SET commands to be able to rebuild these indexes. You would
have to create your own (TSQL) job where you execute needed SET commands and then DBCC DBREINDEX (or
DBCC INDEXDEFRAG).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:3c9401c37b42$a2255740$a601280a@.phx.gbl...
> I have an error like this, every time I reindex this table
> via db maintenance plan.
> Rebuilding indexes for table 'tbJOTrStatusbyWC'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
> because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What does it means this error ?
> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> thx.|||Tibor is correct in that the maintenance wizard has issues with things such
as computed columns and indexed views. I suggest you create your own
scheduled job to do the DBREINDEX and not use the wizard and all should be
fine.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u$PV9Y2eDHA.3788@.tk2msftngp13.phx.gbl...
> > You are wrong,
> I'm pretty certain that I'm not. But you are welcome to think so, if you
wish.
> I still recommend that you check very closely if you have either indexes
on computed columns or
> indexes on views.
> You do not need any special SET options if you have an PK over several
columns, so it has to be
> something else.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
> news:3d9d01c37b5e$e471ee80$a601280a@.phx.gbl...
> > You are wrong,
> >
> > I don't use a computed columns, I use multi colums(JONo,
> > OPID,seqNo) for my primay key, this is the structure.
> >
> > TrackMainID int 4 1
> > JONo int 4 0
> > OPID int 4 0
> > SeqNo int 4 0
> > QtyIN numeric 5 1
> > QtyOut numeric 5 1
> > QtyOpen numeric 5 1
> > WCID int 4 1
> > FirstInDate datetime 8 1
> > LastOutDate datetime 8 1
> >
> > I still don't understand the message : incorrect
> > settings: 'QUOTED_IDENTIFIER, ARITHABORT'
> >
> > I must setting where ?
> >
> >
> >
> > >--Original Message--
> > >You most probably have an index on a computed columns or
> > an indexed view. And unfortunately, the
> > >maint plan doesn't execute the needed SET commands to be
> > able to rebuild these indexes. You would
> > >have to create your own (TSQL) job where you execute
> > needed SET commands and then DBCC DBREINDEX (or
> > >DBCC INDEXDEFRAG).
> > >
> > >--
> > >Tibor Karaszi, SQL Server MVP
> > >Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> > message
> > >news:3c9401c37b42$a2255740$a601280a@.phx.gbl...
> > >> I have an error like this, every time I reindex this
> > table
> > >> via db maintenance plan.
> > >>
> > >> Rebuilding indexes for table 'tbJOTrStatusbyWC'
> > >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> > >> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> > failed
> > >> because the following SET options have incorrect
> > >> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> > >>
> > >> What does it means this error ?
> > >> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> > >>
> > >> thx.
> > >
> > >
> > >.
> > >
>sql

Error Processingn Cube with time dimension

The strange thing is that- i create two cubes with same data source using the same fact table and the same dimension in both cases.

However with the first one i dont define the dimension as a time dimension and the cube processes fine.

With the second cube i do define the dimension as a time dimension and then the cube processing errors. The error say attribute key cannnot be found? But it was found in the first cube?

Anyone know what might be causing this?

Thanks,

Can you try and use copy/paste functionality in BI Dev studio to make a copy of your existing cube and try to process it then?

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

|||

Thanks for the reply unfortunatly i still get he same error when i try this.

Some more information-

The error i get is that attribute key with value 0 cannot be found. i get as many of these errors as there are nulls in the fact tables date dimension key column. There arent any 0 values.

So it might be that for some reaon the nulls are converted to zeros when a date dimension is added and this throws the referential integrity errors? Please help..

Thanks,

|||

I think you guessed it right.

You need to make sure your fact table doesnt have null's in the date dimension key column.

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

|||

But surely my date dimension field can contain nulls. It is a nullable field in the OLTP system.?

i noticed this http://mgarner.wordpress.com/tag/mdx/ see the 'null = 0' post at the bottom.

|||

This is not a good practice to have a nullable key.

Although Analysis Services allows you to map null keys to Unknown member in the dimension, it is not a good idea.

To solve this problem try define a named calculation in your DSV to map null keys to some pre-defined value on the fly.

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

|||

Thanks for you reply Edward

But.

Analysis services is not converting nulls to unknown if the column is defined as a time dimension. It is converting them to zeros and raisng referential integrity errors.

Is it the case that Analysis services cubes cannot contain nullable time dimensions?

This doesnt sound right to me as date columns often signify an event and therefore a null indicates that the event hasnt occured.

Not being able to define nullable time dimensions if true is a serious flaw in analysis services 2005.

What value could i set the calculated field to in the case of nulls. Surely unknown is the right value for a null date? I certainly wouldnt want to put in someone arbitary date and presumaably it would have to be a vlaue of datetime data type?

[Edit]

Edward below is some test code which builds a test fact table and test time dim. If you define the dim as a time dim then the processing will error if you insert the null in the time dim. Note if you dont define it as a time dim it processess fine with or without the null.

Please have a look at this and let me know.

Thanks alot for all your help.

if object_id('DateDim') is not null

drop table DateDim

go

if object_id('datefact') is not null

drop table datefact

go

create table DateDim

(ID int identity, date smalldatetime)

go

insert DateDim

select getdate()

union all

select getdate()+1

union all

select getdate() +2

union all

select getdate() +3

union all

select getdate() +4

union all

select getdate() +5

create table datefact

(id int identity, datekey int , amount int)

go

insert datefact

select 1, 50

union all

select 1, 50

union all

select 3, 50

union all

select 4, 50

union all

select 5, 50

union all

select 5, 50

/*--Uncomment this so a null is insert into the time dimension reprocess the cube and it will error

union all

select null, 50

*/

sql

Error opening table in Windows 2003

I'm running SQL Server 2000 SP3 on Windows 2003 Server.
I can create tables, indexes, etc. in SQL Enterprise
Manager. But I'm unable to open a table to view the data
in SQL Enterprise Manager. It give me the following
error message: Provider cannot be found. It may not be
properly installed.
I've uninstalled SQL Server 2000 and re-installed it with
SP3 but still running into the same problem.
Anyone know what's going on or have any idea to correct
this error?
ThanksDid you try using Query Analyzer? How are you connecting to SQL Server
service (windows auth, sql auth)?
<tchen@.ceira.com> wrote in message
news:03a901c399be$c3fb7b20$a601280a@.phx.gbl...
> I'm running SQL Server 2000 SP3 on Windows 2003 Server.
> I can create tables, indexes, etc. in SQL Enterprise
> Manager. But I'm unable to open a table to view the data
> in SQL Enterprise Manager. It give me the following
> error message: Provider cannot be found. It may not be
> properly installed.
> I've uninstalled SQL Server 2000 and re-installed it with
> SP3 but still running into the same problem.
> Anyone know what's going on or have any idea to correct
> this error?
> Thanks

error opening table in Enterprise MGR

I am getting an error message when I try to open (to view the rows of) any
table in any of the databases on a SQL 7 server from the server where the
databases actually reside- it says:
"An unexpected error happened during this operation. [Query] - Query
Designer encountered a query error: Unspecified error
This error is new; I am able to query and get results from the tables in
query analyzer and I am able to view the tables through Enterprise Manager
when I link to these databases from another computer. I restarted SQL server
without any luck. Do you think it needs to be reinstalled?
Check the following article which addresses the issue:
FIX: Enterprise Manager Returns Unexpected Error on Table or
View
http://support.microsoft.com/?id=273472
-Sue
On Wed, 26 Jan 2005 07:47:03 -0800, "PatW"
<PatW@.discussions.microsoft.com> wrote:

>I am getting an error message when I try to open (to view the rows of) any
>table in any of the databases on a SQL 7 server from the server where the
>databases actually reside- it says:
>"An unexpected error happened during this operation. [Query] - Query
>Designer encountered a query error: Unspecified error
>This error is new; I am able to query and get results from the tables in
>query analyzer and I am able to view the tables through Enterprise Manager
>when I link to these databases from another computer. I restarted SQL server
>without any luck. Do you think it needs to be reinstalled?
sql

error opening table in Enterprise MGR

I am getting an error message when I try to open (to view the rows of) any
table in any of the databases on a SQL 7 server from the server where the
databases actually reside- it says:
"An unexpected error happened during this operation. [Query] - Query
Designer encountered a query error: Unspecified error
This error is new; I am able to query and get results from the tables in
query analyzer and I am able to view the tables through Enterprise Manager
when I link to these databases from another computer. I restarted SQL serve
r
without any luck. Do you think it needs to be reinstalled?Check the following article which addresses the issue:
FIX: Enterprise Manager Returns Unexpected Error on Table or
View
http://support.microsoft.com/?id=273472
-Sue
On Wed, 26 Jan 2005 07:47:03 -0800, "PatW"
<PatW@.discussions.microsoft.com> wrote:

>I am getting an error message when I try to open (to view the rows of) any
>table in any of the databases on a SQL 7 server from the server where the
>databases actually reside- it says:
>"An unexpected error happened during this operation. [Query] - Query
>Designer encountered a query error: Unspecified error
>This error is new; I am able to query and get results from the tables in
>query analyzer and I am able to view the tables through Enterprise Manager
>when I link to these databases from another computer. I restarted SQL serv
er
>without any luck. Do you think it needs to be reinstalled?

error opening table in Enterprise MGR

I am getting an error message when I try to open (to view the rows of) any
table in any of the databases on a SQL 7 server from the server where the
databases actually reside- it says:
"An unexpected error happened during this operation. [Query] - Query
Designer encountered a query error: Unspecified error
This error is new; I am able to query and get results from the tables in
query analyzer and I am able to view the tables through Enterprise Manager
when I link to these databases from another computer. I restarted SQL server
without any luck. Do you think it needs to be reinstalled?Check the following article which addresses the issue:
FIX: Enterprise Manager Returns Unexpected Error on Table or
View
http://support.microsoft.com/?id=273472
-Sue
On Wed, 26 Jan 2005 07:47:03 -0800, "PatW"
<PatW@.discussions.microsoft.com> wrote:
>I am getting an error message when I try to open (to view the rows of) any
>table in any of the databases on a SQL 7 server from the server where the
>databases actually reside- it says:
>"An unexpected error happened during this operation. [Query] - Query
>Designer encountered a query error: Unspecified error
>This error is new; I am able to query and get results from the tables in
>query analyzer and I am able to view the tables through Enterprise Manager
>when I link to these databases from another computer. I restarted SQL server
>without any luck. Do you think it needs to be reinstalled?

Error on Passing parameter to stored procedure

Hi,

I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@.dtmWarrantyStart', which was not supplied" even though I supplied it in the code

which is

Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@.dtmWarrantyStart", SqlDbType.datetime, 8)
ParamdtmWarrantyStart.Value = dtmWarrantyStart
myCommand.Parameters.Add(ParamdtmWarrantyStart)

below is the stored procedure.

create Proc sp_AddBoard(@.BrandID int,
@.strPcName varchar(50),
@.bitAccounted bit,
@.dtmAccounted datetime,
@.dtmWarrantyStart datetime,
@.dtmWarrantyEnd datetime,
-- @.strDescription varchar(500),
@.intStatus int,
@.ModelNo varchar(50),
@.intMemorySlots int,
@.intMemSlotTaken int,
@.intAgpSlots int,
@.intPCI int,
@.bitWSound bit,
@.bitWLan bit,
@.bitWVideo bit,
@.dtmAcquired datetime,
@.stat bit output,
@.intFSB int) as
if not exists(select strPcName from tblBoards where strPcName=@.strPcName)
begin
insert into tblBoards
(BrandID, strPcName, bitAccounted,
dtmAccounted, dtmWarrantyStart,
dtmWarrantyEnd, --strDescription,
intStatus,
ModelNo, intMemorySlots, intMemSlotTaken,
intAgpSlots, intPCI, bitWLan,
bitWVideo, dtmAcquired,intFSB,bitWSound)

values

(@.BrandID,@.strPcName,@.bitAccounted,
@.dtmAccounted,@.dtmWarrantyStart,
@.dtmWarrantyEnd,--@.strDescription,
@.intStatus,
@.ModelNo,@.intMemorySlots,@.intMemSlotTaken,
@.intAgpSlots,@.intPCI,@.bitWLan,
@.bitWVideo,@.dtmAcquired,@.intFSB,@.bitWSound)
end
else
begin
set @.stat=1
end

The table is also designed to accept nulls on that field but still same error occured.

Please helpjust check if there is any value in the parameter value. the way i normally do is :

myCommand.Parameters.Add(New SqlParameter("@.cusbday",SqlDbType.datetime))
If len(trim(bday.Text)) = 0 Then
myCommand.Parameters("@.cusbday").Value = SqlDateTime.null
Else
myCommand.Parameters("@.cusbday").Value = DateTime.Parse(bday.Text)
End If

also you need to import the namespace System.Data.SqlTypes to use sqldatetime.null

<%@. import Namespace="System.Data.SqlTypes" %>

hthsql

Error on Index!

Hi, I am having a real headache with a table in my db, one of these days
we found that some basic select queries were taking like 4 minutes, to
execute, and they shouldn’t take more that one second, so we were getting
hundreds of timeouts between our applications, I was assigned to check
what was happening, and I ran a script that i found on the web, that takes
every table of the schema and then reindex it, well the thing was in that
specific table the scrip got an error telling me this:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
Server Driver][SQL Server]A column has been specified more than once in
the order by list. Columns in the order by list must be unique.
I check all the indexes in that table and i found 4, but none of them had
an order by column duplicated, I even restore the db somewhere else and
played with those indexes, but it seems like I cant delete one of them the
first one, my index script is the following:
CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
[yearno], [wNo], [Dayno]) ON [PRIMARY]
GO
CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [wNo],
[Dayno]) ON [PRIMARY]
GO
I tried to remove them all and then recreate them , but when I try to
delete the clustered one, the error pops again and doesn’t let me delete
it, I also check the whole db script to see if i got something else in
another place, but I didn’t found anything... any suggestions?
How can i remove all the indexes, and / or get rid of this slowing error?
Thanks for your time
PD: The following is the reindex script
DECLARE @.MyTable varchar(255)
DECLARE myCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.MyTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @.MyTable
DBCC DBREINDEX(@.MyTable, '', 90)
FETCH NEXT FROM myCursor INTO @.MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
--Thanks!
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/- Can check the output of sp_helpindex table_name?
- Can you try to recreate the clustered index with the option DROP_EXISTING?
AMB
"Edgardo Sepulveda" wrote:

> Hi, I am having a real headache with a table in my db, one of these days
> we found that some basic select queries were taking like 4 minutes, to
> execute, and they shouldn’t take more that one second, so we were gettin
g
> hundreds of timeouts between our applications, I was assigned to check
> what was happening, and I ran a script that i found on the web, that takes
> every table of the schema and then reindex it, well the thing was in that
> specific table the scrip got an error telling me this:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
> Server Driver][SQL Server]A column has been specified more than once in
> the order by list. Columns in the order by list must be unique.
> I check all the indexes in that table and i found 4, but none of them had
> an order by column duplicated, I even restore the db somewhere else and
> played with those indexes, but it seems like I cant delete one of them the
> first one, my index script is the following:
> CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
> [yearno], [wNo], [Dayno]) ON [PRIMARY]
> GO
> CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [wNo],
> [Dayno]) ON [PRIMARY]
> GO
> I tried to remove them all and then recreate them , but when I try to
> delete the clustered one, the error pops again and doesn’t let me delete
> it, I also check the whole db script to see if i got something else in
> another place, but I didn’t found anything... any suggestions?
> How can i remove all the indexes, and / or get rid of this slowing error?
>
> Thanks for your time
>
>
> PD: The following is the reindex script
>
>
> DECLARE @.MyTable varchar(255)
> DECLARE myCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN myCursor
> FETCH NEXT FROM myCursor INTO @.MyTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Reindexing Table: ' + @.MyTable
> DBCC DBREINDEX(@.MyTable, '', 90)
> FETCH NEXT FROM myCursor INTO @.MyTable
> END
> CLOSE myCursor
> DEALLOCATE myCursor
> EXEC sp_updatestats
>
> --Thanks!
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>|||The output of the sp_helpindex was:
aaaaatblaction_PK nonclustered, unique, primary key located on PRIMARY ID
Gradectrl nonclustered located on PRIMARY GradeCtrl
hind_1612584833_58A_83A nonclustered, hypothetical, auto create located on
PRIMARY wNo, yearno
hind_1612584833_83A_58A nonclustered, hypothetical, auto create located on
PRIMARY yearno, wNo
hind_c_1612584833_34A clustered, hypothetical, auto create located on
PRIMARY GradeCtrl
hind_c_1612584833_58A clustered, hypothetical, auto create located on
PRIMARY wNo
hind_c_1612584833_83A clustered, hypothetical, auto create located on
PRIMARY yearno
hind_c_1612584833_83A_58A clustered, hypothetical, auto create located on
PRIMARY yearno, wNo
IX_Accountno clustered located on PRIMARY AccountNo, yearno, wNo, Dayno
IX_tblaction nonclustered located on PRIMARY yearno, wNo, Dayno
Linelink nonclustered located on PRIMARY LineLINK
ticketno nonclustered located on PRIMARY TicketNo
I think this is the problem, too many indexes. or too many combinations.
Thanks for the tip Alejandro, any suggestions Based on this info?
Thanks for your Time.
Edgardo

Error on Importing datather PRIMARY FILE GROUP IS FULL

I am trying to import data from one table to another on different servers.
Could not allocate space for object microfilmacion in database Dbsiab
because ther PRIMARY FILE GROUP IS FULL
As the message is indicating, your primary file group where the table exists
is full. You will have to set the file to autogrow, if it isn't already (see
ALTER DATABASE in Books Online). Also, make sure, you have enough free space
on your data disk.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
I am trying to import data from one table to another on different servers.
Could not allocate space for object microfilmacion in database Dbsiab
because ther PRIMARY FILE GROUP IS FULL
|||Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> I am trying to import data from one table to another on different servers.
>
> Could not allocate space for object microfilmacion in database Dbsiab
> because ther PRIMARY FILE GROUP IS FULL
>
|||How can I do that? I didnt understand.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:uT2gn4rhEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
> news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
servers.
>
|||Assuming you are using Outlook Express when you create the post it will have
a section at the top titled "NewsGroups". Normally it will fill in the name
of the group in which you are currently in but you can click on the Icon to
the left of it and choose all the groups you wish this post to appear. Then
when someone answers in one newsgroup it will appear in all of them.
Good luck,
Andrew J. Kelly SQL MVP
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:eUp%23QsshEHA.3200@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> How can I do that? I didnt understand.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:uT2gn4rhEHA.632@.TK2MSFTNGP12.phx.gbl...
one.
> servers.
>

Error on Importing datather PRIMARY FILE GROUP IS FULL

I am trying to import data from one table to another on different servers.
Could not allocate space for object microfilmacion in database Dbsiab
because ther PRIMARY FILE GROUP IS FULLAs the message is indicating, your primary file group where the table exists
is full. You will have to set the file to autogrow, if it isn't already (see
ALTER DATABASE in Books Online). Also, make sure, you have enough free space
on your data disk.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
I am trying to import data from one table to another on different servers.
Could not allocate space for object microfilmacion in database Dbsiab
because ther PRIMARY FILE GROUP IS FULL|||Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> I am trying to import data from one table to another on different servers.
>
> Could not allocate space for object microfilmacion in database Dbsiab
> because ther PRIMARY FILE GROUP IS FULL
>|||How can I do that? I didnt understand.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:uT2gn4rhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
> news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
servers.[vbcol=seagreen]
>|||Assuming you are using Outlook Express when you create the post it will have
a section at the top titled "NewsGroups". Normally it will fill in the name
of the group in which you are currently in but you can click on the Icon to
the left of it and choose all the groups you wish this post to appear. Then
when someone answers in one newsgroup it will appear in all of them.
Good luck,
Andrew J. Kelly SQL MVP
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:eUp%23QsshEHA.3200@.TK2MSFTNGP10.phx.gbl...
> How can I do that? I didnt understand.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:uT2gn4rhEHA.632@.TK2MSFTNGP12.phx.gbl...
one.[vbcol=seagreen]
> servers.
>

Error on Importing datather PRIMARY FILE GROUP IS FULL

I am trying to import data from one table to another on different servers.
Could not allocate space for object microfilmacion in database Dbsiab
because ther PRIMARY FILE GROUP IS FULLAs the message is indicating, your primary file group where the table exists
is full. You will have to set the file to autogrow, if it isn't already (see
ALTER DATABASE in Books Online). Also, make sure, you have enough free space
on your data disk.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
I am trying to import data from one table to another on different servers.
Could not allocate space for object microfilmacion in database Dbsiab
because ther PRIMARY FILE GROUP IS FULL|||Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> I am trying to import data from one table to another on different servers.
>
> Could not allocate space for object microfilmacion in database Dbsiab
> because ther PRIMARY FILE GROUP IS FULL
>|||How can I do that? I didnt understand.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribió en el mensaje
news:uT2gn4rhEHA.632@.TK2MSFTNGP12.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
> news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> > I am trying to import data from one table to another on different
servers.
> >
> >
> > Could not allocate space for object microfilmacion in database Dbsiab
> > because ther PRIMARY FILE GROUP IS FULL
> >
> >
>|||Assuming you are using Outlook Express when you create the post it will have
a section at the top titled "NewsGroups". Normally it will fill in the name
of the group in which you are currently in but you can click on the Icon to
the left of it and choose all the groups you wish this post to appear. Then
when someone answers in one newsgroup it will appear in all of them.
Good luck,
--
Andrew J. Kelly SQL MVP
"Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
news:eUp%23QsshEHA.3200@.TK2MSFTNGP10.phx.gbl...
> How can I do that? I didnt understand.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribió en el mensaje
> news:uT2gn4rhEHA.632@.TK2MSFTNGP12.phx.gbl...
> > Please don't post independently in separate newsgroups. You can add
> > multiple newsgroups to the header and then all the answers appear as
one.
> > See my reply in the other newsgroup.
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Luis Esteban Valencia" <luisvalen@.haceb.com> wrote in message
> > news:uRLheyrhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> > > I am trying to import data from one table to another on different
> servers.
> > >
> > >
> > > Could not allocate space for object microfilmacion in database Dbsiab
> > > because ther PRIMARY FILE GROUP IS FULL
> > >
> > >
> >
> >
>sql

Error on DROP TABLE

Hi,

I was trying to drop a table (or alter it in any way) and the Managment Studio Express gave me an error of timeout. So I say something like DROP TABLE myTable, and it takes a while and gives me the error. Does anybody know how to unlock a table?

Thanks

Hi Alessandro,

As this is Express, I'm guessing that a stopping and restarting the SQL Server service sin't going to impact usage? If not, do this and re-issue your drop command, you'll probably that an application is placing a lock on the table in question and blocking the ddl command.

There are other ways to identify the offending session (spid), but this is probably easiest in your case.

Cheers,

Rob

|||

post back the actual error u gets...

Madhu

|||

Hi

Possible to post the error message to further explore your problem?

At the mean time:

1. Check whether the table really exists in the correct database. Do the folowing:

Select Name from Sys.Objects where name='tablename' -- (if exist)

Drop table databasename.schema.tablename

2. Check that the Login that you are using to execute this query has the approriate permission. You can do so by verify the user properties in the security node.

3. Check whether the table has any reference to other tables

4. Verify whether the table is locked using the current activity. If necessary, you can end the lock by killing the session.

Error on Date Data During Insert

Hi Folx,
I am new to SQL Server and I am struggling with source data for a table with two db_datetime columns. The data can be inserted using native SQL, but errors when I build a Data Flow Container.

Versions:
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

Microsoft SQL Server Management Studio
9.00.1399.00

Error:
[Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component.

Source Row:

101|100|2006/12/19 23:50:01.00|2006/12/19 23:50:01.00|||

How I got here (in approximate order...):

Using ETL I created a Container in which I created a PackageCreated a Flat File ConnectionCreated a Flat File SourceEdited the columns on the Flat File Connection
database timestamp [DT_DBTIMESTAMP]NOTE: corresponding columns on destination table in SQL Server Management Studio are of type datetime

Created an OLE DB DestinationExecuted the package which returned the above named error.

I mucked about with data types, Derived Columns, Data Conversion, etc and Googled for solutions without success. One of the errors (which I could not duplicate...) I noticed during my "experimentation" was an out-of-memory condtion.

Questions:
01. What is the proper format for my source data?
02. Could this be a memory issue? If so, how do I diagnose it?

In advance, thanks for your help.

Bill

ps: be kind to me...not only am i an old guy, but i'm a unix guy too...

Native SQL that works:

insert into "transaction"
(transaction_id,
org_id,
extraction_date,
create_date,
transaction_size,
profile_count,
version_count)
values
(101,100,
'2006/12/19 23:50:01',
'2006/12/19 23:50:01',
NULL,
NULL,
NULL)Are you sure the flat file source is mapped correctly, with correct lengths, and with a date/time format correct for DB_DBTIMESTAMP?

There should be other errors right next to the one you posted... Can you grab those as well?

Phil|||I also wonder if it's a locale issue within SSIS, or something... That is with respect to the leading year on your DT_DBTIMESTAMP column. Just throwing that out there.|||All the error messages:

[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "extraction_date" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

NOTE: OutputColumnWidth is zero and "grayed out" in the Flat File Connection Manager Editor

[Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component.

[Flat File Source [1]] Error: An error occurred while processing file "C:\work\CAPS\SQL\UNL\unloader\cont100.txt" on data row 1.

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.|||
Locale ID is English (United States)|||Have you redirected your error output to a data viewer to be able to inspect the error row(s)?

Have you previewed the data in the flat file connection? Does it all look correct? Can you post a sample row of data?|||
Well Phil, thought I was going to be slick and redirect to a data viewer, but I don't know what I did wrong because now my package done disappeared on me...in other words, the structure no longer shows in the "Solution Explorer". I can see my Control Flow, Data Flow, and Connection Managers, but don't know how to now display/execute the package!

Before that happened, I did open the Flat File Connection Manager Editor and the data looked correct when I selected "Columns" and "Preview".

I will reconstruct after lunch...and if you would like to take this discussion offline, you can send e-mail to tampa_dba@.yahoo.com. If / when this gets resolved, then we can post the solution...

|||The solution explorer is buggy at best.

Find the physical package, and move it to another folder... Then, in solution explorer, you can add existing package. Select the moved package, and you'll be all set again. You'll then be able to delete the "moved" file.|||
Okay, it's back. Is this what you want?

101|100|2006/12/19 23:50:01.00|2006/12/19 23:50:01.00|||

'Name''Data Type''Precision''Scale''Length''Code Page''Sort Key Position''Comparison Flags''Source Component'
'transaction_id''DT_I4''0''0''0''0''0''''Flat File Source'
'org_id''DT_I4''0''0''0''0''0''''Flat File Source'
'extraction_date''DT_DBTIMESTAMP''0''0''0''0''0''''Flat File Source'
'create_date''DT_DBTIMESTAMP''0''0''0''0''0''''Flat File Source'
'transaction_size''DT_I4''0''0''0''0''0''''Flat File Source'
'profile_count''DT_I4''0''0''0''0''0''''Flat File Source'
'version_count''DT_I4''0''0''0''0''0''''Flat File Source'|||SSIS does not like the fraction in your timestamp column. If I drop that, the package works fine. Now I'm trying to figure out why.|||
thanks for your help. dropping the fractional worked. i don't like that it worked because the datatype should support fractional time, but it is not a requirement.

Error on Clustered Index and NText Field

Hi i have a problem whenever i try to update a table in which i have a clustered index and a ntext field i have this error through a ASP page

Microsoft OLE DB Provider for SQL Server (0x80040E14)
The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

ThanksMay be you are trying to update more then one recond in query.

BOL:

If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message.

Modifying a text, ntext, or image column with UPDATE initializes the column, assigns a valid text pointer to it, and allocates at least one data page unless updating the column with NULL.

Note The UPDATE statement is logged. If you are replacing or modifying large blocks of text, ntext, or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged.|||i only have this error when i try to update the ntext field and the data is > than 10 KB...
Any idea and how could i use the updatetext in a asp SCRIPT?
Thanks again

Error on a view

I am getting an error when I open a view err is:

Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

How do I work aorund this issue?

On the assumption that you are using SQL Server 2000, there are a couple of KB articles on this:

http://support.microsoft.com/kb/818406
http://support.microsoft.com/kb/828269

Either way, it seems that the solution is to upgrade to SQL Server 2000 SP4.

Chris

|||

Chris,

That would be a long term fix. Is there any shortterm workaorund for this? pl let me know.

|||

Well, according to the second link I posted:

"If you still receive the "Msg 4414" error message, you may have to break up the query so that a lower number of tables or views are referenced in the query. You may change the view definition that is referenced in the problematic query to lower the number of tables that are referenced in the view."

Chris