Odbc error

Dear all
My one user has installed sql server 2000 enterprise edition , SP3
(8.00.0860) on my computer.
I have created one odbc connection to sql server.
When he want to connect to sql user via odbc in application he gets
following error
Statement(s) could not be prepared
Unable to close cursor
[Microsoft][ODBC SQL Server Driver]Invalid cursor stat
What can be the problem?
RegardsWhat language is the application written in? At least the failing module?
I have seen similar errors in both T/SQL (implying a Stored Procedure) and C
(api level calls) modules.
I suspect it needs to be relinked with your new modules. To me, it sounds
like the api calls either are for a different ODBC release or have messed up
pointers.
If you can supply error numbers and complete error messages, it woulld help
--
Joseph R.P. Maloney, CSP,CCP,CDP
"amish" wrote:
> Dear all
> My one user has installed sql server 2000 enterprise edition , SP3
> (8.00.0860) on my computer.
> I have created one odbc connection to sql server.
> When he want to connect to sql user via odbc in application he gets
> following error
> Statement(s) could not be prepared
> Unable to close cursor
> [Microsoft][ODBC SQL Server Driver]Invalid cursor stat
> What can be the problem?
> Regards
>

ODBC driver needed for Compact Edition

I have the newest 'Everywhere' Compact Edition installed, and have created a new desktop database using VS 2005 after installing the VS 2005 SP1 Beta, all successfully. I have no need to use the Mobile components and no need to syncronize to a mobile device. I can connect to the database programatically in VB.NET no problem.

Instead, I want to connect to the desktop database using MS Access. So far, I can't connect using the standard (old) SQLServer ODBC driver, and haven't found any new driver, either. Am I just premature and need to be patient? Does anyone know if there's one in the works? Or am I the only one to have thought of it?

Any help would be appreciated.

Thanks

Merle Nicholson

SQL Server Compact (was Everywhere) Edition does not have ODBC Driver. It has OLEDB and ADO.NET providers right now.

Thanks,

Laxmi

ODBC datasource wont work on report server!!

Hi,

I have VS2005 and sql server express and ive created some reports using sql server
as a backend. everything works as it should.

THEN...

i created a report that uses a system DSN odbc source and it works in VS2005 it works fine.
i have deployed it to the report server and the datasource appears t be there, but when i try and run the report i ge the following message

An error has occurred during report processing.

An attempt has been made to use a data extension 'ODBC' that is not registered for this report server.anyone got any ideas how i can make this work? i have tried to find an explanation using google, but i cannot seem to see any thing that answers the question.

Regards,

Russ
Express versions of Reporting Services allow for data sources to only be local and must be a matching SKU of SQL Server.

ODBC Data Source On Clustered SQL Server

We created an ODBC datasource on a SQL Server in the cluster. Then there was
a hardware failover and subsequently unrelated DTS processes failed because
the data source wasn't defined on the failover machine. Even though those
processes did not use the ODBC source, in fact nothing used the ODBC source,
it was just created to be used in testing.
So, how do you create an ODBC datasouce that will be cluster-wide and not
just machine specific? Do you have to do it through the Cluster Management
console?
Thanks,
Bob
ODBC DSN's are not services and not applications; they are specifications,
typically stored in the registry. If you need it, just like mapped
networked drives, then you will need to create them separately on each
cluster node that requires them. There is no way to cluster them.
However, if you switched to using Data Links or ODBC file-based DSNs, then
you could store them on one of the shared clustered drives. In this case,
they would failover with the group, retain the same drive letter and path;
so, they would always be present with the virtual server.
On to another topic. Although we allow retaining the DTS package in the
MSDB repository and logging to that package, we do not allow the execution
of DTSRun on the DBMS servers. We require our clients to execute those on
an Application Server.
We try to minimize the number of external processes that run alongside the
DBMS memory space in order to maximize performance and system stability.
The last thing we want is for an external process to foul connected to an
internal SQL Server thread.
Sincerely,
Anthony Thomas

"Bob C" <BobC@.discussions.microsoft.com> wrote in message
news:65CC8979-C05A-43A9-92D6-9B156958E8CD@.microsoft.com...
> We created an ODBC datasource on a SQL Server in the cluster. Then there
was
> a hardware failover and subsequently unrelated DTS processes failed
because
> the data source wasn't defined on the failover machine. Even though those
> processes did not use the ODBC source, in fact nothing used the ODBC
source,
> it was just created to be used in testing.
> So, how do you create an ODBC datasouce that will be cluster-wide and not
> just machine specific? Do you have to do it through the Cluster Management
> console?
> Thanks,
> Bob

