SQL Alternative to cursor: method

Showing posts with label method. Show all posts

Sunday, March 11, 2012

An exception occurred during the DBComms.receive method.

I am getting SQLException when I connect SQL Server 2005. I would like to know how to correct this problem.

I get this error randomly. Never seenit before. Sometimes I get this error one day later from application begins, sometimes two days later, sometimes 3 day later.

DBCP borrowObject failed: com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context Sad 841) [Thread[http-8080-Processor18,5,main], IO:457d2, Dbc:1a642]. PktNum:0. TotalReceived:0. PktSize:4,096.
org.apache.commons.dbcp.DbcpException: com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context841) [Thread[http-8080-Processor18,5,main], IO:457d2, Dbc:1a642]. PktNum:0. TotalReceived:0. PktSize:4,096.
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:85)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:184)
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(Unknown Source)
at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:117)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:110)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:312)
at org.apache.struts.util.GenericDataSource.getConnection(GenericDataSource.java:276)
at com.y.util.DbAccess.getConnection(DbAccess.java:29)
at com.y.biz.UserMgr.leftDays(UserMgr.java:404)
at com.y.action.LoginAction.login(LoginAction.java:194)
at sun.reflect.GeneratedMethodAccessor303.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:220)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:446)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:266)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1292)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:510)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context841) [Thread[http-8080-Processor18,5,main], IO:457d2, Dbc:1a642]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.sendCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.changeSettings(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:83)
... 44 more

Web Server:

CPU 2*1.66G, 4GB ram, 146GB

Windows 2003 Server

JDK 1.4.2

Tomcat 5.0

Driver Name: Microsoft SQL Server 2005 JDBC Driver

JDBC Driver Version 1.0

Use struts connection pool in application.

Database Server:

CPU 2*1.66G, 4GB ram, 4 x 146GB

Windows 2003 Server

SQL Server 2005, Product Version: 9.00.1399

Hello,

Do you get similar failures with the newest JDBC driver?

The latest driver can be downloaded at the link below. I would try the v1.1 or the V1.2 CTP drivers.

http://msdn2.microsoft.com/en-us/data/aa937724.aspx

Thanks,

Jaaved

|||

Use JDBC 1.2 now, but the error is same:

DBCP borrowObject failed: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
org.apache.commons.dbcp.DbcpException: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:85)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:184)
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(Unknown Source)
at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:117)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:110)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:312)
at org.apache.struts.util.GenericDataSource.getConnection(GenericDataSource.java:276)
at com.y.util.DbAccess.getConnection(DbAccess.java:29)
at com.y.biz.AccessRightMgr.getAccessRight(AccessRightMgr.java:135)
at org.apache.jsp.web.customer_005flist_jsp._jspService(customer_005flist_jsp.java:148)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:696)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:474)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:409)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:312)
at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1014)
at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:417)
at org.apache.struts.action.RequestProcessor.processActionForward(RequestProcessor.java:390)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:271)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1292)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:510)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)

All is same, just remove "An exception occurred during the DBComms.receive method."

|||

Is the exception "Connection Reset" or "Connection Reset by Peer"?

This class of exception means that SQL Server is terminating the connection prematurely. It can have many causes so I'd urge you to look at both the SQL Server Profiler Traces and the JDBC Driver Diagnostic Logs and try to determine what was the operation immediately before the connection was terminated.

Here is how to enabled Logging in the JDBC driver:

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

Thanks,

Jaaved

An exception occurred during the DBComms.receive method.

I am getting SQLException when I connect SQL Server 2005. I would like to know how to correct this problem.

I get this error randomly. Never seenit before. Sometimes I get this error one day later from application begins, sometimes two days later, sometimes 3 day later.

Web Server:

CPU 2*1.66G, 4GB ram, 146GB

Windows 2003 Server

JDK 1.4.2

Tomcat 5.0

Driver Name: Microsoft SQL Server 2005 JDBC Driver

JDBC Driver Version 1.0

Use struts connection pool in application.

Database Server:

CPU 2*1.66G, 4GB ram, 4 x 146GB

Windows 2003 Server

SQL Server 2005, Product Version: 9.00.1399

Hello,

Do you get similar failures with the newest JDBC driver?

The latest driver can be downloaded at the link below. I would try the v1.1 or the V1.2 CTP drivers.

http://msdn2.microsoft.com/en-us/data/aa937724.aspx

Thanks,

Jaaved

|||

Use JDBC 1.2 now, but the error is same:

All is same, just remove "An exception occurred during the DBComms.receive method."

|||

Is the exception "Connection Reset" or "Connection Reset by Peer"?

Here is how to enabled Logging in the JDBC driver:

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

