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

Occasional Very Slow Performance.. with SQL Server 2000

Hi I am using JDB with SQL Server to call Stored Procedures..
The problem is that it is working fine for some time.. But after a
certain period the time taken to execute rises to around 15 to 50 secs
from 1 sec .After restart it again starts working fine...
can anyone tell me the reason for this behaviortry to update the statistics of your tables or defrag your indexes
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>|||Two things to check for:
See if you get different execution plans
See if you have any blocking
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>

Occasional Very Slow Performance.. with SQL Server 2000

Hi I am using JDB with SQL Server to call Stored Procedures..
The problem is that it is working fine for some time.. But after a
certain period the time taken to execute rises to around 15 to 50 secs
from 1 sec .After restart it again starts working fine...
can anyone tell me the reason for this behaviortry to update the statistics of your tables or defrag your indexes
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>|||Two things to check for:
See if you get different execution plans
See if you have any blocking
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>

Occasional Error When Executing CLR Stored Procedure

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:

<code>

Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select

End If

End While

End If

End If

Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)

Finally

' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If

End Try

End Sub

</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).

It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.

Steven
|||

Understood. Thanks for the explanation.

So all I have to do is make everything local, non-shared then right?

Except for the main sub procedure declaration below which has to remain shared right?:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _

Am I on the right track?

|||

Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.

|||

Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.

Occasional Error When Executing CLR Stored Procedure

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:

<code>

Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select

End If

End While

End If

End If

Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)

Finally

' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If

End Try

End Sub

</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).

It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.

Steven
|||

Understood. Thanks for the explanation.

So all I have to do is make everything local, non-shared then right?

Except for the main sub procedure declaration below which has to remain shared right?:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _

Am I on the right track?

|||

Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.

|||

Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.

Occasional "Timeout expired" message - on SP that should take 1 second