ODBC data source doesn't like my SQL query

Good morning all,

I have created this query in SQL Server 2005 which uses an MS SQL Server data source - I've tested it and it perfroms as it should:

SELECT DISTINCT
WIP.R0 AS [Job No], WIP_R23.R0 AS [Pack No], PlanningM2.R1 AS [Part No], WIP.R17 AS FKF, WIP2.R17 AS FKF2, SUBSTRING(PlanningM2.R0, 1, 3)
AS Ref, PlanningM2.R5 AS Qty, LTRIM(RTRIM(WIP_R23.R23)) AS Shortages, LEN(PlanningM2.R1) AS StrLen, Stock.R2 AS [Stock Qty],
WIP.R10 AS Status, WIP2.R10 AS [Pack Status], WIP.R15 AS [Qty UC]
FROM C001_UNIDATA_WIP_NF AS WIP INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM ON WIP.R0 = PlanningM.ASSY INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM2 ON PlanningM.R1 = PlanningM2.ASSY INNER JOIN
C001_UNIDATA_WIP_R23 AS WIP_R23 ON PlanningM.R1 = WIP_R23.R0 INNER JOIN
C001_UNIDATA_WIP_NF AS WIP2 ON WIP_R23.R0 = WIP2.R0 INNER JOIN
C001_UNIDATA_STOCK_NF AS Stock ON PlanningM2.R1 = Stock.R0
WHERE (WIP_R23.R23 IS NULL) AND (WIP.R0 LIKE N'%' + @.RP1 + N'%') OR
(WIP.R0 LIKE N'%' + @.RP1 + N'%') AND (PlanningM2.R1 = RIGHT(LTRIM(RTRIM(WIP_R23.R23)), LEN(PlanningM2.R1)))
ORDER BY [Job No], [Pack No]

I am now trying to recreate this query using ODBC and am having one helluva problem with it as it doesn't seem to like many of the functions I've used, such as LTRIM, RTRIM, LEN, RIGHT and won't accept the parameter I've included, or any other parameter come to think of it.

Has anyone else had a similar problem at all, and could you direct me to a solution if you have?

Thanks in advance,

Chris

you could use a table function to execute your query and get results from it...

Select * From MyTableFunction(<parameters>)

This should work...

|||You'd be better off building this into a stored procedure and executing the procedure via your ODBC connection.|||

Check the ODBC driver documentation for the canonical functions. You need to use those instead of data source specific functions to run the same SQL statement using different drivers. This also applies to the SQL grammar. Of course, this depends on the capability of the driver and the level of implementation. For example, below are some of the mappings:

LEN() -> {fn LENGTH(<col_or_expr>)}

LTRIM -> {fn LTRIM(<col_or_expr>)}

RTRIM -> {fn RTRIM(<col_or_expr>)}

|||

Yes the ODBC driver is very limited in what it allows you to do.

Thanks for the nod in the right direction.

ODBC data source doesn't like my SQL query

Good morning all,

I have created this query in SQL Server 2005 which uses an MS SQL Server data source - I've tested it and it perfroms as it should:

SELECT DISTINCT
WIP.R0 AS [Job No], WIP_R23.R0 AS [Pack No], PlanningM2.R1 AS [Part No], WIP.R17 AS FKF, WIP2.R17 AS FKF2, SUBSTRING(PlanningM2.R0, 1, 3)
AS Ref, PlanningM2.R5 AS Qty, LTRIM(RTRIM(WIP_R23.R23)) AS Shortages, LEN(PlanningM2.R1) AS StrLen, Stock.R2 AS [Stock Qty],
WIP.R10 AS Status, WIP2.R10 AS [Pack Status], WIP.R15 AS [Qty UC]
FROM C001_UNIDATA_WIP_NF AS WIP INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM ON WIP.R0 = PlanningM.ASSY INNER JOIN
C001_UNIDATA_PLANNINGM_NF AS PlanningM2 ON PlanningM.R1 = PlanningM2.ASSY INNER JOIN
C001_UNIDATA_WIP_R23 AS WIP_R23 ON PlanningM.R1 = WIP_R23.R0 INNER JOIN
C001_UNIDATA_WIP_NF AS WIP2 ON WIP_R23.R0 = WIP2.R0 INNER JOIN
C001_UNIDATA_STOCK_NF AS Stock ON PlanningM2.R1 = Stock.R0
WHERE (WIP_R23.R23 IS NULL) AND (WIP.R0 LIKE N'%' + @.RP1 + N'%') OR
(WIP.R0 LIKE N'%' + @.RP1 + N'%') AND (PlanningM2.R1 = RIGHT(LTRIM(RTRIM(WIP_R23.R23)), LEN(PlanningM2.R1)))
ORDER BY [Job No], [Pack No]