Thanks,

Jaaved

An exception occurred during the DBComms.receive method.

I am getting SQLException when I connect SQL Server 2005. I would like to know how to correct this problem.

I get this error randomly. Never seenit before. Sometimes I get this error one day later from application begins, sometimes two days later, sometimes 3 day later.

Web Server:

CPU 2*1.66G, 4GB ram, 146GB

Windows 2003 Server

JDK 1.4.2

Tomcat 5.0

Driver Name: Microsoft SQL Server 2005 JDBC Driver

JDBC Driver Version 1.0

Use struts connection pool in application.

Database Server:

CPU 2*1.66G, 4GB ram, 4 x 146GB

Windows 2003 Server

SQL Server 2005, Product Version: 9.00.1399

Hello,

Do you get similar failures with the newest JDBC driver?

The latest driver can be downloaded at the link below. I would try the v1.1 or the V1.2 CTP drivers.

http://msdn2.microsoft.com/en-us/data/aa937724.aspx

Thanks,

Jaaved

|||

Use JDBC 1.2 now, but the error is same:

All is same, just remove "An exception occurred during the DBComms.receive method."

|||

Is the exception "Connection Reset" or "Connection Reset by Peer"?

Here is how to enabled Logging in the JDBC driver:

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

Thanks,

Jaaved

Friday, February 24, 2012

AMO: Hanging on Partition.Update

I am using AMO to manage partitions.

I am trying to create a partition then use the Update method to create the new partition.. snippet of code below:

65 //create the new partition

66 Partition newPartition = mg.Partitions.Add(partitionName, partitionName);

67 newPartition.StorageMode = StorageMode.Molap;

68 newPartition.Source = new QueryBinding(db.DataSources[0].ID, bindingQuery);

69 XmlaWarningCollection warnings = new XmlaWarningCollection();

71 newPartition.Update(UpdateOptions.Default,UpdateMode.Create,warnings);

72 //TODO: Deal with warnings

Every time I run it, it just hangs on the Update method. It seems to be looping.. the query execution will only stop when I restart the SSAS Service.

No errors, no log entries, no Event Log entries.... nada, zilch, vacuum, nothing......

Any ideas anyone?

BTW: This is the SP2 CTP.. I think this might be a bug...|||

It sounds like a bug, it should not hang indefinitely. It would be a good idea to post this on the connect site if it is not already there.

In the mean time, have you tried calling Update() at the cube level, not on the partition object? This is the pattern that the AmoAdventureWorks sample uses (one of the AMO samples distributed with SQL Server), they don't actually call update on the partitions or measure groups, calling update at the cube level causes the changes to all the child objects to get persisted.

|||OK.. I'll give that a whirl... I do seem to be spending an awful lot of time at the connect site lately...

|||

Changed the code to the following:

65 //create the new partition

66 Partition newPartition = mg.Partitions.Add(partitionName, partitionName);

67 newPartition.StorageMode = StorageMode.Molap;

68 newPartition.Source = new QueryBinding(db.DataSources[0].ID, bindingQuery);

69 XmlaWarningCollection warnings = new XmlaWarningCollection();

71 cube.Update(UpdateOptions.ExpandFull,UpdateMode.CreateOrReplace);

72 //newPartition.Update(UpdateOptions.Default,UpdateMode.Create,warnings);

73 //TODO: Deal with XmlaWarningCollection warnings

But the answer is still no.. it hangs on the update cube method too.. I can see a CommandBegin in the trace with the following XMLA:

<Create AllowOverwrite="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
...

and the details for the new partition is indeed included in the XMLA...but nothing happens .. it is still hanging... no errors, no logs.. the CPU for the SSAS service is zero with the memory stationary.

When I try to look at anything to do with the SSAS database in the SQL Server Management studio, it just hangs.

This is getting serious!

|||

This is probably a silly question, but can I just confirm that you are in fact using the Enterprise edition of SQL Server? Partitions are an Enterprise only feature and if you try to deploy them to a Standard edition server you will get unpredictable results.

I don't know if you have tried this yet, but I have seen one other case where we had symptoms similar to this, simple "alter" statements were causing the server to "hang". We were lucky that this was a dev environment and deleting the database first and then fully re-deploying appeared to fix this, but I have not yet been able to identify what triggered this behaviour.

|||

This is currently the developer edition, SP2 CTP which I am using as a local dev environment. I don't yet have a proper server environment in which to run tests.

From my understanding developer edition has alll the functionality of the Enterprise edition (?)

|||

You are correct. The Developer edition has the same functionality as the Enterprise edition.

I don't know if this is possible, but could you create a partition manually and trace the xmla that is generated using profiler and then run your program and trace that and see if the xmla that is produced is different? That might help highlight something that you might need to add or change to get things working.