Hi there,
I've got something kind of weird going on. I have a stored procedure that
normally takes less than one second to execute, but it has started taking
over 30 seconds from time to time.
The SP is called by an ASP page on a web server, running on a separate
machine. The database server is running SQL Server 2000. The ASP page
makes an ODBC connection, using TCP/IP and creates & sends the stored
procedure call.
From time to time, something goes haywire, and the ASP page returns
[Microsoft][ODBC SQL Server Driver]Timeout expired
Then, the ASP page will not work no matter what we do, even if we leave it a
day or two. (No locks on the database, sometimes not even any connections!)
The only way to fix it is to run the stored procedure through Query
Analyzer. It will take a little over 30 seconds, but finish successfully.
Then, if I run the exact same SP again, it will complete in 0 seconds, and
the web page will work fine again.
I've seen this before, but I cannot for the life of me remember what we did
to fix it. We tried changing the ODBC connection to Named Pipes instead of
TCP/IP but that didn't help.
Any suggestions?
BeverleyI am having the same issue and the solution provided by SQL crafter in the S
ql Server Timeout expired post is not working. Could someone help us with t
his issue?
Thanks,
Rick|||I have been struggling with a similar problem for a couple of weeks and came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes, ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- Fred
"Beverley" <ali_webitems@.hotmail.com> wrote in message
news:OpxbH$K9DHA.712@.tk2msftngp13.phx.gbl...
> Hi there,
> I've got something kind of weird going on. I have a stored procedure that
> normally takes less than one second to execute, but it has started taking
> over 30 seconds from time to time.
> The SP is called by an ASP page on a web server, running on a separate
> machine. The database server is running SQL Server 2000. The ASP page
> makes an ODBC connection, using TCP/IP and creates & sends the stored
> procedure call.
> From time to time, something goes haywire, and the ASP page returns
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Then, the ASP page will not work no matter what we do, even if we leave it
a
> day or two. (No locks on the database, sometimes not even any
connections!)
> The only way to fix it is to run the stored procedure through Query
> Analyzer. It will take a little over 30 seconds, but finish successfully.
> Then, if I run the exact same SP again, it will complete in 0 seconds, and
> the web page will work fine again.
> I've seen this before, but I cannot for the life of me remember what we
did
> to fix it. We tried changing the ODBC connection to Named Pipes instead
of
> TCP/IP but that didn't help.
> Any suggestions?
> Beverley
>|||I have been struggling with a similar problem for a couple of weeks and
came
to this group in search of a solution. Perhaps a pooling of information
will help.
In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in
service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe -- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes,
ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:
The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.
I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.
So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.
- ITFred|||ITFRED, have you found a solution to your problem at all'
I have a similar problem, I also get the *occasional timeout*.
Mine is an ASP web application connected to MSDE, and the application
gets a timeout error occasionally when the ASP code tries to update
many records in a table, but smaller tables work fine.
The error message is:
> Error Number -214xxxxxx, ODBC SQL Server Driver timeout expired.
In a testing environment on a dev server, the same ASP application
connects to a SQL2K database and it has NOT encountered any problems.
My conclusion is (thanks to all your posts I have read) that it is in
ODBC connections where the MSDE engine times out.
Can anyone please come up with a solution!'
Is using a different database an option, perhaps mysql or postgreSQL?
Thanks.
huge
ITFred wrote:
> *I have been struggling with a similar problem for a couple of weeks
> and
> came
> to this group in search of a solution. Perhaps a pooling of
> information
> will help.
> In my situation, I have a VB interactive front-end on multiple
> machines
> accessing a SQL2K database through ODBC over TCP/IP. It has been in
> service
> for over 2 years and the master table now has over 750K records in
> it.
> Recently we started receiving occasional "Timeout Expired" errors in
> pretty
> much the same manner as you describe -- a query that would normally
> take
> 1-2 seconds would suddenly take over 90 and crap out. I ran
> database
> integrity checks, reconstructed the indexes, created additional
> indexes,
> ran
> numerous original and reconfigured queries through the Index
> Analyzer. And
> of course spent hours trying various searches in the MS KB. I have
> reached
> the following conclusions:
> The problem (in my case, at least) originates with the Query
> Optimizer. A
> very specific query run through the Query Analyzer will *always*
> select an
> appropriate index or mix of indexes. The *identical* query
> submitted
> through ODBC, however, will sometimes select a completely
> inappropriate
> index mix, or no index at all, wind up executing full-table scans or
> other
> time-wasting substitutes, and time out. This effect, I suspect, is
> a
> permanent feature of MSSQL, but does not manifest on smaller tables
> because
> the malfunction does not cause a timeout on smaller tables, just an
> inexcusable waste of time.
> I proved this theory by adding index hints to my ODBC-originated
> queries
> (which Books Online says should never be necessary), and observing
> the
> performance stabilize. Problem is, I consider this a completely
> hokey
> solution since if I ever decide to reconfigure my indexes, I will
> have
> dozens of coordinating code changes to perform. Also, there are
> several
> queries in which I cannot use index hints because the query requires
> column-level 'OR'-ing with which hints are incompatible.
> So I am still looking for a "proper" solution from one of you out
> there, or
> at least an acknowledgment from MS that this is a behavioral issue
> with
> SQL2K that needs to be addressed.
> - ITFred *
huge
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message397556.html

obtaining the ip address of connection