I am now trying to recreate this query using ODBC and am having one helluva problem with it as it doesn't seem to like many of the functions I've used, such as LTRIM, RTRIM, LEN, RIGHT and won't accept the parameter I've included, or any other parameter come to think of it.

Has anyone else had a similar problem at all, and could you direct me to a solution if you have?

Thanks in advance,

Chris

you could use a table function to execute your query and get results from it...

Select * From MyTableFunction(<parameters>)

This should work...

|||You'd be better off building this into a stored procedure and executing the procedure via your ODBC connection.|||

Check the ODBC driver documentation for the canonical functions. You need to use those instead of data source specific functions to run the same SQL statement using different drivers. This also applies to the SQL grammar. Of course, this depends on the capability of the driver and the level of implementation. For example, below are some of the mappings:

LEN() -> {fn LENGTH(<col_or_expr>)}

LTRIM -> {fn LTRIM(<col_or_expr>)}

RTRIM -> {fn RTRIM(<col_or_expr>)}

|||

Yes the ODBC driver is very limited in what it allows you to do.

Thanks for the nod in the right direction.

ODBC Data Source Administrator

Hi all, hope you can help?
I have created a system DSN in the usual way using the above ODBC tool
(I am trying to create a connection to our Open Accounts package using
the OpenEdge 10.1A driver). I provide the host name, the port number
and the database name, the user ID and password and test the
connection. All works a treat...'Connection established'.
I open up Excel and try to import data using my newly created DSN...it
exposes all of the tables within the database and lets me select
fields from those table. When I get to the 'return data to Microsoft
Excel' part I get the following error message 'Access denied
(Authorisation failed) (7512)'.
Any ideas guys?
Regards
PaulHi Paul,
What database is the Open Accounts package using to store it's data, SQL
Server, Access, Oracle etc. We need to know this because we need to
troubleshoot the security settings/accounts etc. of the database
containing the Open Accounts data.
Jonathan
thePriest wrote:
> Hi all, hope you can help?
> I have created a system DSN in the usual way using the above ODBC tool
> (I am trying to create a connection to our Open Accounts package using
> the OpenEdge 10.1A driver). I provide the host name, the port number
> and the database name, the user ID and password and test the
> connection. All works a treat...'Connection established'.
> I open up Excel and try to import data using my newly created DSN...it
> exposes all of the tables within the database and lets me select
> fields from those table. When I get to the 'return data to Microsoft
> Excel' part I get the following error message 'Access denied
> (Authorisation failed) (7512)'.
> Any ideas guys?
> Regards
> Paul
>

ODBC Creation from commandline

I am trying to get an ODBC connection created from a commandline, I
have created the ODBC connection, exported the registry key, and have
tried doing regedit /s "regkey", problem is after I do that it never
shows us in the ODBC manager, it shows up in the registry under
ODBC.INI\Source, but I dont see it in the manager, any ideas?
"Duane Haas" <techsupport@.suduhaas.com> wrote in message
news:uUTjiBQcFHA.2212@.TK2MSFTNGP14.phx.gbl...
>I am trying to get an ODBC connection created from a commandline, I
> have created the ODBC connection, exported the registry key, and have
> tried doing regedit /s "regkey", problem is after I do that it never
> shows us in the ODBC manager, it shows up in the registry under
> ODBC.INI\Source, but I dont see it in the manager, any ideas?
Are you adding a value to the ODBC Data Sources key as well as the database
connection key values?
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95
|||dbmonitor wrote:

> "Duane Haas" <techsupport@.suduhaas.com> wrote in message
> news:uUTjiBQcFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Are you adding a value to the ODBC Data Sources key as well as the
> database connection key values?
Yes, it creates the key, and all the values. I just export it from
another machine that already has the connection.
|||"Duane Haas" <techsupport@.suduhaas.com> wrote in message
news:uUMF6ZndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> dbmonitor wrote:
>
> Yes, it creates the key, and all the values. I just export it from
> another machine that already has the connection.
Are you able to post the reg settings you are adding?
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95

ODBC Creation from commandline

