Data that is absent

I have a single table which we use to keep track of data from different
physical machines. The columns are: TheDate, TheName, & TheNotes. The Date
column is datetime and the rest are char. Every day each machine we collect
data on gets a row of information: The date of the collection, the machine
name, & any notes on the machine.
Since everyday there is a new row for each machine, what I would like to do
is in Query Analyzer be able to tell if there are any dates missing. Then we
could manually check the machine and see why it is not reporting.
I read an article that said to use the EXISTS command to select data based
on the preence or absence of values. I tried a couple different code samples
,
none showed signs of progress. If I were coding in VB I would use an array
and compare the distinct values. Is there a way in SQL to show values absent
from a known sequence.
Thanks
vmI assume you have a table with one row for each machine you want to manage.
You can do something like this.
tableA = Machine Names
tableB = Holds daily rows
SELECT a.Name FROM tableA AS a
WHERE NOT EXISTS (SELECT * FROM tableB AS b WHERE a.Name = b.Name
AND b.TheDate BETWEEN @.FromDate AND @.ToDate)
Andrew J. Kelly SQL MVP
"vm" <vm@.discussions.microsoft.com> wrote in message
news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
>I have a single table which we use to keep track of data from different
> physical machines. The columns are: TheDate, TheName, & TheNotes. The Date
> column is datetime and the rest are char. Every day each machine we
> collect
> data on gets a row of information: The date of the collection, the machine
> name, & any notes on the machine.
> Since everyday there is a new row for each machine, what I would like to
> do
> is in Query Analyzer be able to tell if there are any dates missing. Then
> we
> could manually check the machine and see why it is not reporting.
> I read an article that said to use the EXISTS command to select data based
> on the preence or absence of values. I tried a couple different code
> samples,
> none showed signs of progress. If I were coding in VB I would use an array
> and compare the distinct values. Is there a way in SQL to show values
> absent
> from a known sequence.
> Thanks
> vm|||Actually all of the data is in a single table. Each row in the table contain
s
the date, machine name, and any notes for the machine in different columns.
The database is simple and probably inefficient because I am fairly new to
SQL and the data I need to store and access is fairly straight forward. The
main reason I went with SQL db over Access, Excel, or even text files is
because the number of machines I have to work with multiplied by the number
of days would have blown everything else away. The data is simple enough tha
t
I could have went with any of the other storage options except for the sheer
number of records.
Is there any way your code will work with a single table. What I have read
on EXISTS mentions multiple tables.
Thanks
vm
vm
"Andrew J. Kelly" wrote:

> I assume you have a table with one row for each machine you want to manage
.
> You can do something like this.
> tableA = Machine Names
> tableB = Holds daily rows
> SELECT a.Name FROM tableA AS a
> WHERE NOT EXISTS (SELECT * FROM tableB AS b WHERE a.Name = b.Name
> AND b.TheDate BETWEEN @.FromDate AND @.ToDate)
> --
> Andrew J. Kelly SQL MVP
>
> "vm" <vm@.discussions.microsoft.com> wrote in message
> news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
>
>|||"vm" <vm@.discussions.microsoft.com> wrote in message
news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
> I have a single table which we use to keep track of data from
different
> physical machines. The columns are: TheDate, TheName, & TheNotes.
The Date
> column is datetime and the rest are char. Every day each machine
we collect
> data on gets a row of information: The date of the collection, the
machine
> name, & any notes on the machine.
> Since everyday there is a new row for each machine, what I would
like to do
> is in Query Analyzer be able to tell if there are any dates
missing. Then we
> could manually check the machine and see why it is not reporting.
> I read an article that said to use the EXISTS command to select
data based
> on the preence or absence of values. I tried a couple different
code samples,
> none showed signs of progress. If I were coding in VB I would use
an array
> and compare the distinct values. Is there a way in SQL to show
values absent
> from a known sequence.
> Thanks
> vm
vm,
Basically, Andrew Kelly was right.
In order to have a known sequence of dates, you would build a
calendar table with your dates.
This way, you can run a NOT EXISTS or Frustrated Outer Join query
against the calendar table, and that will show you the missing
dates.
Sincerely,
Chris O.|||Excellent, that seems the simpelest solution. I am away from work for the
wend, but will try on Monday.
Thanks to both!
vm
"Chris2" wrote:

> "vm" <vm@.discussions.microsoft.com> wrote in message
> news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
> different
> The Date
> we collect
> machine
> like to do
> missing. Then we
> data based
> code samples,
> an array
> values absent
> vm,
> Basically, Andrew Kelly was right.
> In order to have a known sequence of dates, you would build a
> calendar table with your dates.
> This way, you can run a NOT EXISTS or Frustrated Outer Join query
> against the calendar table, and that will show you the missing
> dates.
>
> Sincerely,
> Chris O.
>
>

Data Space Usage