any one know how the get the ip address of a source connection to my sql
server using a query/stored procedure/ex stored procedure etc.
essentially to ip address of the "host" process
thanksI don't if IP is possible, but system_user returns the Windows
Authentication domain\user, host_name() returns the workstation name, and
host_id() returns workstation id.
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OeeW$6ijFHA.4000@.TK2MSFTNGP12.phx.gbl...
> any one know how the get the ip address of a source connection to my sql
> server using a query/stored procedure/ex stored procedure etc.
> essentially to ip address of the "host" process
> thanks
>|||Note that you can't always derive an IP address from a host name, but in an
enclosed environment, this might work for you:
declare @.cmd varchar(255)
set @.cmd = 'ping '+HOST_NAME()
create table #foo (l VARCHAR(8000))
set nocount on
insert #foo exec master..xp_cmdshell @.cmd
select substring
(
l,
charindex('[', l)+1,
charindex(']', l)-charindex('[',l)-1
)
from #foo
WHERE l like 'Pinging%'
drop table #foo
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OeeW$6ijFHA.4000@.TK2MSFTNGP12.phx.gbl...
> any one know how the get the ip address of a source connection to my sql
> server using a query/stored procedure/ex stored procedure etc.
> essentially to ip address of the "host" process
> thanks
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Note that you can't always derive an IP address from a host name, but
> in an enclosed environment, this might work for you:
>
> declare @.cmd varchar(255)
> set @.cmd = 'ping '+HOST_NAME()
> create table #foo (l VARCHAR(8000))
> set nocount on
> insert #foo exec master..xp_cmdshell @.cmd
> select substring
> (
> l,
> charindex('[', l)+1,
> charindex(']', l)-charindex('[',l)-1
> )
> from #foo
> WHERE l like 'Pinging%'
> drop table #foo
>
Good idea. You'll get better performance if you limit the number of echo
requests to 1 with the -n parameter:
declare @.c nvarchar(255)
set @.c = 'ping ' + host_name() + ' -n 1'
exec master..xp_cmdshell @.c
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> Good idea. You'll get better performance if you limit the number of echo
> requests to 1 with the -n parameter:
Good catch. Though I have to wonder if this is a one-time thing or if we
plan on putting this into production. =)|||thanks,
the problem is I want the closed environment to be able to access, but
certain connections from the firewall not to be able to. I have replication
coming in over the firewall (connection I want), but do not want someone
just trying to launch the app (unwanted connection) from across the
internet, if I knew the ip of the connection this could be accomplished, but
it looks like so far it can't be done.........
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:erKHYNjjFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Aaron Bertrand [SQL Server MVP] wrote:
> Good idea. You'll get better performance if you limit the number of echo
> requests to 1 with the -n parameter:
> declare @.c nvarchar(255)
> set @.c = 'ping ' + host_name() + ' -n 1'
> exec master..xp_cmdshell @.c
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Assuming Windows Authentication, to verify a that a connection is internal,
all you need is to check the host name of the connection. Also, system_user
will return the domain\user like: Cerebrus\jturner
Is resolving to a specific IP really necessary?
Also change the default listening port, remove support for unneeded
protocols, etc.
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:OpUktQjjFHA.1480@.TK2MSFTNGP10.phx.gbl...
> thanks,
> the problem is I want the closed environment to be able to access, but
> certain connections from the firewall not to be able to. I have
> replication coming in over the firewall (connection I want), but do not
> want someone just trying to launch the app (unwanted connection) from
> across the internet, if I knew the ip of the connection this could be
> accomplished, but it looks like so far it can't be done.........
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:erKHYNjjFHA.2152@.TK2MSFTNGP14.phx.gbl...
>|||So what is your protection doing inside the database? Don't you think
Windows (or another application) will be better at analyzing incoming
connections, trying to PREVENT your app from being called, than from the
database merely responding to the fact that your firewall has been breached
and the app has been successfully called?
Again, you seem to be protecting one morsel of cheese from the mice, but
letting them have their way with the rest of the fridge...
"Matthew Kempf" <mpkempf@.gmail.com> wrote in message
news:eBpkbZjjFHA.1048@.tk2msftngp13.phx.gbl...
> it doesn't
> i have satellites and i don't want to try to launch the program.... i
> just know they will try.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:evZZeWjjFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||the firewall is fine; the security is fine
i have an application that makes an encrypted connection to an sql server
located at a satellite. the satellite uses transactional replication over
ssl to push a copy of the database to the host so reporting can be run; so
the only connection that can be made to the sql server is that application
or if you know the sa password or application userid and password. the
problem is if someone launches that application from the satellite and makes
a connection to the firewall (not violating any security). what i'm
attempting to do is write the remote application to have sql server check to
see what ip address the application is connecting from. if the application
sees that it attempting a connection from the firewall ip address then the
app will shutdown. it needs to do this because it will break replication if
this occurs, and a snapshot merge of 24 8gb databases over the internet
sucks royally.
"Aaron Bertrand [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
news:%23$gVjdjjFHA.3336@.TK2MSFTNGP10.phx.gbl...
> So what is your protection doing inside the database? Don't you think
> Windows (or another application) will be better at analyzing incoming
> connections, trying to PREVENT your app from being called, than from the
> database merely responding to the fact that your firewall has been
> breached and the app has been successfully called?
> Again, you seem to be protecting one morsel of cheese from the mice, but
> letting them have their way with the rest of the fridge...
>
>
> "Matthew Kempf" <mpkempf@.gmail.com> wrote in message
> news:eBpkbZjjFHA.1048@.tk2msftngp13.phx.gbl...
>|||> see what ip address the application is connecting from. if the
> application sees that it attempting a connection from the firewall ip
> address then the app will shutdown.
Can you explain "the application"? Is this an EXE, a service, or ...?