AMO: About the measure object''s remove method.

hi,friend, please look this:

I have a measure object in AMO.

Dim meas As Microsoft.AnalysisServices.Measure
Meas =tDatabase.Cubes(0).MeasureGroups(0).Measures(0)

tDatabase.Cubes(0).MeasureGroups(0).Measures.Remove(Meas, True)

I just want to know the meaning of the Remove method's second para. Measures.Remove(item as Microsoft.AnalysisServices.Measure, CleanUp as boolean)

What's the CleanUP? The remove method also support to only use the item para. What happened if I set the CleanUP to True/False, or just missing it?

Thanks!

Hi,

The 'cleanUp' boolean argument specifies if the dependent objects will be removed. For a Measure, the dependents include the PerspectiveMeasure objects. For a DimensionAttribute, the dependents include the CubeAttribute objects.

If you do not specify the 'cleanUp' parameter, the dependents are removed.

If you want for example to replace one object with another (having the same ID), then you can use the 'cleanUp'=false argument.

When working connected to the server, the 'cleanUp' argument has limited use, because you cannot save an invalid object or an object that would invalidate other objects in the database. For example, if you chose to remove a Measure with 'cleanUp'=false and dependent PerspectiveMeasures do exist, then you will get an error when trying to save the MeasureGroup (because it will leave the Perspective(s) with broken references).

Adrian Dumitrascu

|||

Thank you very much, Adrian.

As you said,The 'cleanUp' boolean argument specifies if the dependent objects will be removed.

I want to konw, the KPI object is the dependent object to the Measure? When I remove a measure with CleanUp= True, will the KPI associated be removed too? Or Just I need to remove the KPI by myself? If I don't remove the KPI, but removed the measure which associated, will it report an error when I save it to the server?

Thanks again.

|||

> I want to konw, the KPI object is the dependent object to the Measure? When I remove a measure with CleanUp= True, will the KPI associated be removed too?

No, the KPI objects are not removed. If the Value property of the KPI is referring to a certain measure, when you delete that measure, AMO will not parse the Value (nor any Command objects from an MdxScript in the Cube, or other MDX fragments in other objects) to see if the measure is used.

When checking for dependents, AMO only sees direct-defined metadata references; for example: a PerspectiveMeasure has the MeasureID property to reference the Measure, a CubeAttribute has the AttributeID property to reference a DimensionAttribute, a CubeDimension has the DimensionID property to reference a Dimension.

> If I don't remove the KPI, but removed the measure which associated, will it report an error when I save it to the server?

The .Update() method will succeed (because the Analysis Services engine will do a similar dependencies analysis as AMO), but you will get MDX errors when browsing.

Adrian Dumitrascu

|||

very clear!

Thank you, Adrian!

AMO: About the measure object's remove method.

hi,friend, please look this:

I have a measure object in AMO.

Dim meas As Microsoft.AnalysisServices.Measure
Meas =tDatabase.Cubes(0).MeasureGroups(0).Measures(0)

tDatabase.Cubes(0).MeasureGroups(0).Measures.Remove(Meas, True)

I just want to know the meaning of the Remove method's second para. Measures.Remove(item as Microsoft.AnalysisServices.Measure, CleanUp as boolean)

What's the CleanUP? The remove method also support to only use the item para. What happened if I set the CleanUP to True/False, or just missing it?

Thanks!

Hi,

If you do not specify the 'cleanUp' parameter, the dependents are removed.

If you want for example to replace one object with another (having the same ID), then you can use the 'cleanUp'=false argument.

Adrian Dumitrascu

|||

Thank you very much, Adrian.

As you said,The 'cleanUp' boolean argument specifies if the dependent objects will be removed.

Thanks again.

|||

> I want to konw, the KPI object is the dependent object to the Measure? When I remove a measure with CleanUp= True, will the KPI associated be removed too?

> If I don't remove the KPI, but removed the measure which associated, will it report an error when I save it to the server?

The .Update() method will succeed (because the Analysis Services engine will do a similar dependencies analysis as AMO), but you will get MDX errors when browsing.

Adrian Dumitrascu

|||

very clear!

Thank you, Adrian!

Sunday, February 19, 2012

AMO - Memory Error !!! PLEASE HELP

based on the Adventure Works example i decide to create the DSV, when i call the method ' AddTable ' as follows:

private void AddTable(Microsoft.AnalysisServices.DataSourceView dsv, OleDbConnection connection, String tableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",
connection);
DataTable[] dataTables = adapter.FillSchema(dataSet, SchemaType.Mapped, tableName);
DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add("TableType", "Table");
dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");
dataTable.ExtendedProperties.Add("DbTableName", tableName);
}