I am trying to get an ODBC connection created from a commandline, I
have created the ODBC connection, exported the registry key, and have
tried doing regedit /s "regkey", problem is after I do that it never
shows us in the ODBC manager, it shows up in the registry under
ODBC.INI\Source, but I dont see it in the manager, any ideas?"Duane Haas" <techsupport@.suduhaas.com> wrote in message
news:uUTjiBQcFHA.2212@.TK2MSFTNGP14.phx.gbl...
>I am trying to get an ODBC connection created from a commandline, I
> have created the ODBC connection, exported the registry key, and have
> tried doing regedit /s "regkey", problem is after I do that it never
> shows us in the ODBC manager, it shows up in the registry under
> ODBC.INI\Source, but I dont see it in the manager, any ideas?
Are you adding a value to the ODBC Data Sources key as well as the database
connection key values?
--
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95|||dbmonitor wrote:

> "Duane Haas" <techsupport@.suduhaas.com> wrote in message
> news:uUTjiBQcFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Are you adding a value to the ODBC Data Sources key as well as the
> database connection key values?
Yes, it creates the key, and all the values. I just export it from
another machine that already has the connection.|||"Duane Haas" <techsupport@.suduhaas.com> wrote in message
news:uUMF6ZndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> dbmonitor wrote:
>
> Yes, it creates the key, and all the values. I just export it from
> another machine that already has the connection.
Are you able to post the reg settings you are adding?
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95

ODBC Connectivity problem in Sql Server2005

Hi,

I have created DSN connection with sql server authentication in Sql server 2005. There is message comes "Connection Succeeded".

After I try through my application again asking username and password for the DSN at run time. My application is developed in Powerbuilder 5.0.

The connection string as follows,

SQLCA.dbms= "ODBC"
SQLCA.dBparm = "CONNECTSTRING='DSN=ntm',APPNAME = 'ntm',uid='sa',pwd='sierra'"

Can you help me?

I'm not really sure what Powerbuilder syntax is, but did you try something like:
SQLCA.dBparm ="CONNECTSTRING='DSN=ntm;uid=sa;pwd=sierra'"|||

Thanks for your kind reply..

We have rectified that problem.

sql

ODBC Connectivity problem in Sql Server2005

Hi,

I have created DSN connection with sql server authentication in Sql server 2005. There is message comes "Connection Succeeded".

After I try through my application again asking username and password for the DSN at run time. My application is developed in Powerbuilder 5.0.

The connection string as follows,

SQLCA.dbms= "ODBC"
SQLCA.dBparm = "CONNECTSTRING='DSN=ntm',APPNAME = 'ntm',uid='sa',pwd='sierra'"

Can you help me?

I'm not really sure what Powerbuilder syntax is, but did you try something like:
SQLCA.dBparm ="CONNECTSTRING='DSN=ntm;uid=sa;pwd=sierra'"|||

Thanks for your kind reply..

We have rectified that problem.

ODBC connection with stored procedure on iSeries as data source

I have created a stored procedure on the iSeries that creates a cursor and opens it. I am trying to write my report to use the stored procedure. I cannot get the data source to work. How do I create my data source so that it uses the stored procedure? My SP has three parameters I am trying to pass from the report. The parms are created in the layout.

Thank you

Figured it out. Had to use this syntax: Call procname ('parm1', parm2, 'parm3')

|||

I was looking for an answer to my stored procedure problem and I came across your question/answer...I was hoping you'd help me out...we are using ODBC and an iseries and since I'm very much of a newbie to VB and .net, I am using point and click when I can...can you tell me what I'm doing wrong? I have an asp.net master and content pages...the content pages have datagrids (Gridview1) and a SQLDataSource control...Any help is greatly appreciated!

Imports IBM.Data.DB2.iSeries

Partial Class SSNName

Inherits System.Web.UI.Page

Dim ssnpsn As String = "123456789"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim conn As New iDB2Connection("DataSource=datasourcename")

Try

conn.Open()

Catch ex As Exception

End Try

Dim sqlSSN As String = "call libraryname.SP_QSSNNAME(@.ssn)"

Dim cmdSSN As New iDB2Command(sqlSSN, conn)

cmdSSN.Parameters.Add("@.ssn", iDB2DbType.iDB2Numeric, 9)

cmdSSN.Parameters("@.ssn").Value = ssnpsn

Dim dr As iDB2DataReader

dr = cmdSSN.ExecuteReader()

Gridview1.DataSource = dr

Gridview1.DataBind()

conn.Close()

End Sub

End Class

|||

I have been using the iSeries for some time now and I find this approach the best.

1. Create a new class under App_code

2. Create a function with the follwing code:

Shared Function GetCatByCatID(ByVal CatID As Integer) As System.Data.DataSet