obtaining return values from DBCC INDEXDEFRAG in a sp

Hi All,
I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number
of indexes.
I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages Removed). However I cannot seem to find a way to do this
Can anyone help please?
Thanks in advance
DanCreate a table with the same structure as the DBCC returns and then use below technique:
INSERT theTable
EXEC('DBCC...')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number of indexes.
> I wish to then manipulate the values from the result set (Pages Scanned, Pages Moved, Pages
> Removed). However I cannot seem to find a way to do this
> Can anyone help please?
> Thanks in advance
> Dan
>|||You can't do that as the insert/exec creates a user transaction and
INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
the results from INDEXDEFRAG except using an external text file.
Dan - what do you want to do with the values, out of interest?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Create a table with the same structure as the DBCC returns and then use
below technique:
> INSERT theTable
> EXEC('DBCC...')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> >
> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
number of indexes.
> >
> > I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages
> > Removed). However I cannot seem to find a way to do this
> >
> > Can anyone help please?
> >
> > Thanks in advance
> >
> > Dan
> >
>|||Ah, thanks Paul. I didn't consider the transaction aspect of it...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
>> Create a table with the same structure as the DBCC returns and then use
> below technique:
>> INSERT theTable
>> EXEC('DBCC...')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
>> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
>> > Hi All,
>> >
>> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> number of indexes.
>> >
>> > I wish to then manipulate the values from the result set (Pages Scanned,
> Pages Moved, Pages
>> > Removed). However I cannot seem to find a way to do this
>> >
>> > Can anyone help please?
>> >
>> > Thanks in advance
>> >
>> > Dan
>> >
>>
>|||Hi Paul,
I want to put them into a "User Friendly" report, that basically lists the
time, index defragged and the pages scanned, moved, etc.
Regards
Dan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to
> capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
>> Create a table with the same structure as the DBCC returns and then use
> below technique:
>> INSERT theTable
>> EXEC('DBCC...')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
>> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
>> > Hi All,
>> >
>> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> number of indexes.
>> >
>> > I wish to then manipulate the values from the result set (Pages
>> > Scanned,
> Pages Moved, Pages
>> > Removed). However I cannot seem to find a way to do this
>> >
>> > Can anyone help please?
>> >
>> > Thanks in advance
>> >
>> > Dan
>> >
>>
>|||I'm afraid the only way to do it is to output the results to a flat file and
then post-process.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uE0WCTFrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I want to put them into a "User Friendly" report, that basically lists the
> time, index defragged and the pages scanned, moved, etc.
> Regards
> Dan
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> > You can't do that as the insert/exec creates a user transaction and
> > INDEXDEFRAG cannot run inside a user transaction. There's no way to
> > capture
> > the results from INDEXDEFRAG except using an external text file.
> >
> > Dan - what do you want to do with the values, out of interest?
> >
> > Regards
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> >> Create a table with the same structure as the DBCC returns and then use
> > below technique:
> >>
> >> INSERT theTable
> >> EXEC('DBCC...')
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> >> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> >> > Hi All,
> >> >
> >> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> > number of indexes.
> >> >
> >> > I wish to then manipulate the values from the result set (Pages
> >> > Scanned,
> > Pages Moved, Pages
> >> > Removed). However I cannot seem to find a way to do this
> >> >
> >> > Can anyone help please?
> >> >
> >> > Thanks in advance
> >> >
> >> > Dan
> >> >
> >>
> >>
> >
> >
>