Hi All
I have tables with Text / nText columns. On an average I add 12-15K rows/day in each of these tables.
Is there any easy way to compute the growth of SIZE of these tables, on a daily basis.
I used datalength (Text Col Name), but not sure if that is the right way.
Any ideas.I plot the growth of databases in Excel. To do this I use the backup
information stored in msdb. If it's just a single table you want to monitor
this won't be of any help.
select backup_start_date,backup_size from msdb..backupset where
database_name = 'pubs'
--
HTH
Ryan Waight, MCDBA, MCSE
"Prasanna Prabhu" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:A98763F8-F73C-4EDF-918A-2B5D1D33F2E1@.microsoft.com...
> Hi All
> I have tables with Text / nText columns. On an average I add 12-15K
rows/day in each of these tables.
> Is there any easy way to compute the growth of SIZE of these tables, on a
daily basis.
> I used datalength (Text Col Name), but not sure if that is the right way.
> Any ideas.
>|||How about using sysindexes to get table/index size each night? Just be aware that the info might be
out-of-date (DBCC UPDATEUSAGE).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Prasanna Prabhu" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:A98763F8-F73C-4EDF-918A-2B5D1D33F2E1@.microsoft.com...
> Hi All
> I have tables with Text / nText columns. On an average I add 12-15K rows/day in each of these
tables.
> Is there any easy way to compute the growth of SIZE of these tables, on a daily basis.
> I used datalength (Text Col Name), but not sure if that is the right way.
> Any ideas.
>|||Hi All,
Thanks for your help. Because there is something wrong on Prasanna side. He
asked me post his response here. Here is the reply from the Prasanna:
--
Hi Tibor
I could only get the "COUNT of rows" information from sysindexes.
How will get the information about the SPACE usage of these newly added
rows.
I want to know the rate at which the DB is growing evey 3-4 hours.
----
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Prasanna,
Thanks for your response. I understand that you want to monitor the growth
size of the tables in your database on a daily basis. If I have
misunderstood, please feel free to let me know.
As I understand, there is no easy way to meet your requirements with SQL
Server. However, we can produce a stored procedure selecting data from the
sysindexes table and storing the result records in the specified tables.
Then we perform the stored procedure every day via scheduled job. In that
case, we can monitor the growth size of the tables via reviewing the
specified tables storing the useful records.
Sysindexes contains one row for each index and table in the database and
stored in each database.
For additional information regarding the sysindexes tables, please refer to
the following articles on SQL Server Books Online.
Topic: "sysindexes"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Sorry for the late reply:
> Hi Tibor
> I could only get the "COUNT of rows" information from sysindexes.
> How will get the information about the SPACE usage of these newly added
> rows.
Check out the pages, dpages and reserved columns. Sysindexes is documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:ibfolwcmDHA.2464@.cpmsftngxa06.phx.gbl...
> Hi All,
> Thanks for your help. Because there is something wrong on Prasanna side. He
> asked me post his response here. Here is the reply from the Prasanna:
> --
> Hi Tibor
> I could only get the "COUNT of rows" information from sysindexes.
> How will get the information about the SPACE usage of these newly added
> rows.
> I want to know the rate at which the DB is growing evey 3-4 hours.
> ----
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>
>

Data Source Views - Calculated Columns

Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.

I took the following steps:

- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.

ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.

Anyone have any ideas?

Thanks - MikeyNero

You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...

Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.

|||

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

|||

MikeyNero wrote:

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

Mikey,

I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.

I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.

|||

Hello,

I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)

I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation

Cheer

Eric

|||

Rafael Salas wrote:

MikeyNero wrote:

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

Mikey,

I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.

I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.

I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"

In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.

Just my two-penneth worth.

-Jamie

|||

SQLDataMonkey wrote:

Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation

I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.

My advice? If you're using SSIS, don't use DSVs!

-Jamie

Data Source Views - Calculated Columns

Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.

I took the following steps:

- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.

ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.

Anyone have any ideas?

Thanks - MikeyNero

You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...

Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.

|||

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

|||

MikeyNero wrote:

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

Mikey,

I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.

I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.

|||

Hello,

I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)

I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation

Cheer

Eric

|||

Rafael Salas wrote:

MikeyNero wrote:

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

Mikey,

I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.

I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.

I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"

In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.

Just my two-penneth worth.

-Jamie

|||

SQLDataMonkey wrote:

Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation

I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.

My advice? If you're using SSIS, don't use DSVs!

-Jamie

sql

Data Source Column Changes

I have some Dynamic SQL that builds a table with the top 20 item numbers as columns, Stores as rows and an X marking the intersection of Store/Item. This produces a table which is a matrix showing which stores are low on which items.
This table is generated once a week and each week will have different column names because the item numbers which the stores are low on changes from week to week.
How can I write a report that will handle the column names of the data table changing without having to do maintenance on the report every week?

Thanks,
Mark Redman.

One way of doing this is to move the dynamic SQL into a stored procedure and return a dataset which always has fixed column names. The actual item information (item number) would be e.g. the first row in that generated dataset.

-- Robert

Data Report Problem