Dim connectionString As String = ConfigurationManager.ConnectionStrings("gsafleetcs").ConnectionString

Dim cnn As IBM.Data.DB2.iSeries.iDB2Connection = New IBM.Data.DB2.iSeries.iDB2Connection(connectionString)

Dim proc As String = "GetCatInfo"

Dim dbCmd As IBM.Data.DB2.iSeries.iDB2Command = New IBM.Data.DB2.iSeries.iDB2Command

dbCmd.CommandText = proc

dbCmd.CommandType = Data.CommandType.StoredProcedure

dbCmd.Connection = cnn

Dim dbParam_ID As System.Data.IDataParameter = New IBM.Data.DB2.iSeries.iDB2Parameter

dbParam_ID.ParameterName = "@.CatID"

dbParam_ID.Value = CatID

dbParam_ID.DbType = Data.DbType.Int32

dbParam_ID.Direction = Data.ParameterDirection.Input

dbCmd.Parameters.Add(dbParam_ID)

Dim dataAdapter As IBM.Data.DB2.iSeries.iDB2DataAdapter = New IBM.Data.DB2.iSeries.iDB2DataAdapter

Dim dataSet As System.Data.DataSet = New System.Data.DataSet

dataAdapter.SelectCommand = dbCmd

dataAdapter.Fill(dataSet)

dbCmd.Dispose()

cnn.Close()

Return dataSet

End Function

3. Then in your asp page where the grid view create a Object datasource, unclick the Show only data components to expose you class name, then click next and choose you approiate method and finally define your parameter(s).

These Rebooks helped a lot:

1. IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries

2. Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET

Good Luck

|||

Are you using the Report Designer?

In my Dataset definition I've defined my Name, Data Source, Command Type as 'Stored Procedure' and in the Query String I put the name of the stored procedure. When I execute it responds with

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Object reference not set to an instance of an object.

ADDITIONAL INFORMATION:

Object reference not set to an instance of an object. (System.Data)

BUTTONS:

OK

I know it finds the store procedure, becuase at first I had a typo and the error message stated it could not find it. When I fix the typo I recieved the above error message. Here is the stored procedure:

BEGIN
DECLARE C1 CURSOR FOR
SELECT PEMS . EMS1548 . EMSDCO , PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT , PEMS . FUELDET . FUDTYP , PEMS . FUELDET . FUDFQ AS GALS , PEMS . FUELDET . FUDFD AS DOLS , PEMS . FUELDET . FUDDAT
FROM PEMS . EMS1548 , PEMS . EMS1547 , PEMS . FUELDET
WHERE PEMS . EMS1548 . EMSDCO = PEMS . EMS1547 . EMSDPT AND PEMS . EMS1547 . EMSIDC = PEMS . FUELDET . FUDIDX AND
( PEMS . FUELDET . FUDTYP IN ( 'UNL' , 'DSL' ) ) AND ( PEMS . FUELDET . FUDEQT IN ( '022406' , '016460' , '026352' , '017068' , '023207' , '021870' , '019334' , '022754' , '020608' , '019337' ) )
ORDER BY PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT ;
OPEN C1 ;
END

Any ideas would be appreciated.

|||

I found the solution:

I was missing "RESULT SET 1" before the LANGUAGE SQL and "SET RESULT SETS CURSOR C1;" after the OPEN C1; statement

ODBC connection with stored procedure on iSeries as data source

I have created a stored procedure on the iSeries that creates a cursor and opens it. I am trying to write my report to use the stored procedure. I cannot get the data source to work. How do I create my data source so that it uses the stored procedure? My SP has three parameters I am trying to pass from the report. The parms are created in the layout.

Thank you

Figured it out. Had to use this syntax: Call procname ('parm1', parm2, 'parm3')

|||

I was looking for an answer to my stored procedure problem and I came across your question/answer...I was hoping you'd help me out...we are using ODBC and an iseries and since I'm very much of a newbie to VB and .net, I am using point and click when I can...can you tell me what I'm doing wrong? I have an asp.net master and content pages...the content pages have datagrids (Gridview1) and a SQLDataSource control...Any help is greatly appreciated!

Imports IBM.Data.DB2.iSeries

Partial Class SSNName

Inherits System.Web.UI.Page

Dim ssnpsn As String = "123456789"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim conn As New iDB2Connection("DataSource=datasourcename")

Try

conn.Open()

Catch ex As Exception

End Try

Dim sqlSSN As String = "call libraryname.SP_QSSNNAME(@.ssn)"