obtaining return values from DBCC INDEXDEFRAG in a sp

Hi All,
I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number
of indexes.
I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages Removed). However I cannot seem to find a way to do this
Can anyone help please?
Thanks in advance
Dan
Create a table with the same structure as the DBCC returns and then use below technique:
INSERT theTable
EXEC('DBCC...')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number of indexes.
> I wish to then manipulate the values from the result set (Pages Scanned, Pages Moved, Pages
> Removed). However I cannot seem to find a way to do this
> Can anyone help please?
> Thanks in advance
> Dan
>
|||You can't do that as the insert/exec creates a user transaction and
INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
the results from INDEXDEFRAG except using an external text file.
Dan - what do you want to do with the values, out of interest?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Create a table with the same structure as the DBCC returns and then use
below technique:[vbcol=seagreen]
> INSERT theTable
> EXEC('DBCC...')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
number of indexes.[vbcol=seagreen]
Pages Moved, Pages
>
|||Ah, thanks Paul. I didn't consider the transaction aspect of it...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> below technique:
> number of indexes.
> Pages Moved, Pages
>
|||Hi Paul,
I want to put them into a "User Friendly" report, that basically lists the
time, index defragged and the pages scanned, moved, etc.
Regards
Dan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to
> capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> below technique:
> number of indexes.
> Pages Moved, Pages
>
|||I'm afraid the only way to do it is to output the results to a flat file and
then post-process.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uE0WCTFrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I want to put them into a "User Friendly" report, that basically lists the
> time, index defragged and the pages scanned, moved, etc.
> Regards
> Dan
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
>

Obtaining Procedure Declaration

Where does SQL Server 2000 store the definition of stored procedures and
views? Is there a system table that I can query and retrieve the definition
,
like it shows up when scripting from the QA Object Browser?Kevin Bowker wrote:
> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the definiti
on,
> like it shows up when scripting from the QA Object Browser?
syscomments table contains full text of views, procedures and other objects.|||One of my colleagues apparently does better footwork than I do and came up
with this for those who may be interested:
select routine_definition
from information_schema.routines
where routine_type='Procedure'
"Kevin Bowker" wrote:

> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the definiti
on,
> like it shows up when scripting from the QA Object Browser?|||I would use sp_helptext in 2000 and previous. This will account for
procedures larger than 8K that span multiple rows in syscomments and
INFORMATION_SCHEMA.ROUTINES.
In SQL Server 2005, you can use the new OBJECT_DEFINITION() function, or the
new catalog view sys.procedures
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:73D65E14-A411-4FD7-96FB-A85CAC02EF5F@.microsoft.com...
> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the
> definition,
> like it shows up when scripting from the QA Object Browser?

Obtaining data from an EXEC

Hi all,

I have a stored procedure which returns a recordset of data. What I want to do is execute this stored procedure in another stored procedure and insert the recordset returned into a temporary table.

ie.

Stored Procedure Test1 may perform the following action: -
SELECT * FROM MyTable1

Stored Procedure Test2 would do something like this: -

EXEC Test1

INSERT INTO #TempTable {The recordset returned from Test1}

Any ideas how I would go about this?

Cheers,

Robchange sp Test2 to:

insert into #TempTable
execute Test1|||Novice Brain Surgeon is an appropriate labal, you're a star.

Cheers,

