IBM Informix Ole Db Provider

Hello,

I'm getting the below errors when using sql server's data import wizard. Please Note: My test connection was successful and the error occurs when I click the "Next" button after closing the properties window. For Security reasons I've filled the User Id and DataSource with a bogus account, database, and server i.e. xxxx. Notice the password is blank?... Is that normal? I'm in a jam and appreciate your help.

Thanks,

Matt

===================================

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=xxx;Data Source=xxx@.xxxx". (SQL Server Import and Export Wizard)

===================================

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Program Location:

at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.LoadTypeData(OleDbConnection myDestConnection)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.CacheDataSourceInfo(IDbConnection myConnection)

Moving to the "SQL Server Integration Services", which deals with data transformation issues.

|||

This means your OLE DB provider does not publish Provider_Types schema. The wizard relies on this schema to map data types between heterogeneous data sources.

You may want to try to build your SSIS package manually using OLE DB Source to access your Informix database.

Thanks.

|||

Hello,

Does your solution apply to the linked server as well? Any chance you know if SQL Server 2005 allows setting up an informix 7.3 db as a Linked Server? I now can connect with ADO.NET 2.0 using the IBM.Data.Informix namespace but have not been able to find any documentation on setting up connection through a linked server. FYI, I now have to correct IBM SDKs installed (2.9).

Thanks,

Matt

|||

I am having the same problem. Is there a way to get SQL server 2005 to talk to the Informix 7.3 engine? I get the following error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Ifxoledbc" for linked server "MINNYME2" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "MINNYME2".

|||

You are in luck. I'm currently still looking into this but have done a significant grind and configured the connection with ODBC. How are you connecting? With the Informix OLEDB? I'm currently waiting for the AIX admin to set something up on the server. He has to run a few scripts to set up the OLEDB properly. I found a page on IBM's site with significant documentation. Please see below. I believe I know what is wrong with my OLEDB connection, it is the set-up. I have a working ODBC now and can help you with that if need be.

http://www-306.ibm.com/software/data/informix/pubs/library/csdk_29.html

Regards,

Matt

|||

Yes, I am connecting using Informix OLE DB. I tried create a linked server with SQL Server 2005 with the IBM Informix OLE DB Provider. Here is my connection info. Did specify something wrong? It seems you abandoned this approach. Should I do the same and try to link the server with ODBC?