Dim cmdSSN As New iDB2Command(sqlSSN, conn)

cmdSSN.Parameters.Add("@.ssn", iDB2DbType.iDB2Numeric, 9)

cmdSSN.Parameters("@.ssn").Value = ssnpsn

Dim dr As iDB2DataReader

dr = cmdSSN.ExecuteReader()

Gridview1.DataSource = dr

Gridview1.DataBind()

conn.Close()

End Sub

End Class

|||

I have been using the iSeries for some time now and I find this approach the best.

1. Create a new class under App_code

2. Create a function with the follwing code:

Shared Function GetCatByCatID(ByVal CatID As Integer) As System.Data.DataSet

Dim connectionString As String = ConfigurationManager.ConnectionStrings("gsafleetcs").ConnectionString

Dim cnn As IBM.Data.DB2.iSeries.iDB2Connection = New IBM.Data.DB2.iSeries.iDB2Connection(connectionString)

Dim proc As String = "GetCatInfo"

Dim dbCmd As IBM.Data.DB2.iSeries.iDB2Command = New IBM.Data.DB2.iSeries.iDB2Command

dbCmd.CommandText = proc

dbCmd.CommandType = Data.CommandType.StoredProcedure

dbCmd.Connection = cnn

Dim dbParam_ID As System.Data.IDataParameter = New IBM.Data.DB2.iSeries.iDB2Parameter

dbParam_ID.ParameterName = "@.CatID"

dbParam_ID.Value = CatID

dbParam_ID.DbType = Data.DbType.Int32

dbParam_ID.Direction = Data.ParameterDirection.Input

dbCmd.Parameters.Add(dbParam_ID)

Dim dataAdapter As IBM.Data.DB2.iSeries.iDB2DataAdapter = New IBM.Data.DB2.iSeries.iDB2DataAdapter

Dim dataSet As System.Data.DataSet = New System.Data.DataSet

dataAdapter.SelectCommand = dbCmd

dataAdapter.Fill(dataSet)

dbCmd.Dispose()

cnn.Close()

Return dataSet

End Function

3. Then in your asp page where the grid view create a Object datasource, unclick the Show only data components to expose you class name, then click next and choose you approiate method and finally define your parameter(s).

These Rebooks helped a lot:

1. IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries

2. Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET

Good Luck

|||

Are you using the Report Designer?

In my Dataset definition I've defined my Name, Data Source, Command Type as 'Stored Procedure' and in the Query String I put the name of the stored procedure. When I execute it responds with

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Object reference not set to an instance of an object.

ADDITIONAL INFORMATION:

Object reference not set to an instance of an object. (System.Data)

BUTTONS:

OK

I know it finds the store procedure, becuase at first I had a typo and the error message stated it could not find it. When I fix the typo I recieved the above error message. Here is the stored procedure:

BEGIN
DECLARE C1 CURSOR FOR
SELECT PEMS . EMS1548 . EMSDCO , PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT , PEMS . FUELDET . FUDTYP , PEMS . FUELDET . FUDFQ AS GALS , PEMS . FUELDET . FUDFD AS DOLS , PEMS . FUELDET . FUDDAT
FROM PEMS . EMS1548 , PEMS . EMS1547 , PEMS . FUELDET
WHERE PEMS . EMS1548 . EMSDCO = PEMS . EMS1547 . EMSDPT AND PEMS . EMS1547 . EMSIDC = PEMS . FUELDET . FUDIDX AND
( PEMS . FUELDET . FUDTYP IN ( 'UNL' , 'DSL' ) ) AND ( PEMS . FUELDET . FUDEQT IN ( '022406' , '016460' , '026352' , '017068' , '023207' , '021870' , '019334' , '022754' , '020608' , '019337' ) )
ORDER BY PEMS . EMS1548 . EMSDDE , PEMS . FUELDET . FUDEQT ;
OPEN C1 ;
END

Any ideas would be appreciated.

|||

I found the solution:

I was missing "RESULT SET 1" before the LANGUAGE SQL and "SET RESULT SETS CURSOR C1;" after the OPEN C1; statement

ODBC Connection to Excel 2007

Hello everyone,

Since I would like to use an Excel 2007 File (*.xlsx) as Data Source, I created an ODBC Connection. It worked fine so far: the connection is established, I get the data as expected in the "Query Designer tab" and I could insert the fields in my report. However, when I want to see the result on the "Preview tab", I get an error saying that the "Report Definition '/myReport' is not valid...".

I don't know what could be wrong in my Report... Am I missing something in the ODBC configuration?