Rob|||Actually, I hated the "Guru" label and was trying to be cute.

Obtain contents of stored procedures

Hi all,

I need to be able to obtain the full contents, i.e. sql statements
inside, of all procedures in SQl Server 2000. I am using MS type 4
JDBC driver. I know that from the database metadata, I could get the
names of all stored procedures and their column structures. However,
I did not find any thing that will help me to obtain contents of the
stored procedures. Any help would be very much appreciated. Thanks
:-)) Vien NguyenFor a single stored procedure you can use:

EXEC sp_helptext 'sp'

The code is stored in the text column of a system table called syscomments.
You can query this table directly (though not always recommended) or you can
use INFORMATION_SCHEMA.ROUTINES view. See SQL Server Books Online for more
details.

--
- Anith
( Please reply to newsgroups only )

ObsoletedSprocException is being thrown

I have run into a strange problem with SQL Server 2000 (SP4). I am
calling a fairly simple stored procedure that selects a set of data
from one table based on several parameters passed in (combination of
varchar and int).
The problem is that everytime I execute this procedure, SQL server
returns with:
Server: Msg 50000, Level 16, State 1, Procedure
dl_CARDEX_GetUsageHistory, Line 11
ObsoletedSprocException:dl_CARDEX_GetUsa
geHistory.
dl_CARDEX_GetUsageHistory is the name of my sproc.
I spliced out the select statement and it runs fine in Query Analyzer.
I googled for 'ObsoletedSprocException' and couldn't find a single
reference to it!
It's coming right from SQL Server as I get this message directly from
Query Analyzer when executing the sproc.
Any ideas? Very odd that no one has posted anything about this
exception before.
Thanks,"Tom" <tdietz@.gmail.com> wrote in message
news:1166111230.919365.237940@.l12g2000cwl.googlegroups.com...
>I have run into a strange problem with SQL Server 2000 (SP4). I am
> calling a fairly simple stored procedure that selects a set of data
> from one table based on several parameters passed in (combination of
> varchar and int).
> The problem is that everytime I execute this procedure, SQL server
> returns with:
>
> Server: Msg 50000, Level 16, State 1, Procedure
> dl_CARDEX_GetUsageHistory, Line 11
> ObsoletedSprocException:dl_CARDEX_GetUsa
geHistory.
> . . .
From BOL
When RAISERROR is used with the msg_id of a user-defined message in
sys.messages, msg_id is returned as the SQL Server error number, or native
error code. When RAISERROR is used with a msg_str instead of a msg_id, the
SQL Server error number and native error number returned is 50000.
http://msdn2.microsoft.com/en-us/library/ms177497.aspx
So what is on line 11 of the procedure dl_CARDEX_GetUsageHistory?
RAISERROR?
David

ObsoletedSprocException is being thrown

I have run into a strange problem with SQL Server 2000 (SP4). I am
calling a fairly simple stored procedure that selects a set of data
from one table based on several parameters passed in (combination of
varchar and int).
The problem is that everytime I execute this procedure, SQL server
returns with:
Server: Msg 50000, Level 16, State 1, Procedure
dl_CARDEX_GetUsageHistory, Line 11
ObsoletedSprocException:dl_CARDEX_GetUsageHistory.
dl_CARDEX_GetUsageHistory is the name of my sproc.
I spliced out the select statement and it runs fine in Query Analyzer.
I googled for 'ObsoletedSprocException' and couldn't find a single
reference to it!
It's coming right from SQL Server as I get this message directly from
Query Analyzer when executing the sproc.
Any ideas? Very odd that no one has posted anything about this
exception before.
Thanks,"Tom" <tdietz@.gmail.com> wrote in message
news:1166111230.919365.237940@.l12g2000cwl.googlegroups.com...
>I have run into a strange problem with SQL Server 2000 (SP4). I am
> calling a fairly simple stored procedure that selects a set of data
> from one table based on several parameters passed in (combination of
> varchar and int).
> The problem is that everytime I execute this procedure, SQL server
> returns with:
>
> Server: Msg 50000, Level 16, State 1, Procedure
> dl_CARDEX_GetUsageHistory, Line 11
> ObsoletedSprocException:dl_CARDEX_GetUsageHistory.
> . . .
From BOL
When RAISERROR is used with the msg_id of a user-defined message in
sys.messages, msg_id is returned as the SQL Server error number, or native
error code. When RAISERROR is used with a msg_str instead of a msg_id, the
SQL Server error number and native error number returned is 50000.
http://msdn2.microsoft.com/en-us/library/ms177497.aspx
So what is on line 11 of the procedure dl_CARDEX_GetUsageHistory?
RAISERROR?
David