I have a transaction table which contains 2 columns, Amount and Cat_id (category)

eg my table is like

Amount | Cat_id
500 | 1
300 | 1
800 | 2
400 | 2

So now ive 2 queries
select amount from transaction where category = 1;

select amount from transaction where category = 2;

I want to display the result of these 2 queries in my report.

If i try to add these queries in two seperate commands in the data environment and then add it to the report then it shows an error.

So I created 2 views cat1 and cat 2 with those queries, created a new query which takes the value from those two views and placed it on the report.

But now wen i see the report i get this

Cat_1 | Cat_2
500 | 800
300 | 800
500 | 400
300 | 400

While i want my report like this.

Cat_1 | Cat_2
500 | 800
300 | 400

Please help me guys, have to build a small program.i am not sure i am reading this correctly, but i am going to take a stab at helping just in case...
have you tried grouping your results by category?

hope this helps...|||What about rewriting your query to:

select amount from transaction where category = 1 OR category = 2
Order By category

That will get your results in the form of:

Amount | Cat_id
500 | 1
300 | 1
800 | 2
400 | 2

... unless category 1 and category 2 come from 2 different tables. If they do, you could use a JOIN.

Data Reader Source as ODBC / complex sql

Hello All,

How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does

Create newtable as

(select day, sessionId)

from source

// lots of other joins and unions

select day, sessionId from newtable

drop newtable

I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?

Are there any good examples of this, taking data from a ODBC source into SQL server?

Thanks in advance.

Because your first statement does not return a resultset, I'd guess that SSIS is not able to interpret the metadata to create the output columns for you. Try adding a dummy select as the first statement.|||

Any special reason for no breaking the logic accross diffrent components/tasks?

I would use an execute sql task in control flow for the create and the drop table; that way the data reader would have only the query.

|||I don't understand. Why even do the create table and drop table statements? Why not just use the query you used to populate the table?|||I've never worked on a Netezza data source, but I have seen similar patterns from people who have. Maybe it's a more optimized approach for the Netezza engine. However, I don't really know - maybe the OP can clarify. I'd like to know just for my own personal education.|||

I am jumping in on the project 1/2 through. All of the querying the ODBC source (netezza) was done before my time. My job is to just convert our current process to SSIS. I am having a lot of problems doing this

- I created in a execute sql task and create a temp table

- The next step is a data flow task

- The source is a DataReader Source (this is were the problems start)

In trying to get this to work I did the following to the Netezza connection string:

- RetainSameConnection = True

- DelayValidation = True

Then I would get this error

Error: 0xC0047062 at Data Flow Task, DataReader Source [1]: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.

at System.Data.Odbc.OdbcConnection.SetStateExecuting(String method, OdbcTransaction transaction)

at System.Data.Odbc.OdbcCommand.ValidateConnectionAndTransaction(String method)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (1) failed the pre-execute phase and returned error code 0x80131509.

I would like to know how to get this to work, however after a day to think about the design I am going to create view which will replace the create temp table in step 1 (this will be done offline and not in the SSIS package). Now my query can use that instead creating the temp table.

Thanks for everyone's help, I really wish there were more examples of querying ODBC source and entering the data into SQL Server. I thought that would be one of the most common uses of SSIS.

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank you
Bringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you
|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my loca
l
machine it takes 14 minutes (slams my machine) to get back the whole dataset
.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such ma
ny rows.
Start by retrieving only the data you need, then tune the query by adding in
dexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
--
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:
> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

data presentation at each level

I have a table with Columns as metric id, product name, product id, sales, amount.

The values of the column metric id will be 101, 102, 103 etc..

I need to create a report, in which I need to show up data as below:

1 series -

metric 101:

product name, product id, sales, amount

metric 102:

product name, product id, sales, amount

metric 103:

product name, product id, sales, amount

2 series--

metric 201:

product name, product id, sales, amount

metric 202:

product name, product id, sales, amount

metric 303:

product name, product id, sales, amount

Please let me know, how I can show the rows of that table grouped for each metric id.

Go to your layout, click on the Details row of your table and right-click then select "Edit Group". You should see an area labeled "Group On", if you click the first row there it will bring down a list of fields, pick the field that you want to group by.

Data Overflow

Hi,

I would like to know if we can define an unsigned integer data type in SQL Server 2005. We have a situation where one of the integer columns will reach its 2 billion limit. I want to know if there's any way in which I can extend this to say 4 billion by making the data type unsigned or any other way which doesn't require me to change the data type to bigint.

Any help is highly appreciated.

TIA

Ritesh

Unfortunately, there isn't. If you are able to figure out a way to use the negative numbers then you may be OK, otherwise, it might be time for a bigint.

Data not Exported to CSV/XML

I have a table where one of the columns is hidden based on the following criteria:
=IIF(Fields!ChildIsEmpty5.Value, true, false)

If the Fields!ChildIsEmpty5.Value is false (so the column is showing), and I export the report to CSV or XML, the data in that column is not exported.