My Connection String is as follows:
Dsn=pl_excel;dbq=C:\Public\PL_DataSource.xlsx;defaultdir=C:\Public;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

I also tried to create an OLE DB connection but I got the same error...

Thanks for your help, I'm waiting for your suggestions...
Did anyone have the same issue?
|||

Hi

With OLEDB it works fine ...

Test That with OLEDB:

OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<your DB>;Server=<your Server>;

Test taht with ODBC:

ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;

that should work

ODBC Connection to Excel 2007

Hello everyone,

Since I would like to use an Excel 2007 File (*.xlsx) as Data Source, I created an ODBC Connection. It worked fine so far: the connection is established, I get the data as expected in the "Query Designer tab" and I could insert the fields in my report. However, when I want to see the result on the "Preview tab", I get an error saying that the "Report Definition '/myReport' is not valid...".

I don't know what could be wrong in my Report... Am I missing something in the ODBC configuration?

My Connection String is as follows:
Dsn=pl_excel;dbq=C:\Public\PL_DataSource.xlsx;defaultdir=C:\Public;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

I also tried to create an OLE DB connection but I got the same error...

Thanks for your help, I'm waiting for your suggestions...
Did anyone have the same issue?
|||

Hi

With OLEDB it works fine ...

Test That with OLEDB:

OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<your DB>;Server=<your Server>;

Test taht with ODBC:

ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;

that should work

ODBC Connection Problem

Hello,
I have an install of SQL that my users cannot connect to with an ODBC
connection. I have created Windows User logins on the SQL server through
Enterprise Manager, and have assigned them access to one specific database.
The specific user accounts have public, db_datareader and db_datawriter
permissions to the database. When the users attemtpt to create their ODBC
connections they receive an access denied message. What other permissions do
I need to give these users? Do they need any other permissions to the actual
server the SQL instance resides on?
Thanks,
JBaileyJBailey,
Please let me know the following:
- These accounts that you created to access that datbase, can they connect
and query the database from query analyser?
- Also, could you please created a UDL to test for connectivity (this gives
us a good idea if the odbc connection is ok). Also it could be that we
arent passing the correct 'connection string'?
Create a text file and name it <anything>.UDL. Rt-click the UDL file and
select Properties. The Connection tab contains all the fields exposed to
the application, and you choose the appropriate items to be filled in to
successfully "Test Connection" before you are able to use it. Once the Test
button works, click on OK and open up the UDL file in Notepad. Its just a
text file, and contains a provider string that you should be able to paste
into your application or whichever application is calling the sql server.
Cheers!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql

ODBC connection for client application to SQL Server 2005 Express installed on network computer

Hi All,

I've developed an application that connects to a SQL Server 2005 Express database. I created a DSN to connect to the database through ODBC. Currently, I am testing locally and everything works fine.

I would now like to install my application on another workstation and connect remotely to the database located on my development machine.

The client workstation does not have SQL Server 2005 Express installed on it because I would just like my application to connect remotely by creating the DSN and using ODBC. What I'm missing here are the database drivers. The "SQL Natice Client" is not available on this client workstation. How can I deploy the necessary drivers with my installation file so that I may create the required DSN name using the SQL Native Client driver?

Thanks!
Deployment of the SNAC can be found here:

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

If you don′t use the new features of SQL Server 2005, you can also use the MDAC driver which is compatible with the Pre SQL 2005 features of SQL Server 2005.

My best practice is not to use the DSN rather than using direct connection strings which can be configured in configuration files.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||Thanks for the reply.

I can't seem to find the sqlncli.msi file on my drive. I have the Express edition installed. Is there somewhere else I can find this installer for the SNAC driver?

If not, which version of MDAC do I need, and how would I build my DSN? I agree with using connection strings instead of DSN's, unfortunately that is a decision I'm unable to change.

|||

SNAC: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc
it

Just get the newest MDAC, since it makes no difference if you get 2.7 or 2.8 but getting the newest will keep you away from old bugs :-)