ObsoletedSprocException is being thrown

I have run into a strange problem with SQL Server 2000 (SP4). I am
calling a fairly simple stored procedure that selects a set of data
from one table based on several parameters passed in (combination of
varchar and int).
The problem is that everytime I execute this procedure, SQL server
returns with:
Server: Msg 50000, Level 16, State 1, Procedure
dl_CARDEX_GetUsageHistory, Line 11
ObsoletedSprocException:dl_CARDEX_GetUsageHistory.
dl_CARDEX_GetUsageHistory is the name of my sproc.
I spliced out the select statement and it runs fine in Query Analyzer.
I googled for 'ObsoletedSprocException' and couldn't find a single
reference to it!
It's coming right from SQL Server as I get this message directly from
Query Analyzer when executing the sproc.
Any ideas? Very odd that no one has posted anything about this
exception before.
Thanks,
"Tom" <tdietz@.gmail.com> wrote in message
news:1166111230.919365.237940@.l12g2000cwl.googlegr oups.com...
>I have run into a strange problem with SQL Server 2000 (SP4). I am
> calling a fairly simple stored procedure that selects a set of data
> from one table based on several parameters passed in (combination of
> varchar and int).
> The problem is that everytime I execute this procedure, SQL server
> returns with:
>
> Server: Msg 50000, Level 16, State 1, Procedure
> dl_CARDEX_GetUsageHistory, Line 11
> ObsoletedSprocException:dl_CARDEX_GetUsageHistory.
> . . .
From BOL
When RAISERROR is used with the msg_id of a user-defined message in
sys.messages, msg_id is returned as the SQL Server error number, or native
error code. When RAISERROR is used with a msg_str instead of a msg_id, the
SQL Server error number and native error number returned is 50000.
http://msdn2.microsoft.com/en-us/library/ms177497.aspx
So what is on line 11 of the procedure dl_CARDEX_GetUsageHistory?
RAISERROR?
David

Obsolete technology - dblibrary