Is there another property I can select to get this data to be exported? Or is this functionality not supported?

Thanks for any help you can provide!
Jessica

Jessica,

This functionality is by design. Fields where visibility (hidden) is expression-based are not output to CSV/XML by default.

If you always want this column present in the CSV/XML output, you can set DataElementOutput property of the column to 'Yes'(or in Data Output tab, set data output to 'Yes')

Thanks!

|||But what to do if i need to show or hide object, depending on some condition(expression) in export also?|||

Still not working.

I selected the report item (textbox), right click, select properties, data output tab and I set the output from auto to yes. This item still does not show up in the xml output file.

thanks,

Helmut.

Data not Exported to CSV/XML

I have a table where one of the columns is hidden based on the following criteria:
=IIF(Fields!ChildIsEmpty5.Value, true, false)

If the Fields!ChildIsEmpty5.Value is false (so the column is showing), and I export the report to CSV or XML, the data in that column is not exported.

Is there another property I can select to get this data to be exported? Or is this functionality not supported?

Thanks for any help you can provide!
Jessica

Jessica,

This functionality is by design. Fields where visibility (hidden) is expression-based are not output to CSV/XML by default.

If you always want this column present in the CSV/XML output, you can set DataElementOutput property of the column to 'Yes'(or in Data Output tab, set data output to 'Yes')

Thanks!

|||But what to do if i need to show or hide object, depending on some condition(expression) in export also?|||

Still not working.

I selected the report item (textbox), right click, select properties, data output tab and I set the output from auto to yes. This item still does not show up in the xml output file.

thanks,

Helmut.

Data modeling question

I'm facing the next problem:

I have a table with two columns (among others) modeling category and
subcategory data for each row. I need to summarize info on this two
columns, but with the next specs:

1.- Some grouping is only on the category column.
2.- Some other grouping consider the two columns.