/****** Object: LinkedServer [MINNYME5] Script Date: 05/02/2006 11:10:06 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'MINNYME5', @.srvproduct=N'Informix-CLI 2.9 (32 bit)', @.provider=N'Ifxoledbc', @.datasrc=N'bsco@.becton_tcp', @.provstr=N'server=bsco@.becton_tcp;database=bsco;uid=informix;password=tewwetca'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'use remote collation', @.optvalue=N'true'

|||

Here is the code to set up the ODBC linked server. Please read the comments and notes provided throughout. I can help you further if need be. Just so we are on the same page, I'm using

OS: Windows XP Pro.

DotNet Framework: .NET SDK 1.1

IBM software development kit version: IBM CSDK 2.9

Good Luck!

EXEC sp_addlinkedserver

@.server = 'yourServer', -- defined in SetNet32 on tab 'Server information', -- field 'Informix Server'

@.provider = 'MSDASQL', -- DO NOT CHANGE !

@.datasrc = 'yourOdbcConnectionName', -- name of the ODBC connection defined in your system. (you must configure this.)

@.srvproduct = 'Informix-CLI 3.30 (32 bit)',

@.provstr = 'Host=yourHost;Server=yourServer;Service=yourService;Protocol=olsoctcp;Database=yourDB;UID=yourUIDToConnectToAIX;PWD=yourPWD'

NOTE: I'm not 100% sure if a login is needed but i created one.

EXEC sp_addlinkedsrvlogin 'yourLinkedServer', 'false', 'yourWindowsXPUserID', 'yourUIDToConnectToAIX', 'yourPassword'

READ-You need to set up permission for the UserId connecting to the AIX server in the AIX server. I had a AIX admin set me up considering I know little about IBM products.

Run the following query from SQL SERVER to query an ODBC linked server:

SELECT * FROM OPENQUERY(yourLinkedServer, 'select * from yourTable')

|||i done that , but cant insert into linked server tables and this error happens
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].
do u know why?

|||

Although I have never tried inserting a row into an Informix Linked Server, I'd assume that it is not possible considering I've seen similar results with other data sources.

|||thank you for replying
also i cant set linked server with oledb informix
when setup linked server with odbc can update, delete and select from linked table but only cant insert into the table

IBM Informix Ole Db Provider

Hello,

I'm getting the below errors when using sql server's data import wizard. Please Note: My test connection was successful and the error occurs when I click the "Next" button after closing the properties window. For Security reasons I've filled the User Id and DataSource with a bogus account, database, and server i.e. xxxx. Notice the password is blank?... Is that normal? I'm in a jam and appreciate your help.

Thanks,

Matt

===================================

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=xxx;Data Source=xxx@.xxxx". (SQL Server Import and Export Wizard)

===================================

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Program Location:

at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.LoadTypeData(OleDbConnection myDestConnection)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.CacheDataSourceInfo(IDbConnection myConnection)

Moving to the "SQL Server Integration Services", which deals with data transformation issues.

|||

This means your OLE DB provider does not publish Provider_Types schema. The wizard relies on this schema to map data types between heterogeneous data sources.

You may want to try to build your SSIS package manually using OLE DB Source to access your Informix database.

Thanks.

|||

Hello,

Does your solution apply to the linked server as well? Any chance you know if SQL Server 2005 allows setting up an informix 7.3 db as a Linked Server? I now can connect with ADO.NET 2.0 using the IBM.Data.Informix namespace but have not been able to find any documentation on setting up connection through a linked server. FYI, I now have to correct IBM SDKs installed (2.9).

Thanks,

Matt

|||

I am having the same problem. Is there a way to get SQL server 2005 to talk to the Informix 7.3 engine? I get the following error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Ifxoledbc" for linked server "MINNYME2" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "MINNYME2".

|||

You are in luck. I'm currently still looking into this but have done a significant grind and configured the connection with ODBC. How are you connecting? With the Informix OLEDB? I'm currently waiting for the AIX admin to set something up on the server. He has to run a few scripts to set up the OLEDB properly. I found a page on IBM's site with significant documentation. Please see below. I believe I know what is wrong with my OLEDB connection, it is the set-up. I have a working ODBC now and can help you with that if need be.

http://www-306.ibm.com/software/data/informix/pubs/library/csdk_29.html

Regards,

Matt

|||

Yes, I am connecting using Informix OLE DB. I tried create a linked server with SQL Server 2005 with the IBM Informix OLE DB Provider. Here is my connection info. Did specify something wrong? It seems you abandoned this approach. Should I do the same and try to link the server with ODBC?

/****** Object: LinkedServer [MINNYME5] Script Date: 05/02/2006 11:10:06 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'MINNYME5', @.srvproduct=N'Informix-CLI 2.9 (32 bit)', @.provider=N'Ifxoledbc', @.datasrc=N'bsco@.becton_tcp', @.provstr=N'server=bsco@.becton_tcp;database=bsco;uid=informix;password=tewwetca'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'use remote collation', @.optvalue=N'true'

|||

Here is the code to set up the ODBC linked server. Please read the comments and notes provided throughout. I can help you further if need be. Just so we are on the same page, I'm using

OS: Windows XP Pro.

DotNet Framework: .NET SDK 1.1

IBM software development kit version: IBM CSDK 2.9

Good Luck!

EXEC sp_addlinkedserver

@.server = 'yourServer', -- defined in SetNet32 on tab 'Server information', -- field 'Informix Server'

@.provider = 'MSDASQL', -- DO NOT CHANGE !

@.datasrc = 'yourOdbcConnectionName', -- name of the ODBC connection defined in your system. (you must configure this.)

@.srvproduct = 'Informix-CLI 3.30 (32 bit)',

@.provstr = 'Host=yourHost;Server=yourServer;Service=yourService;Protocol=olsoctcp;Database=yourDB;UID=yourUIDToConnectToAIX;PWD=yourPWD'

NOTE: I'm not 100% sure if a login is needed but i created one.

EXEC sp_addlinkedsrvlogin 'yourLinkedServer', 'false', 'yourWindowsXPUserID', 'yourUIDToConnectToAIX', 'yourPassword'

READ-You need to set up permission for the UserId connecting to the AIX server in the AIX server. I had a AIX admin set me up considering I know little about IBM products.

Run the following query from SQL SERVER to query an ODBC linked server:

SELECT * FROM OPENQUERY(yourLinkedServer, 'select * from yourTable')

|||i done that , but cant insert into linked server tables and this error happens
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].
do u know why?

|||

Although I have never tried inserting a row into an Informix Linked Server, I'd assume that it is not possible considering I've seen similar results with other data sources.

|||thank you for replying
also i cant set linked server with oledb informix
when setup linked server with odbc can update, delete and select from linked table but only cant insert into the table

I/O requests taking longer than 15 seconds to complete

We get below error in MS SQL 2005 server which is fail over virtual server on
windows 2000 advanced servers clusterring.
SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
than 15 seconds to complete on file [Y:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file
handle is 0x00000768. The offset of the latest long I/O is: 0x00000000652000
..
what is the cause and solution ?
NOTE:- 1- there are no sql 2005 service packs installed.
2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
updated upto date .
3- this error is generated on application peak hours .
This blog entry explains the error and offers suggestions
for addressing the issue:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
-Sue
On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
<maslooki@.discussions.microsoft.com> wrote:

>We get below error in MS SQL 2005 server which is fail over virtual server on
>windows 2000 advanced servers clusterring.
>SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
>than 15 seconds to complete on file [Y:\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file
>handle is 0x00000768. The offset of the latest long I/O is: 0x00000000652000
>.
>what is the cause and solution ?
>NOTE:- 1- there are no sql 2005 service packs installed.
>2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
>updated upto date .
>3- this error is generated on application peak hours .
>
|||Thank you MR.Sue Hoegemeier for reply
We also get the below error on the application side :-
" Transaction (Process ID 57) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Return the transaction. "
Please again what is the cause and solution for this error ?
Note :- This happens on peak hours when they generate Reports and upload
transactions .
Thanks for cooperation
"Sue Hoegemeier" wrote:

> This blog entry explains the error and offers suggestions
> for addressing the issue:
> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
> -Sue
> On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
> <maslooki@.discussions.microsoft.com> wrote:
>
>
|||You may consider ensuring that your reporting queries (I assume SELECT only)
use snapshot isolation level. Better still would be to replicate or mirror
to a reporting server which will handle the queries independent of the true
OLTP activity.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"maslooki" <maslooki@.discussions.microsoft.com> wrote in message
news:1D25A567-53FF-4667-9D3D-39F50D1F7FD7@.microsoft.com...[vbcol=seagreen]
> Thank you MR.Sue Hoegemeier for reply
> We also get the below error on the application side :-
> " Transaction (Process ID 57) was deadlocked on lock resources with
> another
> process and has been chosen as the deadlock victim. Return the
> transaction. "
> Please again what is the cause and solution for this error ?
> Note :- This happens on peak hours when they generate Reports and upload
> transactions .
> Thanks for cooperation
> "Sue Hoegemeier" wrote:
|||Thank you all,
Our programmers are working on it.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You may consider ensuring that your reporting queries (I assume SELECT only)
> use snapshot isolation level. Better still would be to replicate or mirror
> to a reporting server which will handle the queries independent of the true
> OLTP activity.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "maslooki" <maslooki@.discussions.microsoft.com> wrote in message
> news:1D25A567-53FF-4667-9D3D-39F50D1F7FD7@.microsoft.com...
>
>

I/O requests taking longer than 15 seconds to complete

We get below error in MS SQL 2005 server which is fail over virtual server o
n
windows 2000 advanced servers clusterring.
SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
than 15 seconds to complete on file [Y:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS
file
handle is 0x00000768. The offset of the latest long I/O is: 0x0000000065200
0
.
what is the cause and solution ?
NOTE:- 1- there are no sql 2005 service packs installed.
2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
updated upto date .
3- this error is generated on application peak hours .This blog entry explains the error and offers suggestions
for addressing the issue:
http://blogs.msdn.com/sqlserverstor.../21/642314.aspx
-Sue
On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
<maslooki@.discussions.microsoft.com> wrote:

>We get below error in MS SQL 2005 server which is fail over virtual server
on
>windows 2000 advanced servers clusterring.
>SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
>than 15 seconds to complete on file [Y:\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS
file
>handle is 0x00000768. The offset of the latest long I/O is: 0x000000006520
00
>.
>what is the cause and solution ?
>NOTE:- 1- there are no sql 2005 service packs installed.
>2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
>updated upto date .
>3- this error is generated on application peak hours .
>|||Thank you MR.Sue Hoegemeier for reply
We also get the below error on the application side :-
" Transaction (Process ID 57) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Return the transaction.
"
Please again what is the cause and solution for this error ?
Note :- This happens on peak hours when they generate Reports and upload
transactions .
Thanks for cooperation
"Sue Hoegemeier" wrote:

> This blog entry explains the error and offers suggestions
> for addressing the issue:
> [url]http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx[/url
]
> -Sue
> On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
> <maslooki@.discussions.microsoft.com> wrote:
>
>|||You may consider ensuring that your reporting queries (I assume SELECT only)
use snapshot isolation level. Better still would be to replicate or mirror
to a reporting server which will handle the queries independent of the true
OLTP activity.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"maslooki" <maslooki@.discussions.microsoft.com> wrote in message
news:1D25A567-53FF-4667-9D3D-39F50D1F7FD7@.microsoft.com...[vbcol=seagreen]
> Thank you MR.Sue Hoegemeier for reply
> We also get the below error on the application side :-
> " Transaction (Process ID 57) was deadlocked on lock resources with
> another
> process and has been chosen as the deadlock victim. Return the
> transaction. "
> Please again what is the cause and solution for this error ?
> Note :- This happens on peak hours when they generate Reports and upload
> transactions .
> Thanks for cooperation
> "Sue Hoegemeier" wrote:
>|||Thank you all,
Our programmers are working on it.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You may consider ensuring that your reporting queries (I assume SELECT onl
y)
> use snapshot isolation level. Better still would be to replicate or mirro
r
> to a reporting server which will handle the queries independent of the tru
e
> OLTP activity.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "maslooki" <maslooki@.discussions.microsoft.com> wrote in message
> news:1D25A567-53FF-4667-9D3D-39F50D1F7FD7@.microsoft.com...
>
>sql

I/O requests taking longer than 15 seconds to complete

We get below error in MS SQL 2005 server which is fail over virtual server on
windows 2000 advanced servers clusterring.
SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
than 15 seconds to complete on file [Y:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file
handle is 0x00000768. The offset of the latest long I/O is: 0x00000000652000
.
what is the cause and solution ?
NOTE:- 1- there are no sql 2005 service packs installed.
2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
updated upto date .
3- this error is generated on application peak hours .This blog entry explains the error and offers suggestions
for addressing the issue:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
-Sue
On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
<maslooki@.discussions.microsoft.com> wrote:
>We get below error in MS SQL 2005 server which is fail over virtual server on
>windows 2000 advanced servers clusterring.
>SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
>than 15 seconds to complete on file [Y:\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file
>handle is 0x00000768. The offset of the latest long I/O is: 0x00000000652000
>.
>what is the cause and solution ?
>NOTE:- 1- there are no sql 2005 service packs installed.
>2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
>updated upto date .
>3- this error is generated on application peak hours .
>|||Thank you MR.Sue Hoegemeier for reply
We also get the below error on the application side :-
" Transaction (Process ID 57) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Return the transaction. "
Please again what is the cause and solution for this error ?
Note :- This happens on peak hours when they generate Reports and upload
transactions .
Thanks for cooperation
"Sue Hoegemeier" wrote:
> This blog entry explains the error and offers suggestions
> for addressing the issue:
> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
> -Sue
> On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
> <maslooki@.discussions.microsoft.com> wrote:
> >We get below error in MS SQL 2005 server which is fail over virtual server on
> >windows 2000 advanced servers clusterring.
> >
> >SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
> >than 15 seconds to complete on file [Y:\Microsoft SQL
> >Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file
> >handle is 0x00000768. The offset of the latest long I/O is: 0x00000000652000
> >.
> >
> >what is the cause and solution ?
> >
> >NOTE:- 1- there are no sql 2005 service packs installed.
> >2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
> >updated upto date .
> >3- this error is generated on application peak hours .
> >
>|||You may consider ensuring that your reporting queries (I assume SELECT only)
use snapshot isolation level. Better still would be to replicate or mirror
to a reporting server which will handle the queries independent of the true
OLTP activity.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"maslooki" <maslooki@.discussions.microsoft.com> wrote in message
news:1D25A567-53FF-4667-9D3D-39F50D1F7FD7@.microsoft.com...
> Thank you MR.Sue Hoegemeier for reply
> We also get the below error on the application side :-
> " Transaction (Process ID 57) was deadlocked on lock resources with
> another
> process and has been chosen as the deadlock victim. Return the
> transaction. "
> Please again what is the cause and solution for this error ?
> Note :- This happens on peak hours when they generate Reports and upload
> transactions .
> Thanks for cooperation
> "Sue Hoegemeier" wrote:
>> This blog entry explains the error and offers suggestions
>> for addressing the issue:
>> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
>> -Sue
>> On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
>> <maslooki@.discussions.microsoft.com> wrote:
>> >We get below error in MS SQL 2005 server which is fail over virtual
>> >server on
>> >windows 2000 advanced servers clusterring.
>> >
>> >SQL Server has encountered 16 occurrence(s) of I/O requests taking
>> >longer
>> >than 15 seconds to complete on file [Y:\Microsoft SQL
>> >Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS
>> >file
>> >handle is 0x00000768. The offset of the latest long I/O is:
>> >0x00000000652000
>> >.
>> >
>> >what is the cause and solution ?
>> >
>> >NOTE:- 1- there are no sql 2005 service packs installed.
>> >2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
>> >updated upto date .
>> >3- this error is generated on application peak hours .
>> >
>>|||Thank you all,
Our programmers are working on it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> You may consider ensuring that your reporting queries (I assume SELECT only)
> use snapshot isolation level. Better still would be to replicate or mirror
> to a reporting server which will handle the queries independent of the true
> OLTP activity.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "maslooki" <maslooki@.discussions.microsoft.com> wrote in message
> news:1D25A567-53FF-4667-9D3D-39F50D1F7FD7@.microsoft.com...
> > Thank you MR.Sue Hoegemeier for reply
> >
> > We also get the below error on the application side :-
> > " Transaction (Process ID 57) was deadlocked on lock resources with
> > another
> > process and has been chosen as the deadlock victim. Return the
> > transaction. "
> >
> > Please again what is the cause and solution for this error ?
> >
> > Note :- This happens on peak hours when they generate Reports and upload
> > transactions .
> >
> > Thanks for cooperation
> >
> > "Sue Hoegemeier" wrote:
> >
> >> This blog entry explains the error and offers suggestions
> >> for addressing the issue:
> >> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
> >>
> >> -Sue
> >>
> >> On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
> >> <maslooki@.discussions.microsoft.com> wrote:
> >>
> >> >We get below error in MS SQL 2005 server which is fail over virtual
> >> >server on
> >> >windows 2000 advanced servers clusterring.
> >> >
> >> >SQL Server has encountered 16 occurrence(s) of I/O requests taking
> >> >longer
> >> >than 15 seconds to complete on file [Y:\Microsoft SQL
> >> >Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS
> >> >file
> >> >handle is 0x00000768. The offset of the latest long I/O is:
> >> >0x00000000652000
> >> >.
> >> >
> >> >what is the cause and solution ?
> >> >
> >> >NOTE:- 1- there are no sql 2005 service packs installed.
> >> >2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
> >> >updated upto date .
> >> >3- this error is generated on application peak hours .
> >> >
> >>
> >>
>
>

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5
Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>
|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I
> I
>

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> so if I did combin(35,3) the result would be 6545 and the combinations
>> are
>> listed below. I know how to do it by creating three nested loops in c#
>> but
> I
>> think there's a way to do it in sql by using a cross join or self join
>> but
> I
>> don't see it.
>> Thanks
>>
>> 1 2 3
>> 1 2 4
>> 1 2 5
>> 1 2 6
>> 1 2 7
>> 1 2 8
>> 1 2 9
>> 1 2 10
>> 1 2 11
>> 1 2 12
>> 1 2 13
>> 1 2 14
>> 1 2 15
>> 1 2 16
>> 1 2 17
>> 1 2 18
>> 1 2 19
>> 1 2 20
>> 1 2 21
>> 1 2 22
>> 1 2 23
>> 1 2 24
>> 1 2 25
>> 1 2 26
>> 1 2 27
>> 1 2 28
>> 1 2 29
>> 1 2 30
>> 1 2 31
>> 1 2 32
>> 1 2 33
>> 1 2 34
>> 1 2 35
>> 1 3 4
>> 1 3 5
>> 1 3 6
>> 1 3 7
>> 1 3 8
>> 1 3 9
>> 1 3 10
>> 1 3 11
>> 1 3 12
>> 1 3 13
>> 1 3 14
>> 1 3 15
>> 1 3 16
>> 1 3 17
>> 1 3 18
>> 1 3 19
>> 1 3 20
>> 1 3 21
>> 1 3 22
>> 1 3 23
>> 1 3 24
>> 1 3 25
>> 1 3 26
>> 1 3 27
>> 1 3 28
>> 1 3 29
>> 1 3 30
>> 1 3 31
>> 1 3 32
>> 1 3 33
>> 1 3 34
>> 1 3 35
>> 1 4 5
>>
>sql

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I
> I
>

I need some serious help

Out of the 3 tables below. I need to get a tabular report like this
First Name Last Name Email
Can you receive HTML email Address State
Zip
Tom Thompson xxxx@.xxxxxxxx.com
Yes 575 mystreet Rd
AK 14525
steve Smith
aaaa@.aaaaaaaa.com No
575 double Dam Rd AL 13323
table 1
QuestionID ModID Question
18 362 First Name
19 362 Last Name
20 362 Email
21 362 Can you receive HTML email
23 362 Address
24 362 State
25 362 Zip
Table 2
Resultid QuestionID OptionD
OptionTextboxValue SurveyResultID modid
2051 18 -55 Tom
140 362
2052 19 -55
Thompson 140 362
2053 20 -55
xxxx@.xxxxxxxx.com 140 362
2055 21 47
140 362
2056 23 -55 575
mystreet Rd 140 362
2057 24 52
140 362
2058 25 -55 14525
140 362
2059 18 -55 steve
140 362
2060 19 -55 Smith
140 362
2061 20 -55
aaaa@.aaaaaaaa.com 140 362
2063 21 48
140 362
2064 23 -55 575
double Dam Rd 140 362
2065 24 53
140 362
2066 25 -55 13323
140 362
Table3
optionid QuestionID optionText
41 18 firstName
43 19 lastName
45 20 emailAdd
47 21 Yes
48 21 No
50 23 address
52 24 AK
53 24 AL
55 25 zipOn Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:

>Out of the 3 tables below. I need to get a tabular report like this
(snip)
Hi Dave,
You posted this same message about a week ago in .programming. David
Portas gave you an excellent answer. For your convenience, I've copied the
complete answer below. Do read the entire message and follow the link in
the last paragraph!
(start quote)
What is the rationale for your table design and for persisting the UI
information in the database? Don't you have normalized tables to
represent this information? If this is some kind of content management
layer then I would suggest you don't use it for reporting. Utilize it
as a Staging database with an ETL process to load into a normalized
data model. There are plenty of reasons why the
"entity-attribute-value" model you are proposing should be avoided in
SQL.
Anyway, take a look here for some solutions to your cross-tab report:
http://www.aspfaq.com/2462
(end quote)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'David' to a column of data type
int.
SELECT SurveyResultID,
SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
FROM FormCreator_Results
GROUP BY SurveyResultID
GO
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gsq811lbdg6r3sl5rm1088li7hegceq459@.
4ax.com...
> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>
> (snip)
> Hi Dave,
> You posted this same message about a week ago in .programming. David
> Portas gave you an excellent answer. For your convenience, I've copied the
> complete answer below. Do read the entire message and follow the link in
> the last paragraph!
> (start quote)
> What is the rationale for your table design and for persisting the UI
> information in the database? Don't you have normalized tables to
> represent this information? If this is some kind of content management
> layer then I would suggest you don't use it for reporting. Utilize it
> as a Staging database with an ETL process to load into a normalized
> data model. There are plenty of reasons why the
> "entity-attribute-value" model you are proposing should be avoided in
> SQL.
> Anyway, take a look here for some solutions to your cross-tab report:
> http://www.aspfaq.com/2462
> (end quote)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||The result of a CASE need to be of the same datatype. Is the different expre
ssions inside the CASE
returns different datatypes, then SQL Server will try implicit datatype conv
ersion so that end
result is of the datatype which has the highest precedence according to "Dat
atype Precedence" in
Books Online. Int is higher than the string datatypes, and the string 'David
' cannot be converted to
an int. What you can do is, inside the CASE, use an explicit CAST around the
columns which are
integer so you convert them to appropriate strings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.g
bl...
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'David' to a column of data type
> int.
> SELECT SurveyResultID,
> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
> FROM FormCreator_Results
> GROUP BY SurveyResultID
> GO
> --
>
> David Fetrow
> Helixpoint LLC.
> http://www.helixpoint.com
> davef@.helixpoint.com
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.
4ax.com...
>|||It looks like your OptionTextBoxValue column must be a VARCHAR yet you
are trying to SUM it! I'll guess you only want to sum the numeric
values. Add a WHERE clause:
...
WHERE QuestionID BETWEEN 18 AND 21
or
...
WHERE OptionTextboxValue NOT LIKE '%[^0-9]%'
This complexity is one of the consequences of a having weakly-typed,
multi-valued columns and is an example of why your table design badly
needs fixing.
David Portas
SQL Server MVP
--|||Oops. See David's post. I missed the fact that SUM is performed, and it is p
retty darn hard to sum
strings. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%238OmS7OFFHA.1296@.TK2MSFTNGP10.phx.gbl...
> The result of a CASE need to be of the same datatype. Is the different exp
ressions inside the CASE
> returns different datatypes, then SQL Server will try implicit datatype co
nversion so that end
> result is of the datatype which has the highest precedence according to "D
atatype Precedence" in
> Books Online. Int is higher than the string datatypes, and the string 'Dav
id' cannot be converted
> to an int. What you can do is, inside the CASE, use an explicit CAST aroun
d the columns which are
> integer so you convert them to appropriate strings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.
TK2MSFTNGP10.phx.gbl...
>

I need some serious help

Out of the 3 tables below. I need to get a tabular report like this
First Name Last Name Email
Can you receive HTML email Address State
Zip
Tom Thompson xxxx@.xxxxxxxx.com
Yes 575 mystreet Rd
AK 14525
steve Smith
aaaa@.aaaaaaaa.com No
575 double Dam Rd AL 13323
table 1
QuestionID ModID Question
18 362 First Name
19 362 Last Name
20 362 Email
21 362 Can you receive HTML email
23 362 Address
24 362 State
25 362 Zip
Table 2
Resultid QuestionID OptionD
OptionTextboxValue SurveyResultID modid
2051 18 -55 Tom
140 362
2052 19 -55
Thompson 140 362
2053 20 -55
xxxx@.xxxxxxxx.com 140 362
2055 21 47
140 362
2056 23 -55 575
mystreet Rd 140 362
2057 24 52
140 362
2058 25 -55 14525
140 362
2059 18 -55 steve
140 362
2060 19 -55 Smith
140 362
2061 20 -55
aaaa@.aaaaaaaa.com 140 362
2063 21 48
140 362
2064 23 -55 575
double Dam Rd 140 362
2065 24 53
140 362
2066 25 -55 13323
140 362
Table3
optionid QuestionID optionText
41 18 firstName
43 19 lastName
45 20 emailAdd
47 21 Yes
48 21 No
50 23 address
52 24 AK
53 24 AL
55 25 zipOn Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>Out of the 3 tables below. I need to get a tabular report like this
(snip)
Hi Dave,
You posted this same message about a week ago in .programming. David
Portas gave you an excellent answer. For your convenience, I've copied the
complete answer below. Do read the entire message and follow the link in
the last paragraph!
(start quote)
What is the rationale for your table design and for persisting the UI
information in the database? Don't you have normalized tables to
represent this information? If this is some kind of content management
layer then I would suggest you don't use it for reporting. Utilize it
as a Staging database with an ETL process to load into a normalized
data model. There are plenty of reasons why the
"entity-attribute-value" model you are proposing should be avoided in
SQL.
Anyway, take a look here for some solutions to your cross-tab report:
http://www.aspfaq.com/2462
(end quote)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'David' to a column of data type
int.
SELECT SurveyResultID,
SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
FROM FormCreator_Results
GROUP BY SurveyResultID
GO
--
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
> >Out of the 3 tables below. I need to get a tabular report like this
> (snip)
> Hi Dave,
> You posted this same message about a week ago in .programming. David
> Portas gave you an excellent answer. For your convenience, I've copied the
> complete answer below. Do read the entire message and follow the link in
> the last paragraph!
> (start quote)
> What is the rationale for your table design and for persisting the UI
> information in the database? Don't you have normalized tables to
> represent this information? If this is some kind of content management
> layer then I would suggest you don't use it for reporting. Utilize it
> as a Staging database with an ETL process to load into a normalized
> data model. There are plenty of reasons why the
> "entity-attribute-value" model you are proposing should be avoided in
> SQL.
> Anyway, take a look here for some solutions to your cross-tab report:
> http://www.aspfaq.com/2462
> (end quote)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
returns different datatypes, then SQL Server will try implicit datatype conversion so that end
result is of the datatype which has the highest precedence according to "Datatype Precedence" in
Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted to
an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
integer so you convert them to appropriate strings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'David' to a column of data type
> int.
> SELECT SurveyResultID,
> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
> FROM FormCreator_Results
> GROUP BY SurveyResultID
> GO
> --
>
> David Fetrow
> Helixpoint LLC.
> http://www.helixpoint.com
> davef@.helixpoint.com
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||It looks like your OptionTextBoxValue column must be a VARCHAR yet you
are trying to SUM it! I'll guess you only want to sum the numeric
values. Add a WHERE clause:
...
WHERE QuestionID BETWEEN 18 AND 21
or
...
WHERE OptionTextboxValue NOT LIKE '%[^0-9]%'
This complexity is one of the consequences of a having weakly-typed,
multi-valued columns and is an example of why your table design badly
needs fixing.
--
David Portas
SQL Server MVP
--|||Oops. See David's post. I missed the fact that SUM is performed, and it is pretty darn hard to sum
strings. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%238OmS7OFFHA.1296@.TK2MSFTNGP10.phx.gbl...
> The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
> returns different datatypes, then SQL Server will try implicit datatype conversion so that end
> result is of the datatype which has the highest precedence according to "Datatype Precedence" in
> Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted
> to an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
> integer so you convert them to appropriate strings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
>> Server: Msg 245, Level 16, State 1, Line 1
>> Syntax error converting the varchar value 'David' to a column of data type
>> int.
>> SELECT SurveyResultID,
>> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
>> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
>> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
>> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
>> FROM FormCreator_Results
>> GROUP BY SurveyResultID
>> GO
>> --
>>
>> David Fetrow
>> Helixpoint LLC.
>> http://www.helixpoint.com
>> davef@.helixpoint.com
>> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>>
>

I need some serious help

Out of the 3 tables below. I need to get a tabular report like this
First Name Last Name Email
Can you receive HTML email Address State
Zip
Tom Thompson xxxx@.xxxxxxxx.com
Yes 575 mystreet Rd
AK 14525
steve Smith
aaaa@.aaaaaaaa.com No
575 double Dam Rd AL 13323
table 1
QuestionID ModID Question
18 362 First Name
19 362 Last Name
20 362 Email
21 362 Can you receive HTML email
23 362 Address
24 362 State
25 362 Zip
Table 2
Resultid QuestionID OptionD
OptionTextboxValue SurveyResultID modid
2051 18 -55 Tom
140 362
2052 19 -55
Thompson 140 362
2053 20 -55
xxxx@.xxxxxxxx.com 140 362
2055 21 47
140 362
2056 23 -55 575
mystreet Rd 140 362
2057 24 52
140 362
2058 25 -55 14525
140 362
2059 18 -55 steve
140 362
2060 19 -55 Smith
140 362
2061 20 -55
aaaa@.aaaaaaaa.com 140 362
2063 21 48
140 362
2064 23 -55 575
double Dam Rd 140 362
2065 24 53
140 362
2066 25 -55 13323
140 362
Table3
optionid QuestionID optionText
41 18 firstName
43 19 lastName
45 20 emailAdd
47 21 Yes
48 21 No
50 23 address
52 24 AK
53 24 AL
55 25 zip
On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:

>Out of the 3 tables below. I need to get a tabular report like this
(snip)
Hi Dave,
You posted this same message about a week ago in .programming. David
Portas gave you an excellent answer. For your convenience, I've copied the
complete answer below. Do read the entire message and follow the link in
the last paragraph!
(start quote)
What is the rationale for your table design and for persisting the UI
information in the database? Don't you have normalized tables to
represent this information? If this is some kind of content management
layer then I would suggest you don't use it for reporting. Utilize it
as a Staging database with an ETL process to load into a normalized
data model. There are plenty of reasons why the
"entity-attribute-value" model you are proposing should be avoided in
SQL.
Anyway, take a look here for some solutions to your cross-tab report:
http://www.aspfaq.com/2462
(end quote)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'David' to a column of data type
int.
SELECT SurveyResultID,
SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
FROM FormCreator_Results
GROUP BY SurveyResultID
GO
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
> (snip)
> Hi Dave,
> You posted this same message about a week ago in .programming. David
> Portas gave you an excellent answer. For your convenience, I've copied the
> complete answer below. Do read the entire message and follow the link in
> the last paragraph!
> (start quote)
> What is the rationale for your table design and for persisting the UI
> information in the database? Don't you have normalized tables to
> represent this information? If this is some kind of content management
> layer then I would suggest you don't use it for reporting. Utilize it
> as a Staging database with an ETL process to load into a normalized
> data model. There are plenty of reasons why the
> "entity-attribute-value" model you are proposing should be avoided in
> SQL.
> Anyway, take a look here for some solutions to your cross-tab report:
> http://www.aspfaq.com/2462
> (end quote)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
returns different datatypes, then SQL Server will try implicit datatype conversion so that end
result is of the datatype which has the highest precedence according to "Datatype Precedence" in
Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted to
an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
integer so you convert them to appropriate strings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'David' to a column of data type
> int.
> SELECT SurveyResultID,
> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
> FROM FormCreator_Results
> GROUP BY SurveyResultID
> GO
> --
>
> David Fetrow
> Helixpoint LLC.
> http://www.helixpoint.com
> davef@.helixpoint.com
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>
|||It looks like your OptionTextBoxValue column must be a VARCHAR yet you
are trying to SUM it! I'll guess you only want to sum the numeric
values. Add a WHERE clause:
...
WHERE QuestionID BETWEEN 18 AND 21
or
...
WHERE OptionTextboxValue NOT LIKE '%[^0-9]%'
This complexity is one of the consequences of a having weakly-typed,
multi-valued columns and is an example of why your table design badly
needs fixing.
David Portas
SQL Server MVP
|||Oops. See David's post. I missed the fact that SUM is performed, and it is pretty darn hard to sum
strings. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%238OmS7OFFHA.1296@.TK2MSFTNGP10.phx.gbl...
> The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
> returns different datatypes, then SQL Server will try implicit datatype conversion so that end
> result is of the datatype which has the highest precedence according to "Datatype Precedence" in
> Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted
> to an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
> integer so you convert them to appropriate strings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
>

I need some help on my Sql string

Hi,

i have a sqlString to select records from database as below,

StrSql="Select Distinct ItemCode From StockOnHand Where " & _
 "Description like @.Des"

this sql string works correctly, but i need to display those distinct ItemCode with their Description and Price, but i failed using the sql String at below...

StrSql="Select Distinct ItemCode, Description, Price From StockOnHand Where " & _
 "Description like @.Des"

I know there is something wrong with my sql string.. but my SQL knowledge is onli at the basic level, can any1 pls help me on this...?

Thanks in advances
life's Ng::I know there is something wrong with my sql string..

Good, let's start VERY VERY VERY at the basics.

This:

::but i failed using the sql String at below...

will NOT get you help.

It did not "just fail", it gave you an error string DESCRIBING the error. Now, even while you may not understand this error string when you read it (and normally it is pretty self-explaining), WE may want to have a look at the EXACT error coming there.

So please, post the error you got, too.

Btw, using:

"Select DISTINCT" is about the best way to kill your peroformance anyhow. And - sorry - not really smart in the queries you give there. Unless, naturally, you want your database to be terribly slow.|||Without knowing the exact error, I can tell you that this is not correct:
"Description like @.Des"

try this instead:
"Description like '%@.Des%'"|||Hi, Sorry for my mistake to not descript my problem well and thanks for ur info bout "DISTINCT"

i dint receive any error with both of the SQL statements.

In my table, there is 10 columns ("Description", "ItemCode", "Prices".......)

the 2nd one return records which is not expected... few records selected with the same ItemCode, i onli want the distinct ItemCode displayed according to its description.

This page is created just for user to search&check on the ItemCode using their Description... I think i need to reconstruct my database design.

Best Regards
life's Ng

I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
Learn how not to multi-post to every single group and next time maybe you'll
get some help.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:H4GdnZuIZaAui8LfRVn-hg@.comcast.com...
> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
> SELECT col1, col2 FROM table1 WHERE col3 < @.V
> In col1 rows have many duplicates
> col2 has unique values
> col3 can have 5 possible values: 5, 10, 15, 20, 25
> Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
> The goal is to:
> Return row values from col1 without duplicates, and return corresponding
> values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
> maximum value from all possible values but is <= @.V
> Thank you.
> Vanessa
>

I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
See my answer in .msde.
Jacco Schalkwijk
SQL Server MVP
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:o4SdndJqXfNEiMLfRVn-uQ@.comcast.com...
> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
> SELECT col1, col2 FROM table1 WHERE col3 < @.V
> In col1 rows have many duplicates
> col2 has unique values
> col3 can have 5 possible values: 5, 10, 15, 20, 25
> Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
> The goal is to:
> Return row values from col1 without duplicates, and return corresponding
> values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
> maximum value from all possible values but is <= @.V
> Thank you.
> Vanessa
>

I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
Vanessa
This question has nothing to do with clustering. It looks like you have
posted to every single SQL Server newsgroup, regardless of relevancy. Please
do not do this.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:qISdnUhUgOVTiMLfRVn-hg@.comcast.com...
> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
> SELECT col1, col2 FROM table1 WHERE col3 < @.V
> In col1 rows have many duplicates
> col2 has unique values
> col3 can have 5 possible values: 5, 10, 15, 20, 25
> Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
> The goal is to:
> Return row values from col1 without duplicates, and return corresponding
> values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
> maximum value from all possible values but is <= @.V
> Thank you.
> Vanessa
>

I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
SELECT distinct col1, col2 FROM table1 WHERE col3 < @.V

I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
Learn how not to multi-post to every single group and next time maybe you'll
get some help.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:PcadnZPKVP9xiMLfRVn-jA@.comcast.com...
> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
> SELECT col1, col2 FROM table1 WHERE col3 < @.V
> In col1 rows have many duplicates
> col2 has unique values
> col3 can have 5 possible values: 5, 10, 15, 20, 25
> Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
> The goal is to:
> Return row values from col1 without duplicates, and return corresponding
> values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
> maximum value from all possible values but is <= @.V
> Thank you.
> Vanessa
>
|||Hi Vanessa,
You can try simply this statement if it does'nt matter which values from
col1 you want to take in case qualifying rows have duplicate in col1.
SELECT Col1,Col2 FROM Table1 WHERE Col1 IN
(SELECT DISTINCT Col1 FROm Table1 WHERE Col3<=@.V)
We can always tweak this query to perform better as i have used IN here
instead oj join.
Thanks,
Vicky Dhawan
"Vanessa Lee" wrote:

> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
> SELECT col1, col2 FROM table1 WHERE col3 < @.V
> In col1 rows have many duplicates
> col2 has unique values
> col3 can have 5 possible values: 5, 10, 15, 20, 25
> Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
> The goal is to:
> Return row values from col1 without duplicates, and return corresponding
> values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
> maximum value from all possible values but is <= @.V
> Thank you.
> Vanessa
>
>

I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
It's not completely crystal clear what you need, but my guess is:
SELECT t1.col1, t1.col2
FROM table1 t1
INNER JOIN (SELECT col1, MAX(col3) AS col3
FROM table1
WHERE col3 <= @.v
GROUP BY col1) t2
ON t1.col1 = t2.col1 AND t1.col3 = t2.col3
Jacco Schalkwijk
SQL Server MVP
"Vanessa Lee" <van77788@.yahoo.com> wrote in message
news:Q7ydnWLCvMGoi8LfRVn-iQ@.comcast.com...
> Hi, Could you help me modify the SELECT statement below that would
> accomplish my final goal.
> SELECT col1, col2 FROM table1 WHERE col3 < @.V
> In col1 rows have many duplicates
> col2 has unique values
> col3 can have 5 possible values: 5, 10, 15, 20, 25
> Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
> The goal is to:
> Return row values from col1 without duplicates, and return corresponding
> values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
> maximum value from all possible values but is <= @.V
> Thank you.
> Vanessa
>

I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
>> Could you help me modify the SELECT statement below that would accomplish[vbcol=seagreen]
Yes, but for such questions it is helpful to post your table structures &
sample data so that others can understand you better. For details refer to:
www.aspfaq.com/5006
[vbcol=seagreen]
Based on guesswork:
SELECT * -- use required column names
FROM tbl t1
WHERE t1.col2 = ( SELECT MAX( t2.col2 )
FROM tbl t2
WHERE t2.col1 = t1.col1
AND t2.col3 = t1.col3 )
AND t2.col3 < @.v ;
Anith

I need help with SELECT statement

Hi, Could you help me modify the SELECT statement below that would
accomplish my final goal.
SELECT col1, col2 FROM table1 WHERE col3 < @.V
In col1 rows have many duplicates
col2 has unique values
col3 can have 5 possible values: 5, 10, 15, 20, 25
Variable @.V can be equal to 5 or 10 or 15 or 20 or 25
The goal is to:
Return row values from col1 without duplicates, and return corresponding
values from col2 (both col1 and col2 in one row) "WHERE" col3 always has
maximum value from all possible values but is <= @.V
Thank you.
Vanessa
Vanessa,
I was going to take a look at this but I noticed that you have already
cross-posted in the programming group. Please give one group a chance before
trying another, otherwise we spend unnecessary time trying to solve
something thaat is already solved elsewhere.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)