when i try to create the Dim product calling this function i got this error:

Memory error: While attempting to store a string, a string
was found that was larger than the page size selected. The operation
cannot be completed.
Errors in the OLAP storage engine: An error
occurred while the 'LargePhoto' attribute of the 'DimProduct' dimension
from the 'teste2005' database was being processed.
Errors in the
OLAP storage engine: The process operation ended because the number of
errors encountered during processing reached the defined limit of
allowable errors for the operation.

And i know that this functions works well because if i comment the creation of DimProduct, everything works fine, so the problem is with some atributes, like ' LargePhoto' for example,

How can i fix this memory error ?, i read some posts in the forum, but i didn't get a concrete answer,

PLEASE HELP !!!!!This may help:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=145355&SiteID=1

Monday, February 13, 2012

Am I doin this right? SELECT / ExecuteNonQuery statement

I am working on a web service method that will return weather or not a page url is stored in the database but the ExecuteNonQuery keeps returning -1. I was just wondering if i was doing anything wrong or why the ExecuteNonQuery method does not return a value of 1 or more indicating that the pageurl exists in the database? I have tried using the SQLDataReader as well to no effect and I have verified that SELECT statement refers to valid table and field names. Any help or pointers would be appreciated. I'm still kind of a newb when it comes to db programming.

1 <WebMethod()> _
2Public Function IsPageStored(ByVal pageurlAs String)As Boolean
3 If String.IsNullOrEmpty(pageurl)Then Return False
45 Dim connAs New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
78Dim cmdAs String9 cmd ="SELECT [" & Constants.SourceFieldName &"] "10 cmd &="FROM [" & Constants.StoredCopyTableName &"] "11 cmd &="WHERE ([" & Constants.SourceFieldName &"] ='@.Source')"12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@.Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@.Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase
1516 conn.Open()
1718Dim existsAs Boolean =False19 exists = (C.ExecuteNonQuery > 0)
2021 conn.Close()
22 C.Dispose()
23 C =Nothing24 conn.Dispose()
25 conn =Nothing
2627 Return exists
28End Function29

ExecuteNonQuery:Runs theAdomdCommand without returning any results.

For the purpose I understand, you can use ExecuteReader or ExecuteScalar if you required only one column value is return.

I think you have to use. ExecuteScalar.

Please make sure you click the Answer button if this is true answer.

Regards.

|||

In addition to what the prior person mentioned, if all you want to know is that the record exists then query for a count. SELECT COUNT(primaryKey) FROM tblName WHERE .....<add all your where conditions here>. Then you can use ExecuteScalar - much more efficient.

|||

<WebMethod()> _
2 Public Function IsPageStored(ByVal pageurlAs String)As Boolean
3 If String.IsNullOrEmpty(pageurl)Then Return False
4
5 Dim connAs New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
7
8 Dim cmdAs String

cmd="IF EXISTS("
9 cmd &="SELECT [" & Constants.SourceFieldName &"] "
10 cmd &="FROM [" & Constants.StoredCopyTableName &"] "
11 cmd &="WHERE ([" & Constants.SourceFieldName &"] ='@.Source')"

cmd &= ") SET @.RETVAL=1 ELSE SET @.RETVAL=0"
12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@.Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@.Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase

c.Parameters.Add("@.RETVAL", SqlDbType.Int).Direction = ParameterDirection.Output

15
16 conn.Open()
17
18 Dim existsAs Boolean =False

c.ExecuteNonQuery
19 exists = c.Parameter("@.RETVAL").Value
20
21 conn.Close()
22 C.Dispose()
23 C =Nothing
24 conn.Dispose()
25 conn =Nothing
26
27 Return exists
28 End Function

Using the EXISTS allows SQL Server to stop retrieving records when it finds the very first record that matches, instead of having to find them all. This also should force SQL Server to optimize it's query plan.

I've also changed the code from the above examples to use an output parameter instead of returning a value as a resultset. This is a more efficient way to return a simple result as well.

|||

Thanks for the tips every one, they helped me greatly!

Sunday, March 11, 2012

An exception occurred during the DBComms.receive method.

An exception occurred during the DBComms.receive method.

An exception occurred during the DBComms.receive method.

Friday, February 24, 2012

AMO: Hanging on Partition.Update

AMO: About the measure object''s remove method.

AMO: About the measure object's remove method.

Sunday, February 19, 2012

AMO - Memory Error !!! PLEASE HELP

Monday, February 13, 2012

Am I doin this right? SELECT / ExecuteNonQuery statement

SQL Alternative to cursor

Blog Archive

About Me