The values for the two columns come from external source, i.e. I have
no means to know the precise universe of data (I suppose soon or later
we'll have a sufficient sample of data, but for now it's not the
case). So, I would like to have a grouping table so it's not necessary
to insert a row for every pair of category and subcategory (although
it would be the best approach for the sake of design's simplicity). As
I don't know every possible combination, I would prefer something like
'this category is a - no matter the subcategory', and 'this other
category + subcategory is b'. Let's go with a sample:

------------------

Create Table B ( -- groupings --
categ char(8),
subcateg char(5),
what_group char(10)
)

-- All rows with 432 code are cat. A --
Insert B ( '00000432', ' ', 'Category A' )

-- All rows with 636 code are cat. C except when subcat is 8552 (cat.
B) --
Insert B ( '00000636', '08552', 'Category B' )
Insert B ( '00000636', ' ', 'Category C' )

-- Some data --

Create Table A ( -- data --
categ char(8),
subcateg char(5)
)

Insert A ( '00000432', '01322' )
Insert A ( '00000432', '01222' )
Insert A ( '00000432', '01100' )
Insert A ( '00000432', ' ' )

Insert A ( '00000636', '08552' )
Insert A ( '00000636', '08552' )
Insert A ( '00000636', '01100' )
Insert A ( '00000636', ' ' )
Insert A ( '00000636', '01111' )

-- The query like:

Select b.what_group, count(*) as cnt
From a
Left Join b
On /* ? ? ? ? */

-- Should give --

what_group cnt
----- ----
Category A 4
Category B 2
Category C 3

--------------------

It would be easier knowing all the pairs categ - subcateg. If I don't
know them, is a good idea to model the grouping table as I've done
with rows in B?

TIA,

Diego
Bcn, Spain[posted and mailed, please reply in news]

Diego Buendia (dbuendiab@.yahoo.es) writes:
> I'm facing the next problem:
> I have a table with two columns (among others) modeling category and
> subcategory data for each row. I need to summarize info on this two
> columns, but with the next specs:
> 1.- Some grouping is only on the category column.
> 2.- Some other grouping consider the two columns.
> The values for the two columns come from external source, i.e. I have
> no means to know the precise universe of data (I suppose soon or later
> we'll have a sufficient sample of data, but for now it's not the
> case). So, I would like to have a grouping table so it's not necessary
> to insert a row for every pair of category and subcategory (although
> it would be the best approach for the sake of design's simplicity). As
> I don't know every possible combination, I would prefer something like
> 'this category is a - no matter the subcategory', and 'this other
> category + subcategory is b'. Let's go with a sample:

I have done one change to your set up: rather than using space to
mean "no subcategory", I'm using NULL. Here is a repro which appears
to give the correct result:

Create Table B ( -- groupings --
categ char(8) NOT NULL,
subcateg char(5) NULL,
what_group char(10) NOT NULL
)

-- All rows with 432 code are cat. A --
Insert B VALUES( '00000432', NULL, 'Category A' )

-- All rows with 636 code are cat. C except when subcat is 8552 (cat. B)
--
Insert B VALUES ( '00000636', '08552', 'Category B' )
Insert B VALUES ( '00000636', NULL, 'Category C' )

-- Some data --

Create Table A ( -- data --
categ char(8) NOT NULL,
subcateg char(5) NULL
)

Insert A VALUES( '00000432', '01322' )
Insert A VALUES( '00000432', '01222' )
Insert A VALUES( '00000432', '01100' )
Insert A VALUES( '00000432', NULL )

Insert A VALUES( '00000636', '08552' )
Insert A VALUES( '00000636', '08552' )
Insert A VALUES( '00000636', '01100' )
Insert A VALUES( '00000636', NULL )
Insert A VALUES( '00000636', '01111' )
go
SELECT what_group , COUNT(*)
FROM (
SELECT B.what_group
FROM A
JOIN B ON A.categ = B.categ
AND A.subcateg = B.subcateg
UNION ALL
SELECT B.what_group
FROM A
JOIN B ON A.categ = B.categ
AND B.subcateg IS NULL
WHERE NOT EXISTS (SELECT *
FROM B b1
WHERE A.categ = b1.categ
AND A.subcateg = b1.subcateg)
) AS x
GROUP BY what_group
go
DROP TABLE A, B

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Data Mining Problem: Is that possible to predict Many Many Columns?

Hello,

Can someone please assist?
I have no problem using the provided Algorithms (NaiveBayes, Decision Tree, etc) from SQL Server 2005 Data Mining. For example: If I want to predict whether the customers want to buy bike from the following data, then I use Age, Salary, Gender as input/attribute/feature selection and BuyBike column as "Predict" column.

Table
Age Salary Gender BuyBike

However, say that I have 10,000 types of bikes to predict. How to do that?
Age Salary Gender BuyBike1 BuyBike2 BuyBike3 ...... BuyBike10000

Are there any online resources discussing this issue? I am desperately try to solve this problem. Please assist!

Mary

You can create a nested table. Based on data above, the model would look like:

(

[CustKey] KEY,

[Age] DOUBLE CONTINUOUS,

[Gender] TEXT DISCRETE,

[BikeModels] TABLE PREDICT

(

[Model] TEXT KEY

)

)

Then query the model (NB, DT etc) with a prediction statement like below:

SELECT Predict( BikeModels[, 5]) FROM Model

If you use the optional ",5" it will return the top 5 most likely predictions

More details:

http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1090.aspx (details on the nested table concept)

http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1061.aspx (impact of nested table on the model attributes)

http://msdn2.microsoft.com/en-us/library/ms132190.aspx (documentation for the DMX Predict function)

|||

Hello,

The provided solution (see the previous message) gave me the following error:

Query(2, 25) Parse: The syntax for '[,5]' is incorrect.

Please assist!

Mary

|||

Perhaps this is the right solution:

SELECT Predict( [BikeModels], 5) FROM Model

instead of

SELECT Predict( BikeModels[, 5]) FROM Model

Mary

|||

By [, 5] I meant that ", 5" is optional.

You can use either

SELECT Predict(BikeModels) FROM Model -- for all predictions

or

SELECT Predict(BikeModels, 5) FROM Model --for top 5 predictions

Sorry, I should have made it clear

Data Layout in Matrix

Repost from an earlier question. I am trying to use a matrix to display records in three columns per row, like this:
Record1 Record2 Record3
Model Model Model
Descript Descript Descript
Price Price Price
Unable to get the dynamic columns expanding to the right, or set the # of columns.
Below is the conversation so far...
Any help would be greatly appreciated!
---
Thank you for the response Chris. I've been out and unable to test this until today. I set it up as you suggested - I have a matrix with two three static rows and a column group on =RowNumber(Nothing).
I get the following error message:
"A group expression for the grouping â'matrix1_ColumnGroup1â' uses the RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group."
Using the List group name as the RowNumber parameter throws more errors.
What am I missing?
Michael
"Chris Hays [MSFT]" wrote:
> You'll need to use a matrix to get a horizontially growing layout.
> It should have one dynamic column grouping (group on =RowNumber(Nothing))
> and three static rows (right-click in the data area and select "Add Row"
> twice).
> To limit this to three columns per matrix, put the matrix in a list and
> group on something like this: =Ceiling(RowNumber(Nothing)/3)
>
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "OTB6" <OTB6@.discussions.microsoft.com> wrote in message
> news:7EC2C1E0-98C2-4069-B951-D40123C37ABF@.microsoft.com...
> > Trying to find a way to display returned records in (n) rows of 3 columns
> or less like this:
> >
> > Record 1 Record 2 Record 3
> > col 1 col 1 col 1
> > col 2 col 2 col 2
> > col 3 col 3 col 3
> >
> > Record 4 Record 5
> > col 1 col 1
> > col 2 col 2
> > col 3 col 3
> >
> > Can someone point me in the right direction as far as the tools
> (table/matrix/list)?
> >
> > Thanks!
> >
> > Michael<-- okay really feeling like an idiot now, but...
where/how is this attachment retrieved? if sent to the email listed in my profile, I'm not receiving it. I'd REALLY like to see your solution.
m
"Chris Hays [MSFT]" wrote:
> Using the list group name should have worked.
> I'm attaching a working example so you can compare to what you've done to
> find where they differ.
>|||You really should use a real newsreader (like Outlook Express) so you can
receive and post attachments.
(Most web-based newsreaders fail to implement support for attachments.)
I'll be posting this example to my weblog later today so you'll be able to
get it there if you can't use a real newsreader for some reason:
http://blogs.msdn.com/chrishays
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"OTB6" <OTB6@.discussions.microsoft.com> wrote in message
news:8994534E-8F27-4102-832A-071AEC7D1978@.microsoft.com...
> <-- okay really feeling like an idiot now, but...
> where/how is this attachment retrieved? if sent to the email listed in my
profile, I'm not receiving it. I'd REALLY like to see your solution.
> m
> "Chris Hays [MSFT]" wrote:
> > Using the list group name should have worked.
> > I'm attaching a working example so you can compare to what you've done
to
> > find where they differ.
> >
>

Data integrity error

Hi All,
This is a wierd one... I have a table for which I wish to remove the
IDENTITY attribute for one of the columns. To do this I intended to
create a new column, copy the data from the identity column to the new
column, delete the identity column and then rename the new column...
All pretty straight forward stuff.
However, when trying to do step 2 (up, I get a recursive "Data
integrity error" message appearing in my results window (there are only
15 rows in the table and I get approximately 20K "DIE" messages a
minute)
The script I am using is as follows;
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
Column names have been changed to protect the innocent ;)
>From where I am sitting this all looks pretty generic, and I can't see
why my script is kicking up such a fuss...
Merry christmas to all,
Best, Marki would do it otherwise:
why dont you try to create a new table with all needed indexes, identity, etc'
and then use the select into old table to new table. if your table consist
only 15 rows - may be you should consider rebuid the index after you do the
select into. it worth a try...
tomer
"Cuperman" wrote:
> Hi All,
> This is a wierd one... I have a table for which I wish to remove the
> IDENTITY attribute for one of the columns. To do this I intended to
> create a new column, copy the data from the identity column to the new
> column, delete the identity column and then rename the new column...
> All pretty straight forward stuff.
> However, when trying to do step 2 (up, I get a recursive "Data
> integrity error" message appearing in my results window (there are only
> 15 rows in the table and I get approximately 20K "DIE" messages a
> minute)
> The script I am using is as follows;
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = [id]
> ALTER TABLE tToBeAltered DROP COLUMN [id]
> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
> Column names have been changed to protect the innocent ;)
> >From where I am sitting this all looks pretty generic, and I can't see
> why my script is kicking up such a fuss...
> Merry christmas to all,
> Best, Mark
>|||... and also do DBCC CHECKDB on the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tomer" <tomer@.discussions.microsoft.com> wrote in message
news:9CDA2C47-ADF2-4AF9-AAA0-2730F5D61DAB@.microsoft.com...
>i would do it otherwise:
> why dont you try to create a new table with all needed indexes, identity, etc'
> and then use the select into old table to new table. if your table consist
> only 15 rows - may be you should consider rebuid the index after you do the
> select into. it worth a try...
> tomer
> "Cuperman" wrote:
>> Hi All,
>> This is a wierd one... I have a table for which I wish to remove the
>> IDENTITY attribute for one of the columns. To do this I intended to
>> create a new column, copy the data from the identity column to the new
>> column, delete the identity column and then rename the new column...
>> All pretty straight forward stuff.
>> However, when trying to do step 2 (up, I get a recursive "Data
>> integrity error" message appearing in my results window (there are only
>> 15 rows in the table and I get approximately 20K "DIE" messages a
>> minute)
>> The script I am using is as follows;
>> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
>> UPDATE tToBeAltered SET [new_id] = [id]
>> ALTER TABLE tToBeAltered DROP COLUMN [id]
>> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
>> Column names have been changed to protect the innocent ;)
>> >From where I am sitting this all looks pretty generic, and I can't see
>> why my script is kicking up such a fuss...
>> Merry christmas to all,
>> Best, Mark
>>|||Hi Tomer,
Thanks for taking the time to respond.
I actually have more than one table that will be modified, so was
scripting a solution that would do all tables. Most are working OK
(where Identity is Numeric or Int), but this one table is causing a
real problem.
At the moment I am working on a test DB, hence 15 rows, but when we get
to production environment the table will contain millions of rows.
Thanks for suggesting the option, but I think a full table recreate
would be too dangerous (missing constraints here or there) and more
importantly too slow for my requirements here.
I guess I was hoping that there is a known configuration issue / bug
that I could simply tweak to fix this. Something seems fundamentally
wrong here.
Best, Mark|||** Update: There is even a problem with:
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = 1
This throws the same D.I.E. error... is this a corrupt DB?
Thanks, Mark|||Thanks for taking the time to respond Tibor.
results from DBCC CHECKDB
...
DBCC results for 'tToBeAltered'.
There are 13 rows in 2 pages for object 'tToBeAltered'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'TestDB'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
...
This seems OK to me. Doesn't suggest anything to worry about...
Best, Mark|||Hi Mark,
It sounds pretty weird. but may be you should:
1. to run dbcc indexdefrag. it will not cause any locks - you can run it
online.
2. if you work on a dev enviorment may be you should take the last backup
of this db and restore it and then try to do what you are doing with the id
column.
thx,
Tomer
"Cuperman" wrote:
> ** Update: There is even a problem with:
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = 1
> This throws the same D.I.E. error... is this a corrupt DB?
> Thanks, Mark
>|||I hate it when a thread just runs out of steam and no solution is ever
given ...luckily I have found the cause of my problem... there was an
update trigger that was causing problems...
To fix this all I needed was:
/*************************************************************/
ALTER TABLE tToBeAltered DISABLE TRIGGER ALL
GO
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
GO
ALTER TABLE tToBeAltered ENABLE TRIGGER ALL
GO
/*************************************************************/
Thanks to all who offered assistance.
Best Regards,
Mark