Hello!
In my application I need to use extended stored procedures.
Developing these extended stored procedures I use the "dblibrary APIs" to
access MS SQL Server 2000.
As far as I understand /reading articles in MSDN/, Microsoft declares its
intention not to support dblibrary in future.
Which is the recommended method to access MS SQL Server?
Thank you!
In order, the preferences are:
ADO.Net (but you have to be writing in VB.Net or C#)
ADO
OLE-DB
ODBC
SQL-DMO for controlling the server instead of manipulating the data.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>
|||I'd just tack on SQLXML/IIS onto the end of Geoff's list (in that order) as
well as this is a data access method which is growing in popularity and
might suit your development strategy, especially if you're in the web area\
or working with something like Biztalk.
Regards,
Greg Linwood
SQL Server MVP
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>
|||Just in case the other posters missed that you are developing extended proc's:
Forget about ADO.NET, as it is not supported to have SQL Server execute CLR code.
For this type of low-level programming, I have a feeling that ODBC or OLEDB is the best choice. www.sqldev.net
might have some comments about this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ggeshev" <ggeshev@.tonegan.bg> wrote in message news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>

Obsolete technology - dblibrary

Hello!
In my application I need to use extended stored procedures.
Developing these extended stored procedures I use the "dblibrary APIs" to
access MS SQL Server 2000.
As far as I understand /reading articles in MSDN/, Microsoft declares its
intention not to support dblibrary in future.
Which is the recommended method to access MS SQL Server?
Thank you!In order, the preferences are:
ADO.Net (but you have to be writing in VB.Net or C#)
ADO
OLE-DB
ODBC
SQL-DMO for controlling the server instead of manipulating the data.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>|||I'd just tack on SQLXML/IIS onto the end of Geoff's list (in that order) as
well as this is a data access method which is growing in popularity and
might suit your development strategy, especially if you're in the web area\
or working with something like Biztalk.
Regards,
Greg Linwood
SQL Server MVP
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>|||Just in case the other posters missed that you are developing extended proc's:
Forget about ADO.NET, as it is not supported to have SQL Server execute CLR code.
For this type of low-level programming, I have a feeling that ODBC or OLEDB is the best choice. www.sqldev.net
might have some comments about this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ggeshev" <ggeshev@.tonegan.bg> wrote in message news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>

Obsolete technology - dblibrary

Hello!
In my application I need to use extended stored procedures.
Developing these extended stored procedures I use the "dblibrary APIs" to
access MS SQL Server 2000.
As far as I understand /reading articles in MSDN/, Microsoft declares its
intention not to support dblibrary in future.
Which is the recommended method to access MS SQL Server?
Thank you!In order, the preferences are:
ADO.Net (but you have to be writing in VB.Net or C#)
ADO
OLE-DB
ODBC
SQL-DMO for controlling the server instead of manipulating the data.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>|||I'd just tack on SQLXML/IIS onto the end of Geoff's list (in that order) as
well as this is a data access method which is growing in popularity and
might suit your development strategy, especially if you're in the web area\
or working with something like Biztalk.
Regards,
Greg Linwood
SQL Server MVP
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>|||Just in case the other posters missed that you are developing extended proc'
s:
Forget about ADO.NET, as it is not supported to have SQL Server execute CLR
code.
For this type of low-level programming, I have a feeling that ODBC or OLEDB is the best
choice. www.sqldev.net
might have some comments about this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ggeshev" <ggeshev@.tonegan.bg> wrote in message news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl..
.
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>

ObjectDataSource not passing parameter to report viewer stored proc (sql server)

I have an ObjectDataSource feeding data to a report viewer control, but when I try to give the objectdatasource a parameter it should be expecting (based on the stored proc that's pulling the data) and the GetData method in the XSD file, I get the error:
An error has occurred during report processing.ObjectDataSource 'ObjectDataSource2' could not find a non-generic method 'GetData' that has parameters: Date.The table adapter in the XDS shows GetData(@.Date), and I'm trying to bind the objectdatasource to my report viewer like this:

PlaceHolder1.Controls.Clear()
Dim rv As New Microsoft.Reporting.WebForms.ReportViewer
PlaceHolder1.Controls.Add(rv)
rv.LocalReport.DataSources.Clear()
ObjectDataSource2.TypeName = "DS_ClosedProjectsSnap2TableAdapters.reports_closedin7days_2TableAdapter"
ObjectDataSource2.SelectParameters.Clear()
ObjectDataSource2.SelectParameters.Add("Date", d_collectiondate.SelectedValue)
ObjectDataSource2.DataBind()
rv.LocalReport.ReportPath = "secure\reports\ClosedProjectsSnap.rdlc"
rv.LocalReport.EnableHyperlinks = True
rv.LocalReport.DataSources.Clear()
rv.ProcessingMode = ProcessingMode.Local
rv.LocalReport.DataSources.Add(New ReportDataSource("DS_ClosedProjectsSnap2_reports_closedin7days_2", ObjectDataSource2))
rv.DataBind()

I can't see why this wouldn't work, so I would really appreciate any ideas anybody has.

Thanks

I used to have this error, but all I did to fix it was configure the datasource and make sure each paramter was assigned to textbox controls which i added above the ReportViewer.