The connectionstrings can be found on www.connectionstring.com (Where I also posted an example for the SNAC one :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Excellent,

I got it working using MDAC.

Thanks!

odbc connection call failed

I moved a sql 2000 database to a sql 2005 server. I have a front end in access 2003. I manually created an ODBC data source system DSN using the ODBC Data Source Administrator just like it was on the other server. I can connect fine but users are saying their getting an odbc call fail. I created the connection and relinked the tables to the new server with the moved database. I completed the same task with another database and the user can connect fine. What could be the problem?

You will have to look at the additional information of the ODBC call / error message. I guess they are not priviledged to access the database or even connct to the server.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Odbc and deployment

I've created several reports, using ODBC to an external data source, and they
preview just fine in Visual Studio 2003. However, when I deploy the reports,
open Report Manager, and select a report to run I get:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection) Get Online Help
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found
and no default driver specified
I'm wondering why the datasource doesn't work on deployment (the Odbc driver
is a "User" dsn). Everything is on my "localhost" machine.
Any help is appreciated. Thanks,
BillReport Services user is not you. Make your ODBC dsn a machine dsn and your
problem will go away.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bill H" <BillH@.discussions.microsoft.com> wrote in message
news:1342D1A4-6D41-4BAD-A165-451130EB714A@.microsoft.com...
> I've created several reports, using ODBC to an external data source, and
> they
> preview just fine in Visual Studio 2003. However, when I deploy the
> reports,
> open Report Manager, and select a report to run I get:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'DataSource1'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found
> and no default driver specified
> I'm wondering why the datasource doesn't work on deployment (the Odbc
> driver
> is a "User" dsn). Everything is on my "localhost" machine.
> Any help is appreciated. Thanks,
> Bill|||I have the same problem. My dsn is a system dsn but it still won't work when
I deploy... same error message as described below. Any other ideas? Is it
anything to do with the windows accounts used by the relevant IIS folders?
Rob
"Bruce L-C [MVP]" wrote:
> Report Services user is not you. Make your ODBC dsn a machine dsn and your
> problem will go away.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Bill H" <BillH@.discussions.microsoft.com> wrote in message
> news:1342D1A4-6D41-4BAD-A165-451130EB714A@.microsoft.com...
> > I've created several reports, using ODBC to an external data source, and
> > they
> > preview just fine in Visual Studio 2003. However, when I deploy the
> > reports,
> > open Report Manager, and select a report to run I get:
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'DataSource1'.
> > (rsErrorOpeningConnection) Get Online Help
> > ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found
> > and no default driver specified
> >
> > I'm wondering why the datasource doesn't work on deployment (the Odbc
> > driver
> > is a "User" dsn). Everything is on my "localhost" machine.
> >
> > Any help is appreciated. Thanks,
> >
> > Bill
>
>|||Check out the credentials tab of your data source.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rob Sykes" <RobSykes@.discussions.microsoft.com> wrote in message
news:255CDDC7-811D-4DDF-B963-4391BE127640@.microsoft.com...
>I have the same problem. My dsn is a system dsn but it still won't work
>when
> I deploy... same error message as described below. Any other ideas? Is
> it
> anything to do with the windows accounts used by the relevant IIS folders?
> Rob
> "Bruce L-C [MVP]" wrote:
>> Report Services user is not you. Make your ODBC dsn a machine dsn and
>> your
>> problem will go away.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Bill H" <BillH@.discussions.microsoft.com> wrote in message
>> news:1342D1A4-6D41-4BAD-A165-451130EB714A@.microsoft.com...
>> > I've created several reports, using ODBC to an external data source,
>> > and
>> > they
>> > preview just fine in Visual Studio 2003. However, when I deploy the
>> > reports,
>> > open Report Manager, and select a report to run I get:
>> >
>> > An error has occurred during report processing. (rsProcessingAborted)
>> > Get
>> > Online Help
>> > Cannot create a connection to data source 'DataSource1'.
>> > (rsErrorOpeningConnection) Get Online Help
>> > ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not
>> > found
>> > and no default driver specified
>> >
>> > I'm wondering why the datasource doesn't work on deployment (the Odbc
>> > driver
>> > is a "User" dsn). Everything is on my "localhost" machine.
>> >
>> > Any help is appreciated. Thanks,
>> >
>> > Bill
>>

Obtain the last NEWSEQUENTIALID()

I'm using sql server 2005, I created a customer table and set the customer_id as an uniqueidentifier that is a NEWSEQUENTIALID()...when a user is created I want to obtain the last customer_id so that I can insert it in another table that has a one to many relationship with the customer table. Please help...how do i do this?.

Thanx

YOu can issue this query after you insert: SELECT scope_identity();

and it will give you back your last inserted record. Take a look at this link for more details.

http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

|||I thought the SELECT scope_identity() on worked for identity fields does it also work for uniqueidentifier fields?|||

bdotjones:

I thought the SELECT scope_identity() on worked for identity fields does it also work for uniqueidentifier fields?

Try this for more info. Hope this helps.

http://sqljunkies.com/Article/4067A1B1-C31C-4EAF-86C3-80513451FC03.scuk