Data integrity error

Hi All,
This is a wierd one... I have a table for which I wish to remove the
IDENTITY attribute for one of the columns. To do this I intended to
create a new column, copy the data from the identity column to the new
column, delete the identity column and then rename the new column...
All pretty straight forward stuff.
However, when trying to do step 2 (up, I get a recursive "Data
integrity error" message appearing in my results window (there are only
15 rows in the table and I get approximately 20K "DIE" messages a
minute)
The script I am using is as follows;
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
Column names have been changed to protect the innocent ;)

>From where I am sitting this all looks pretty generic, and I can't see
why my script is kicking up such a fuss...
Merry christmas to all,
Best, Mark
i would do it otherwise:
why dont you try to create a new table with all needed indexes, identity, etc'
and then use the select into old table to new table. if your table consist
only 15 rows - may be you should consider rebuid the index after you do the
select into. it worth a try...
tomer
"Cuperman" wrote:

> Hi All,
> This is a wierd one... I have a table for which I wish to remove the
> IDENTITY attribute for one of the columns. To do this I intended to
> create a new column, copy the data from the identity column to the new
> column, delete the identity column and then rename the new column...
> All pretty straight forward stuff.
> However, when trying to do step 2 (up, I get a recursive "Data
> integrity error" message appearing in my results window (there are only
> 15 rows in the table and I get approximately 20K "DIE" messages a
> minute)
> The script I am using is as follows;
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = [id]
> ALTER TABLE tToBeAltered DROP COLUMN [id]
> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
> Column names have been changed to protect the innocent ;)
> why my script is kicking up such a fuss...
> Merry christmas to all,
> Best, Mark
>
|||... and also do DBCC CHECKDB on the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tomer" <tomer@.discussions.microsoft.com> wrote in message
news:9CDA2C47-ADF2-4AF9-AAA0-2730F5D61DAB@.microsoft.com...[vbcol=seagreen]
>i would do it otherwise:
> why dont you try to create a new table with all needed indexes, identity, etc'
> and then use the select into old table to new table. if your table consist
> only 15 rows - may be you should consider rebuid the index after you do the
> select into. it worth a try...
> tomer
> "Cuperman" wrote:
|||Hi Tomer,
Thanks for taking the time to respond.
I actually have more than one table that will be modified, so was
scripting a solution that would do all tables. Most are working OK
(where Identity is Numeric or Int), but this one table is causing a
real problem.
At the moment I am working on a test DB, hence 15 rows, but when we get
to production environment the table will contain millions of rows.
Thanks for suggesting the option, but I think a full table recreate
would be too dangerous (missing constraints here or there) and more
importantly too slow for my requirements here.
I guess I was hoping that there is a known configuration issue / bug
that I could simply tweak to fix this. Something seems fundamentally
wrong here.
Best, Mark
|||** Update: There is even a problem with:
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = 1
This throws the same D.I.E. error... is this a corrupt DB?
Thanks, Mark
|||Thanks for taking the time to respond Tibor.
results from DBCC CHECKDB
...
DBCC results for 'tToBeAltered'.
There are 13 rows in 2 pages for object 'tToBeAltered'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'TestDB'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
...
This seems OK to me. Doesn't suggest anything to worry about...
Best, Mark
|||Hi Mark,
It sounds pretty weird. but may be you should:
1. to run dbcc indexdefrag. it will not cause any locks - you can run it
online.
2. if you work on a dev enviorment may be you should take the last backup
of this db and restore it and then try to do what you are doing with the id
column.
thx,
Tomer
"Cuperman" wrote:

