Showing posts with label column. Show all posts
Showing posts with label column. Show all posts

Monday, March 19, 2012

ODBC Column Type Error with Bigint

Hello All,
I found an issue and wanted osme advice or guidance
I have found an issue in the ODBC API when getting the column type of a
BIGINT. When the column type is defined as BIGINT in the table then all
works fine. The column type is 127 (x7F) and size of 8 bytes
But if the I use convert to convert the column type eg ( convert(bigint,
colname ) ) or if I sum the column in a group by query the column type
returned is no 127 but 38 (x26) which is SQLINTN but the column size is 8
bytes.
The problem then arises when srv_setcoldata ,srv_setcollen and srv_sendrow
all fail.
If anyone has the xp_gettable_odbc source which comes with SQL Server and
tests the issue like this
master..xp_gettable_odbc '(select convert(bigint,0) as tt ) as dd'
no results will be returned but this
master..xp_gettable_odbc '(select convert(int,0) as tt ) as dd' will return
one row as it should
Has anyone else come across this issue if so any fixes?
Kind regards
Greg OHello Greg,
Based on my research, this seems to be a limitation of Open Data Service
(ODS). It only support 4 bytes int and there is no support of bigint.
You could refer to "Data types" topic in BOL for details.
http://msdn.microsoft.com/library/d...-us/odssql/ods_
6_ref_10_4n5k.asp
Hope this is helpful.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "GregO" <grego@.community.nospam>
| Subject: ODBC Column Type Error with Bigint
| Date: Mon, 7 Nov 2005 18:52:42 +1100
| Lines: 29
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <uCLoZ#24FHA.1536@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: dsl-202-173-157-78.vic.westnet.com.au 202.173.157.78
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:129572
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hello All,
| I found an issue and wanted osme advice or guidance
|
| I have found an issue in the ODBC API when getting the column type of a
| BIGINT. When the column type is defined as BIGINT in the table then all
| works fine. The column type is 127 (x7F) and size of 8 bytes
|
| But if the I use convert to convert the column type eg ( convert(bigint,
| colname ) ) or if I sum the column in a group by query the column type
| returned is no 127 but 38 (x26) which is SQLINTN but the column size is 8
| bytes.
|
| The problem then arises when srv_setcoldata ,srv_setcollen and
srv_sendrow
| all fail.
|
| If anyone has the xp_gettable_odbc source which comes with SQL Server and
| tests the issue like this
| master..xp_gettable_odbc '(select convert(bigint,0) as tt ) as dd'
|
| no results will be returned but this
| master..xp_gettable_odbc '(select convert(int,0) as tt ) as dd' will
return
| one row as it should
|
| Has anyone else come across this issue if so any fixes?
|
| Kind regards
| Greg O
|
|
|

Monday, March 12, 2012

ODBC -- Call Failed with error # 547

[Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
statement conflicted with COLUMN FOREIGN KEY
constraint 'Attachment_FK00'. The conflict occured in
database 'assay', table 'suborders', column 'suborderid'.
(#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
statement has been terminated (#3621)
when user try to upload the document thought Access 2002
to SQL database, occasionally user could get this error.
did someone experience this error before'
thanks,
wilsonHi
You need to look at the data or insert order/method as your FK constraint is
not being satisfied.
John
"Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:1a51b01c44e4d$4543dc20$a501280a@.phx
.gbl...
> [Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
> statement conflicted with COLUMN FOREIGN KEY
> constraint 'Attachment_FK00'. The conflict occured in
> database 'assay', table 'suborders', column 'suborderid'.
> (#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
> statement has been terminated (#3621)
> when user try to upload the document thought Access 2002
> to SQL database, occasionally user could get this error.
> did someone experience this error before'
> thanks,
> wilson|||Hi john,
thanks for your quick response.
why it is working sometimes, but sometimes it is not.
wilson
>--Original Message--
>Hi
>You need to look at the data or insert order/method as
your FK constraint is
>not being satisfied.
>John
>"Wilson" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1a51b01c44e4d$4543dc20$a501280a@.phx
.gbl...
column 'suborderid'.[vbcol=seagreen]
The[vbcol=seagreen]
2002[vbcol=seagreen]
error.[vbcol=seagreen]
>
>.
>|||Hi
That is a feature of the data you are inserting. Try loading this into a
copy of the table (i.e. without the FK) and then check the data with a
statement similar to:
SELECT * FROM TmpTable
WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1a63301c44e5b$c829ce70$a401280a@.phx
.gbl...[vbcol=seagreen]
> Hi john,
> thanks for your quick response.
> why it is working sometimes, but sometimes it is not.
>
> wilson
> your FK constraint is
> message
> column 'suborderid'.
> The
> 2002
> error.|||thanks, John.
But now we got another error:
Syntax error (missing operator) in query
expression '[SubOrderID]='
any ideas'
thanks,
wilson
>--Original Message--
>Hi
>That is a feature of the data you are inserting. Try
loading this into a
>copy of the table (i.e. without the FK) and then check
the data with a
>statement similar to:
>SELECT * FROM TmpTable
>WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
>John
><anonymous@.discussions.microsoft.com> wrote in message
> news:1a63301c44e5b$c829ce70$a401280a@.phx
.gbl...
in[vbcol=seagreen]
INSERT[vbcol=seagreen]
in[vbcol=seagreen]
Server][vbcol=seagreen]
>
>.
>|||Hi
You may want to start ODBC logging and see what the statements are. At a
guess you either are creating the statements dynamically and that is not
working correctly e.g. as missing 'AND' or alternatively that your data has
a character that needs escaping in it e.g as a quote.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1aa3301c44f0b$bc9fcb80$a301280a@.phx
.gbl...[vbcol=seagreen]
> thanks, John.
> But now we got another error:
> Syntax error (missing operator) in query
> expression '[SubOrderID]='
> any ideas'
> thanks,
> wilson
> loading this into a
> the data with a
> in
> INSERT
> in
> Server]

ODBC -- Call Failed with error # 547

[Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
statement conflicted with COLUMN FOREIGN KEY
constraint 'Attachment_FK00'. The conflict occured in
database 'assay', table 'suborders', column 'suborderid'.
(#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
statement has been terminated (#3621)
when user try to upload the document thought Access 2002
to SQL database, occasionally user could get this error.
did someone experience this error before?
thanks,
wilson
Hi
You need to look at the data or insert order/method as your FK constraint is
not being satisfied.
John
"Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:1a51b01c44e4d$4543dc20$a501280a@.phx.gbl...
> [Microsoft}{ODBC SQL Server Driver][SQL Server] INSERT
> statement conflicted with COLUMN FOREIGN KEY
> constraint 'Attachment_FK00'. The conflict occured in
> database 'assay', table 'suborders', column 'suborderid'.
> (#547) [Microsoft}{ODBC SQL Server Driver][SQL Server]The
> statement has been terminated (#3621)
> when user try to upload the document thought Access 2002
> to SQL database, occasionally user could get this error.
> did someone experience this error before?
> thanks,
> wilson
|||Hi john,
thanks for your quick response.
why it is working sometimes, but sometimes it is not.
wilson
>--Original Message--
>Hi
>You need to look at the data or insert order/method as
your FK constraint is
>not being satisfied.
>John
>"Wilson" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1a51b01c44e4d$4543dc20$a501280a@.phx.gbl...
column 'suborderid'.[vbcol=seagreen]
The[vbcol=seagreen]
2002[vbcol=seagreen]
error.
>
>.
>
|||Hi
That is a feature of the data you are inserting. Try loading this into a
copy of the table (i.e. without the FK) and then check the data with a
statement similar to:
SELECT * FROM TmpTable
WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1a63301c44e5b$c829ce70$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi john,
> thanks for your quick response.
> why it is working sometimes, but sometimes it is not.
>
> wilson
> your FK constraint is
> message
> column 'suborderid'.
> The
> 2002
> error.
|||thanks, John.
But now we got another error:
Syntax error (missing operator) in query
expression '[SubOrderID]='
any ideas?
thanks,
wilson
>--Original Message--
>Hi
>That is a feature of the data you are inserting. Try
loading this into a
>copy of the table (i.e. without the FK) and then check
the data with a[vbcol=seagreen]
>statement similar to:
>SELECT * FROM TmpTable
>WHERE FKCol NOT IN ( SELECT PKCol from PKTbl)
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:1a63301c44e5b$c829ce70$a401280a@.phx.gbl...
in[vbcol=seagreen]
INSERT[vbcol=seagreen]
in[vbcol=seagreen]
Server]
>
>.
>
|||Hi
You may want to start ODBC logging and see what the statements are. At a
guess you either are creating the statements dynamically and that is not
working correctly e.g. as missing 'AND' or alternatively that your data has
a character that needs escaping in it e.g as a quote.
John
<anonymous@.discussions.microsoft.com> wrote in message
news:1aa3301c44f0b$bc9fcb80$a301280a@.phx.gbl...[vbcol=seagreen]
> thanks, John.
> But now we got another error:
> Syntax error (missing operator) in query
> expression '[SubOrderID]='
> any ideas?
> thanks,
> wilson
> loading this into a
> the data with a
> in
> INSERT
> in
> Server]

Friday, March 9, 2012

ODBC

I need to create a C/C++ function using ODBC interface to get the
string length of a column in a table.

What is the proper function to use ?Hi

You will need to call SQLColAttribute
http://msdn.microsoft.com/library/d...olattribute.asp

John

"GTi" <bill@.gates.com> wrote in message
news:wSnyd.4363$3N5.4089@.amstwist00...
>I need to create a C/C++ function using ODBC interface to get the
> string length of a column in a table.
> What is the proper function to use ?

ODB Datareader time columns - updated 26th June

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John


|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?

ODB Datareader time columns - help needed

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John


|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?

ODB Datareader time columns

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John


|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?

Wednesday, March 7, 2012

Obtaining Running Totals

I am working on a report and need to figure out a way to come up with
running totals based on a column value. Let me explain...
As shown in the query below, I am displaying "totalmealsize", "minmealsize",
and "maxmealsize" for a particular interval number (which can be anywhere
between 1 and 24). What I would like to do is display a running total for
each interval that summarizes all previous intervals. For example, given
the following data:
INTERVAL TOTAL RUNNING-TOTAL
1 5 5
2 3 8
3 1 9
4 7 16
5 4 20
As you can see, the RUNNING-TOTAL is the sum of all previous interval values
(I would want to do something similar with the min/max values as well). I
have tried various things but have not succeeded. I realize I can do this
in (C#) code as well as probably even create a user-defined function to
handle it. However, I would like to keep it in standard SQL if possible.
Any ideas?
<BEGIN SQL QUERY>
SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
id.RunAnimalID,
id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
AS SMS_MAX
FROM intervaldetail id
INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
WHERE id.intervalsetid = 99
ORDER BY t.Name, tl.Level, id.sequencenumber
<END SQL QUERY>
-- Thanks, JeffTry this
Declare @.t table(INTERVAL int, TOTAL integer)
insert into @.T values(1,5)
insert into @.T values(2,3)
insert into @.T values(3,1)
insert into @.T values(4,7)
insert into @.T values(5,4)
select *,(select sum(total) from @.t where interval <=T.Interval) as
'Running Total' from @.t T
Madhivanan|||Here is a repro,
CREATE TABLE #Totals(ID int IDENTITY(1,1), total int)
INSERT INTO #Totals VALUES(100)
INSERT INTO #Totals VALUES(200)
INSERT INTO #Totals VALUES(300)
INSERT INTO #Totals VALUES(400)
SELECT A.ID , A.Total,(SELECT SUM(total)
FROM #Totals B WHERE B.ID <=A.ID) As RunningTotal
FROM #Totals A
DROP TABLE #Totals
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeff B." <jsb@.community.nospam> wrote in message
news:e6k6GiVHFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I am working on a report and need to figure out a way to come up with
>running totals based on a column value. Let me explain...
> As shown in the query below, I am displaying "totalmealsize",
> "minmealsize", and "maxmealsize" for a particular interval number (which
> can be anywhere between 1 and 24). What I would like to do is display a
> running total for each interval that summarizes all previous intervals.
> For example, given the following data:
> INTERVAL TOTAL RUNNING-TOTAL
> 1 5 5
> 2 3 8
> 3 1 9
> 4 7 16
> 5 4 20
> As you can see, the RUNNING-TOTAL is the sum of all previous interval
> values (I would want to do something similar with the min/max values as
> well). I have tried various things but have not succeeded. I realize I
> can do this in (C#) code as well as probably even create a user-defined
> function to handle it. However, I would like to keep it in standard SQL
> if possible.
> Any ideas?
> <BEGIN SQL QUERY>
> SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
> id.RunAnimalID,
> id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
> AS SMS_MAX
> FROM intervaldetail id
> INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
> INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
> WHERE id.intervalsetid = 99
> ORDER BY t.Name, tl.Level, id.sequencenumber
> <END SQL QUERY>
> -- Thanks, Jeff
>|||Thanks everyone for your replies. The subquery did the trick. I had tried
that one before but I must have had the syntax wrong because I wasn't able
to get it to work. Now it is working just fine.
-- Thanks, Jeff
"Jeff B." <jsb@.community.nospam> wrote in message
news:e6k6GiVHFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I am working on a report and need to figure out a way to come up with
>running totals based on a column value. Let me explain...
> As shown in the query below, I am displaying "totalmealsize",
> "minmealsize", and "maxmealsize" for a particular interval number (which
> can be anywhere between 1 and 24). What I would like to do is display a
> running total for each interval that summarizes all previous intervals.
> For example, given the following data:
> INTERVAL TOTAL RUNNING-TOTAL
> 1 5 5
> 2 3 8
> 3 1 9
> 4 7 16
> 5 4 20
> As you can see, the RUNNING-TOTAL is the sum of all previous interval
> values (I would want to do something similar with the min/max values as
> well). I have tried various things but have not succeeded. I realize I
> can do this in (C#) code as well as probably even create a user-defined
> function to handle it. However, I would like to keep it in standard SQL
> if possible.
> Any ideas?
> <BEGIN SQL QUERY>
> SELECT t.Name, tl.Level, id.sequencenumber, id.intervalnumber,
> id.RunAnimalID,
> id.totalmealsize AS SMS, id.minmealsize AS SMS_MIN, id.maxmealsize
> AS SMS_MAX
> FROM intervaldetail id
> INNER JOIN TreatmentLevel tl ON id.RunAnimalID = tl.RunAnimalID
> INNER JOIN Treatment t on t.TreatmentID = tl.TreatmentID
> WHERE id.intervalsetid = 99
> ORDER BY t.Name, tl.Level, id.sequencenumber
> <END SQL QUERY>
> -- Thanks, Jeff

Friday, February 24, 2012

Obtain a list of all rows insert via a select statement

With Table A being the parent table and having an identity column as the
primary key.
With Table B being a child of table A
I need to do an insert based on a select statement (multiple row returned)
in Table A. Then I need to insert multiple child rows in Table B for every
row inserted in Table A.
What is the best strategy? Is there a way to have a list of all the rows
inserted?
I'm open to any suggestion.
Thank you in advance
MartinYou can access those within a trigger, and have it populate a temp table
that the calling batch created, if such a temp table exists, e.g.,
create table t(k int not null identity primary key, d varchar(10));
go
create trigger trg_i_t on t for insert
as
if object_id('tempdb..#t') is not null
insert into #t select k from inserted;
go
-- test
create table #t(k int);
insert into t(d)
select 'a'
union all select 'b'
union all select 'c';
select * from #t;
drop table #t;
-- Output:
k
--
4
5
6
In SQL Server 2005 it will be much easier with the DML with results
enhancements.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:35F4A835-AC48-4896-B535-B550A768CF74@.microsoft.com...
> With Table A being the parent table and having an identity column as the
> primary key.
> With Table B being a child of table A
> I need to do an insert based on a select statement (multiple row returned)
> in Table A. Then I need to insert multiple child rows in Table B for every
> row inserted in Table A.
> What is the best strategy? Is there a way to have a list of all the rows
> inserted?
> I'm open to any suggestion.
> Thank you in advance
> Martin|||On Tue, 22 Mar 2005 15:51:02 -0800, Martin Rajotte wrote:

>With Table A being the parent table and having an identity column as the
>primary key.
>With Table B being a child of table A
>I need to do an insert based on a select statement (multiple row returned)
>in Table A. Then I need to insert multiple child rows in Table B for every
>row inserted in Table A.
>What is the best strategy? Is there a way to have a list of all the rows
>inserted?
>I'm open to any suggestion.
>Thank you in advance
>Martin
Hi Martin,
I'm not entirely sure if I understand you. Do you mean that you have
some data in one or more tables that needs to be inserted in two new
tables, where the second table links to the identity column of the
first?
The proper way to find the identity value of inserted rows after a
multirow insert is to query the table with the natural key as search
argument. That should return you the identity values.
If you need more specific advise, then please code DDL, sample data and
desired results. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, February 20, 2012

Obscure SQL Server error when task fails in my dts

Does anyone have any insight to this error:
"Duplicate column name resolution could not be done because the ordinal specified a column of a different name"

I googled it and got one result with no resolution.
I get this error from SQL Server when a task in my dts package fails. My dts package is attempting to update records whose fields have changed based on a specified criteria.

Any help would be greatly appreciated.

TIA,
Jennifer
I moved this thread to the dts/SSIS forum.|||

Gail Erickson [MS] wrote:

I moved this thread to the dts/SSIS forum.

Gail -- There is no DTS forum here at forums.microsoft.com.|||Jennifer -- For DTS related issues, please see the following group: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=sg|||ok thanks!

objects

How to find the invalid and corrupted objects in
sql server 2000?
I tried sysindexes and sysobjects tables. Checked the
STATUS column, but didn't understand the values assigned
by Microsoft. Which number means IVALID or Corruption?> How to find the invalid and corrupted objects in
> sql server 2000?
See DBCC CHECKDB and DBCC CHECKTABLE in BOL for starters
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

objects

How to find the invalid and corrupted objects in
sql server 2000?
I tried sysindexes and sysobjects tables. Checked the
STATUS column, but didn't understand the values assigned
by Microsoft. Which number means IVALID or Corruption?> How to find the invalid and corrupted objects in
> sql server 2000?
See DBCC CHECKDB and DBCC CHECKTABLE in BOL for starters
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.