> ** Update: There is even a problem with:
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = 1
> This throws the same D.I.E. error... is this a corrupt DB?
> Thanks, Mark
>
|||I hate it when a thread just runs out of steam and no solution is ever
given ...luckily I have found the cause of my problem... there was an
update trigger that was causing problems...
To fix this all I needed was:
/************************************************** ***********/
ALTER TABLE tToBeAltered DISABLE TRIGGER ALL
GO
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
GO
ALTER TABLE tToBeAltered ENABLE TRIGGER ALL
GO
/************************************************** ***********/
Thanks to all who offered assistance.
Best Regards,
Mark

Data integrity error

Hi All,
This is a wierd one... I have a table for which I wish to remove the
IDENTITY attribute for one of the columns. To do this I intended to
create a new column, copy the data from the identity column to the new
column, delete the identity column and then rename the new column...
All pretty straight forward stuff.
However, when trying to do step 2 (up, I get a recursive "Data
integrity error" message appearing in my results window (there are only
15 rows in the table and I get approximately 20K "DIE" messages a
minute)
The script I am using is as follows;
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
Column names have been changed to protect the innocent ;)

>From where I am sitting this all looks pretty generic, and I can't see
why my script is kicking up such a fuss...
Merry christmas to all,
Best, Marki would do it otherwise:
why dont you try to create a new table with all needed indexes, identity, et
c'
and then use the select into old table to new table. if your table consist
only 15 rows - may be you should consider rebuid the index after you do the
select into. it worth a try...
tomer
"Cuperman" wrote:

> Hi All,
> This is a wierd one... I have a table for which I wish to remove the
> IDENTITY attribute for one of the columns. To do this I intended to
> create a new column, copy the data from the identity column to the new
> column, delete the identity column and then rename the new column...
> All pretty straight forward stuff.
> However, when trying to do step 2 (up, I get a recursive "Data
> integrity error" message appearing in my results window (there are only
> 15 rows in the table and I get approximately 20K "DIE" messages a
> minute)
> The script I am using is as follows;
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = [id]
> ALTER TABLE tToBeAltered DROP COLUMN [id]
> EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
> Column names have been changed to protect the innocent ;)
>
> why my script is kicking up such a fuss...
> Merry christmas to all,
> Best, Mark
>|||... and also do DBCC CHECKDB on the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tomer" <tomer@.discussions.microsoft.com> wrote in message
news:9CDA2C47-ADF2-4AF9-AAA0-2730F5D61DAB@.microsoft.com...[vbcol=seagreen]
>i would do it otherwise:
> why dont you try to create a new table with all needed indexes, identity,
etc'
> and then use the select into old table to new table. if your table consist
> only 15 rows - may be you should consider rebuid the index after you do th
e
> select into. it worth a try...
> tomer
> "Cuperman" wrote:
>|||Hi Tomer,
Thanks for taking the time to respond.
I actually have more than one table that will be modified, so was
scripting a solution that would do all tables. Most are working OK
(where Identity is Numeric or Int), but this one table is causing a
real problem.
At the moment I am working on a test DB, hence 15 rows, but when we get
to production environment the table will contain millions of rows.
Thanks for suggesting the option, but I think a full table recreate
would be too dangerous (missing constraints here or there) and more
importantly too slow for my requirements here.
I guess I was hoping that there is a known configuration issue / bug
that I could simply tweak to fix this. Something seems fundamentally
wrong here.
Best, Mark|||** Update: There is even a problem with:
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = 1
This throws the same D.I.E. error... is this a corrupt DB?
Thanks, Mark|||Thanks for taking the time to respond Tibor.
results from DBCC CHECKDB
...
DBCC results for 'tToBeAltered'.
There are 13 rows in 2 pages for object 'tToBeAltered'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'TestDB'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
...
This seems OK to me. Doesn't suggest anything to worry about...
Best, Mark|||Hi Mark,
It sounds pretty weird. but may be you should:
1. to run dbcc indexdefrag. it will not cause any locks - you can run it
online.
2. if you work on a dev enviorment may be you should take the last backup
of this db and restore it and then try to do what you are doing with the id
column.
thx,
Tomer
"Cuperman" wrote:

> ** Update: There is even a problem with:
> ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
> UPDATE tToBeAltered SET [new_id] = 1
> This throws the same D.I.E. error... is this a corrupt DB?
> Thanks, Mark
>|||I hate it when a thread just runs out of steam and no solution is ever
given ...luckily I have found the cause of my problem... there was an
update trigger that was causing problems...
To fix this all I needed was:
/ ****************************************
*********************/
ALTER TABLE tToBeAltered DISABLE TRIGGER ALL
GO
ALTER TABLE tToBeAltered ADD [new_id] NUMERIC(18,0) NULL
UPDATE tToBeAltered SET [new_id] = [id]
ALTER TABLE tToBeAltered DROP COLUMN [id]
EXEC SP_RENAME 'tToBeAltered.new_id', 'id', 'COLUMN'
GO
ALTER TABLE tToBeAltered ENABLE TRIGGER ALL
GO
/ ****************************************
*********************/
Thanks to all who offered assistance.
Best Regards,
Mark