Consolidate two UPDATE statements into on

I'm working on a stored procedure to age balances. In short, here's the algo
rithm:
Payments get applied to the oldest 'bucket' first; in my example, BalanceOve
r180.
Is the value in the Payments column greater than or equal to the value in Ba
lanceOver180?
If so, subtract BalanceOver180 from Payments and set BalanceOver180 to 0.
If not, subtract Payments from BalanceOver180 and set Payments to 0.
Then go on to the next oldest 'bucket' (which would be Balance151To180) and
run the same two update statements.
There are a total of 7 columns against which payments need to be applied.
Here are the two statements that execute for each column:
UPDATE AgedBalances
SET Payments = Payments - BalanceOver180, BalanceOver180 = 0
WHERE Payments >= BalanceOver180 AND Payments > 0
UPDATE AgedBalances
SET BalanceOver180 = BalanceOver180 - Payments, Payments = 0
WHERE Payments < BalanceOver180 AND Payments > 0
My question: Can these two statements be combined into one using the ABS fun
ction or the CASE construct
or something similar that I don't seem to be able to figure out?
Sample data:
BalanceOver180 Payments
Before: 200 500
After: 0 300
OR
Before: 600 500
After: 100 0
OR
Before: 400 400
After: 0 0
You get the picture.
As always, thanks in advance for all assistance.
Carl Imthurnsomething like that:
update <yor table>
set BalanceOver180 =
case
when
(Payments < BalanceOver180) then (BalanceOver180 - Payments)
when
(Payments >= BalanceOver180) then 0
end,
Payments =
case
when
(Payments < BalanceOver180) then 0
when
(Payments >= BalanceOver180) then (Payments - BalanceOver180)
end
where Payments > 0
--
Programmer
Let me know if it works for you :-))
"Carl Imthurn" wrote:

> I'm working on a stored procedure to age balances. In short, here's the al
gorithm:
> Payments get applied to the oldest 'bucket' first; in my example, BalanceO
ver180.
> Is the value in the Payments column greater than or equal to the value in
BalanceOver180?
> If so, subtract BalanceOver180 from Payments and set BalanceOver180 to 0.
> If not, subtract Payments from BalanceOver180 and set Payments to 0.
> Then go on to the next oldest 'bucket' (which would be Balance151To180) an
d run the same two update statements.
> There are a total of 7 columns against which payments need to be applied.
> Here are the two statements that execute for each column:
> UPDATE AgedBalances
> SET Payments = Payments - BalanceOver180, BalanceOver180 = 0
> WHERE Payments >= BalanceOver180 AND Payments > 0
> UPDATE AgedBalances
> SET BalanceOver180 = BalanceOver180 - Payments, Payments = 0
> WHERE Payments < BalanceOver180 AND Payments > 0
> My question: Can these two statements be combined into one using the ABS f
unction or the CASE construct
> or something similar that I don't seem to be able to figure out?
> Sample data:
> BalanceOver180 Payments
> Before: 200 500
> After: 0 300
> OR
> Before: 600 500
> After: 100 0
> OR
> Before: 400 400
> After: 0 0
> You get the picture.
> As always, thanks in advance for all assistance.
> Carl Imthurn
>|||That worked - thank you very much Sergey.
Sergey Zuyev wrote:

> something like that:
> update <yor table>
> set BalanceOver180 =
> case
> when
> (Payments < BalanceOver180) then (BalanceOver180 - Payments)
> when
> (Payments >= BalanceOver180) then 0
> end,
> Payments =
> case
> when
> (Payments < BalanceOver180) then 0
> when
> (Payments >= BalanceOver180) then (Payments - BalanceOver180)
> end
> where Payments > 0

Consolidate Rows on Select

I want to condense several rows of a table using a select
statement, function, or stored procedure
For Example
mytable :
1 a
2 b
2 c
2 d
3 a
so that my select should result in
1 a
2 b,c,d
3 a
Any ideas or suggestions?
While this code references the sample database in my book (CHA2), the
pattern will work for your problem as well. In this example, the @.EventDates
variable is used to gather, or denomalize, the EventDate column:
USE CHA2
DECLARE
@.EventDates VARCHAR(1024)
SET @.EventDates = ''
SELECT @.EventDates = @.EventDates + CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (Select DateBegin as [d] FROM Event
JOIN Tour
ON Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a
SELECT Left(@.EventDates, Len(@.EventDates)-1) AS 'Outer Banks Lighthouses
Events'
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:392801c4aa4b$477a6810$a401280a@.phx.gbl...
>I want to condense several rows of a table using a select
> statement, function, or stored procedure
> For Example
> mytable :
> 1 a
> 2 b
> 2 c
> 2 d
> 3 a
> so that my select should result in
> 1 a
> 2 b,c,d
> 3 a
> Any ideas or suggestions?

Console apps work for SA but no other user

Hi

My console applications work forSA and no other user. I can run the Stored procedures used in the console application from Query analyser when logged in with username/password that I am attempting to use for console applications. I am using SQL server authenication. User access permissions look ok in Enterprise Manager. Access is permit for my user.

Any suggestions?

Thanks

Permissions in SQL are much more than just access permit. You should also grant EXECUTE permssion for a stored procedure to a user if you want the user to execute the stored procedure; or you can create a role and add the user as member, then grant proper permissions to the role. To understand permissions related concepts in SQL, you can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_03_8q2b.asp

sqlsql

console apps only work if user is SA

Hi

When I try to use a user other then SA my console apps don't work.

I can run the Stored Procedures used in console application from Query analyser when logged in with the username/password that
I'm attempting to use for the console applications.

Under Users in Enterprise Manager Database access is 'permit' for my user.

By the way my web application which uses the same user name and password as in console applications is working. I also have dts packages running using dtsexec accessing the database with the same user name and password and they work fine.

MDAC 2.8 SP2 on windows server 2003 spi

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>ODBCPING.EXE -S xxx.xxx.xxx.xxx
-U myusername -P mypassword

CONNECTED TO SQL SERVER

ODBC SQL Server Driver Version: 03.86.1830

SQL Server Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

If the connection is available then why is the adapter.fill method failing?

I tried it using a text sql statement and that doesn't work either.

The problem isn't database specific as I did a test .bat on Northwind sample database and got same 'general network error'

Here's the error:

apps\Exports>exporter.bat

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, Conne
ctionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startR
ecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior be
havior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

//Testing using SELECT statement as command text

D\Test>Test.bat

D:\Test>TestDBAccess.exe "server=xxx.xxx.xxx.xxx;uid=xxxx;pwd=xxx;
database=Northwind;"

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()

D:\Test>

Any ideas/help much appreciated!

Hi,

would be cool if you could show us your code.Sometimes people hardcode certain properties (I confess that I did that on my own one time :-) ) which will lead to an error where usally there shouldn′t be an error (especially in console apps where connection properties are passed via arguments which isn′t testable while debugging within VS)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Here's the essentials of the code (without some try catch statements).

Thanks.

KH

.bat file

::arg 0 Connection String
::arg 1 Export Path //just a path where I want the output file to go

Exporter.exe "server=xxx.xxx.xxx.xxx;uid=username;pwd=password;database=DATABASENAME;" "d:\\xxx\\xxx\\ExportOut\\"

Source code

using System;

using System.Data;
using System.Security;
using System.Security.Permissions;
using System.Security.Policy;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

namespace Exporter
{
class Export
{

private static String ConnectionString;

private static String ExportPath;

private static StreamWriter ExportLog;

[STAThread]
static void Main(string[] args)
{

ConnectionString = args[0];

ExportPath = args[1];

String DateString = System.DateTime.Today.Day.ToString() + "_" + System.DateTime.Today.Month.ToString();

ExportLog = new StreamWriter(ExportPath+"Export_Log_"+DateString+".txt");

DoExport();

ExportLog.Close();

}

private static void DoExport()
{

ExportLog.WriteLine("Beginning export");
GetData());

}

private static void GetData()
{

SqlCommand SelectCommand = new SqlCommand();

SelectCommand.CommandType=(System.Data.CommandType.StoredProcedure);

SelectCommand.CommandText="GetCSVOutput";

SqlConnection Conn = new SqlConnection(ConnectionString);

SelectCommand.Connection=Conn;

SqlDataAdapter ReportAdapter = new SqlDataAdapter();

FaultReportAdapter.SelectCommand=SelectCommand;

DataSet ReportData = new DataSet();

ReportAdapter.Fill(ReportData,"Report");

if (ReportData.Tables["Report"].Rows.Count == 0)
{
Conn.Close();
ExportLog.WriteLine("No reports to export");
return false;

}
else
{
Conn.Close();
return MakeFile(ReportData);

}

}

private static void MakeFile(DataSet FaultReportData)
{

/* Just prints out the results of Stored procedure to file and closes file */

}

|||Hi,

beside that the FaultReportDapater doesn′t exists (but I guess this is just a typo) you can try disabling the connection pool to see if it is based on this with adding the keywords "Pooling=False" to the connecting string.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Yes that was just a typo. With pooling set to false I still get the same error.

Thanks

KH

|||

What is the call stack of the exception if you disable pooling?

|||

Here's the call stack with pooling set to false.

D:\apps\Export>exporter.bat

D:\content\apps>Exporter.exe "server=xxx.xxx.xxx.xxx;uid=xxxx;pwd=;database=xxxx;pooling=False" "d:\\Content\\xxxx\\ExportOut\\"

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection
, SqlConnectionString connectionOptions)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, Conne
ctionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startR
ecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior be
havior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at Exporter.Export.GetData() in
\\xxxx\dotnet_dll_production
\xxxx\xxxxx\export.cs:line 536
at Exporter.Export.DoExport() in
\\xxxx\dotnet_dll_production\xxxx\xxxx\export.cs:line 143
at Exporter.Export.Main(String[] args) in \\xxxx\dotnet_dll_produc
tion\xxxx\xxxx\export.cs:line 95

D:\apps\Export>

Connnecting to an MS Access DB with an MS SQL 2000 Stored Proc

I would like to access a table in MS Access from a stored procedure on MS
SQL 2000. The MS Access DB is running on a diferent computer attached on the
same network. I think I need to create an ODBC connection from the MS SQL
2000 to the MS Access DB but not sure. Secondly how do I refer to the table
in the MS SQL stored proc?

Thanks for your input,

Joe SeamourYou need to add a linked server, and then you can query the Access database
directly. The BOL entry for sp_addlinkedserver shows an example, and the
topic "OLE DB Provider for Jet" has more details.

I've never set this up to use an Access DB on a different server myself, but
I think it should work if the MSSQL service account has permission to access
the share where the .mdb file is.

When the linked server is configured, you can query it like this.

SELECT * FROM MyLinkedServer...Table

Simon

"Joe Seamour" <jseamour@.attbi.com> wrote in message
news:1MYKa.33514$Bg.16441@.rwcrnsc54...
> I would like to access a table in MS Access from a stored procedure on MS
> SQL 2000. The MS Access DB is running on a diferent computer attached on
the
> same network. I think I need to create an ODBC connection from the MS SQL
> 2000 to the MS Access DB but not sure. Secondly how do I refer to the
table
> in the MS SQL stored proc?
> Thanks for your input,
> Joe Seamour

Connectivity Issue between XP, SQL Server 2000 & SPSS

Hi,
I am trying to build an application with Windows XP, SPSS & SQL
Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
keeps on running & nothing happens.
I tried the same thing on Windows 2003 server. It ran after I installed
.Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
I wonder what I have to do make it run on XP
Any help would be greatly appreciated.
Regards
IT Dev.Hi
"IT Developer" wrote:
> Hi,
> I am trying to build an application with Windows XP, SPSS & SQL
> Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
> with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
> keeps on running & nothing happens.
> I tried the same thing on Windows 2003 server. It ran after I installed
> .Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
> I wonder what I have to do make it run on XP
> Any help would be greatly appreciated.
>
> Regards
>
> IT Dev.
>
I am not familiar with SPSS, but if you are trying to start a windows
application that requres user input from xp_cmdshell then you should try a
different method. I assume SPSS is on the SQL Server itself?
John

Connectivity Issue between XP, SQL Server 2000 & SPSS

Hi,
I am trying to build an application with Windows XP, SPSS & SQL
Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
keeps on running & nothing happens.
I tried the same thing on Windows 2003 server. It ran after I installed
..Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
I wonder what I have to do make it run on XP
Any help would be greatly appreciated.
Regards
IT Dev.
Hi
"IT Developer" wrote:

> Hi,
> I am trying to build an application with Windows XP, SPSS & SQL
> Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
> with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
> keeps on running & nothing happens.
> I tried the same thing on Windows 2003 server. It ran after I installed
> .Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
> I wonder what I have to do make it run on XP
> Any help would be greatly appreciated.
>
> Regards
>
> IT Dev.
>
I am not familiar with SPSS, but if you are trying to start a windows
application that requres user input from xp_cmdshell then you should try a
different method. I assume SPSS is on the SQL Server itself?
John

ConnectionRead(recv()) error help

I keep getting this error while I run my application. I used to run the application but it was taking to long so I decided to add some stored procedures to it and this error started happening.

Microsoft OLE DB Provider for ODBC Drivers
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).

Does anyone know what this error is referring to?

JoseWhat does your connection string look like in your application? And is this an ASP or ASP.NET application?

Terri

Connection.Open () ERROR ! Unable to find stored procedure sp_sdidebug.

Hi all,

When I try to open a connection it gives me error "Stored Procedure sp_sdidebug not found"

Can anyone help me in this respect?

I was running the same application with the SQL Server 2005 only earlier when it was running.But now after I reinstalled VS.NET & SQL Server I am getting this error.What could be the problem?

Please help... I am badly stuck up.

PAM

To rule out the obvious, have you double checked that sp_sdidebug exists on the server that your SQL connection is connecting to? Also, are you connecting to the same server?|||

You are right this stored procedure does not exist in <Database>/Programmability/Stored Procedures/

Is this the location where I should see for it or I need to look into Master?Even that is not there.I checked all other databases but this procedure is nowhere.

What can be done?I have already reinstalles Sql Server 2005.

& I am really stuck up in a stupid issue of connecting to database.

Please advise me something.

Pam

|||

I found the answer somewhere in online forums.The cause of the problem was that the sql server debugging was enabled in the debugging properties of the project.

thanks anyways.

pam

Connection.Execute & SQL Stored Procedure

I am hopeful that those of you with more experience in the compilation of
.asp, the connection object and MS SQL Server as the DB can help me out. I
have a web app that routinely calls stored procedures in a MS SQL DB.
In one web page, I make 6 calls with the Connection.Execute 'store
procedure'. Five of the six calls execute as expected. However; I have one
call in middle of the 6 that only executes the stored procedure partially.
This particular stored procedure does quite a bit of work within the DB, mak
e
3-4 select quiries and then a few updates to a single record. In this
procedure, one of SQL update statements is constructed in a loop to ensure
the appropriate fields are updated and no others. (The fields updated vary
with the input being used) This is where the procedure will "time out". I
have reduce/elimnated all the "debuggin" code that is no esential to the
store procedure. When the dubbuging print and select statements commented
out the procedure will execute further when called from the asp page.
Has anyone run across a situation where the .asp does not allow a stored
procedure to execute to completion before executing the next line of .asp
code? If so is there a way to force the Connection.Execute call to wait unti
l
the SQL server stored procedure has completed executing before processing th
e
next line of asp? Note the stored procedure does not explicitly return
anything to the asp code... also noteworthy, if the same stored procedure is
run from the SQL Query Analyzer (a tool for MS SQL Server) the stored
procedure runs flawlessly and as expected to completion...
Anyone have an idea?Simon McLaren wrote:
> I am hopeful that those of you with more experience in the
> compilation of .asp, the connection object and MS SQL Server as the
> DB can help me out. I have a web app that routinely calls stored
> procedures in a MS SQL DB.
This would have been more on topic at .inetserver.asp.db. However, read on.

> In one web page, I make 6 calls with the Connection.Execute 'store
> procedure'. Five of the six calls execute as expected. However; I
> have one call in middle of the 6 that only executes the stored
> procedure partially. This particular stored procedure does quite a
> bit of work within the DB, make 3-4 select quiries and then a few
> updates to a single record. In this procedure, one of SQL update
> statements is constructed in a loop to ensure the appropriate fields
> are updated and no others. (The fields updated vary with the input
> being used) This is where the procedure will "time out". I have
> reduce/elimnated all the "debuggin" code that is no esential to the
> store procedure. When the dubbuging print and select statements
> commented out the procedure will execute further when called from the
> asp page.
>
There are two timeout properties to be concerned with:
1. The IIS ScriptTimeout property, which controls the max time a page will
be allowed to execute on the server. This can be set globally using IIS
Manager to modify your website's application properties. It can also be set
at the page level using:
Server.ScriptTimeout = <some reasonable value>
2. The ADO Connection's CommandTimeout property. This controls the amount of
time a Command will allow a statement to execute. If set for a longer period
of time than the ScriptTimeout, the script may time out before the command
finishes executing.This is set via:
dim cn
set cn=createobject("adodb.connection")
cn.open ...
cn.CommandTimeout = <some reasonable value>
You should go to msdn.microsoft.com/library and read the documentation about
these properties.
Having said that, I need to add: Why call 6 procedures from ASP? One of the
main benefits of procedures is encapsulation, a huge benefit of which is
reducing the number of round trips to the database. You should create a new
procedure which accepts all the arguments required to call the other 6
procedures. This procedure calls the 6 procedures in turn and then returns
the overall result to the ASP client. One trip to the database. This can be
a huge performance booster.
I also need to add: you need to do some work to optimize the procedures you
have. You should not be allowing an asp page to time out waiting for
database activity to complete. Is there any way to take that time-consuming
activity offline?
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||I'll try to answer to your question as much as I understood it.
First of all, ASP is not compiled, it is interpreted language. Ok, this has
nothing with your question but I had to say it :)
ADO Connection/Command object has CommandTimeout property which defines for
how long it will wait for SP to execute.
I did not work with ASP and ADO for a long time. I had that "luck" to switch
to .NET (c#) and ADO.NET, but from my expirience with ASP adn SPs I would
advice you to:
-Remove print commands from your SP, it may confuse ADO
-Set "set nocount on" at the beging of your code
-Close connection as soon as you finish you finish your work with DB
-Use SQL OLE DB provider for SQL Server
http://msdn.microsoft.com/library/d...
qlprovspec.asp
I hope this help
Regards,
Marko Simic
"Simon McLaren" wrote:

> I am hopeful that those of you with more experience in the compilation of
> .asp, the connection object and MS SQL Server as the DB can help me out. I
> have a web app that routinely calls stored procedures in a MS SQL DB.
> In one web page, I make 6 calls with the Connection.Execute 'store
> procedure'. Five of the six calls execute as expected. However; I have one
> call in middle of the 6 that only executes the stored procedure partially.
> This particular stored procedure does quite a bit of work within the DB, m
ake
> 3-4 select quiries and then a few updates to a single record. In this
> procedure, one of SQL update statements is constructed in a loop to ensure
> the appropriate fields are updated and no others. (The fields updated vary
> with the input being used) This is where the procedure will "time out".
I
> have reduce/elimnated all the "debuggin" code that is no esential to the
> store procedure. When the dubbuging print and select statements commented
> out the procedure will execute further when called from the asp page.
> Has anyone run across a situation where the .asp does not allow a stored
> procedure to execute to completion before executing the next line of .asp
> code? If so is there a way to force the Connection.Execute call to wait un
til
> the SQL server stored procedure has completed executing before processing
the
> next line of asp? Note the stored procedure does not explicitly return
> anything to the asp code... also noteworthy, if the same stored procedure
is
> run from the SQL Query Analyzer (a tool for MS SQL Server) the stored
> procedure runs flawlessly and as expected to completion...
> Anyone have an idea?
>|||Bob and Simic,
Thanks for taking the time to respond. Bob to answer you question about why
the 6 SP calls... Originally it the six where all one procedure.
Progromatically, the six procedures achimplish "steps" of a process, all of
which in this application could be (and are) occassionally executed
independently. I have created an encapsulating SP that in turn calls the 6
seperate SP's, but this non-completion issue forced me to back away from tha
t
idea to try and isolate the "offending" procedure.
The Connection/Command timeouts have been set to 60seconds. This failure
happens much quicker than 60 seconds.
By trial and error last w I tried commenting out all "debugging" code (to
include print and select statement indented to allow me to see what was
happening and if dynamicly generated SQL statements where correct.) in the
offending stored procedure, figuring not that it might be confusing ADO, but
rather that is was adding to the execution time of the stored procedure. An
d
presto, execution to completion for the .asp page and no debuggin "insight"
from the query analyzer.
While I "stumbled" across the issue on my own, it was not until reading your
responses that I understood what the actual issue was. With "debugging code
"
commented out of the stored procedures, I believe I can move back to a singl
e
SP and trip to the database.
Thanks,
Simon
"Simic Marko" wrote:
> I'll try to answer to your question as much as I understood it.
> First of all, ASP is not compiled, it is interpreted language. Ok, this ha
s
> nothing with your question but I had to say it :)
> ADO Connection/Command object has CommandTimeout property which defines fo
r
> how long it will wait for SP to execute.
> I did not work with ASP and ADO for a long time. I had that "luck" to swit
ch
> to .NET (c#) and ADO.NET, but from my expirience with ASP adn SPs I would
> advice you to:
> -Remove print commands from your SP, it may confuse ADO
> -Set "set nocount on" at the beging of your code
> -Close connection as soon as you finish you finish your work with DB
> -Use SQL OLE DB provider for SQL Server
> http://msdn.microsoft.com/library/d...sqlprovspec.asp
> I hope this help
> Regards,
> Marko Simic
>
> "Simon McLaren" wrote:
>

Connection.CommandTimeout

Running VB6 with SQL Server 7, opening a connection and setting this
parameter by Conn.CommandTimeout does not always seem to force a
command (stored procedure) executed through Conn.Execute to timeout
after the specified period. Is anybody able to suggest what may be
going on here.

Thankspeter.dawson@.kingsch.nhs.uk (Petros) wrote in message news:<478ba33e.0309300533.2e31f9f7@.posting.google.com>...
> Running VB6 with SQL Server 7, opening a connection and setting this
> parameter by Conn.CommandTimeout does not always seem to force a
> command (stored procedure) executed through Conn.Execute to timeout
> after the specified period. Is anybody able to suggest what may be
> going on here.
> Thanks

CommandTimeout starts when the database server has accepted the
command and ends when the database server returns the first record. If
the server or network is busy (or not answering at all), this setting
won't help you to get control again.|||Petros (peter.dawson@.kingsch.nhs.uk) writes:
> Running VB6 with SQL Server 7, opening a connection and setting this
> parameter by Conn.CommandTimeout does not always seem to force a
> command (stored procedure) executed through Conn.Execute to timeout
> after the specified period. Is anybody able to suggest what may be
> going on here.

If you are also running queries from the command object, you need
to see the .CommandTimeout for that object too; the value is not
inherited from the connection object.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Connection timeout when generating recordset from a stored procedure in an ASP page

I'm trying to create a binding to a stored procedure on a SQL server. I can
create the database connection and view the tables and stored procedures.
When I try to create a stored procedure binding I can choose the connection
and the procedure. But when I check "Returns DataSet" and click Test... I
get the following Dreamweaver error after a minute or so:
A server timeout has occured. Here are the possible reasons.
1. Please make sure that the webserver is up and running.
2. Please verify that the ODBC DSN exists on the testing server.
Executing the stored procedure in SQL Query Analyzer returns results in less
than 5 seconds.
My webserver is up and running and the web.config file is the same on both
the local machine and testing server. The SQL server and webserver are
different machines. My stored procedure is below.
I can create datasets (querys) on the database tables. It's only when I try
to generate a dataset from a stored procedure that run into problems.
If it matters I'm using ASP.NET VB, SQL Server 2000, IIS 5.0, .NET 1.1 and
Dreamweaver MX 2004.
Please help, I'm at a standstill.
I get the same problem when using VB .NET to to create the ASP page. If I
use Coldfusion instead on ASP it works flawlessly. Only problem is I that
have a developers license for Coldfusion so this is not an acceptable
workaround.
Thanks in advance!! My stored procedure is below:
CREATE PROCEDURE proc_combinedDB
(@.platform varchar(40), @.server varchar(40),@.keyword varchar(50))
WITH RECOMPILE
AS
SELECT *
FROM [prtracker_classworks].[dbo].[Problem Reports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS] LIKE @.server A
ND
Details LIKE @.keyword
UNION ALL
SELECT *
FROM [prtracker_mac bugs].[dbo].[Problem Reports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS]
LIKE @.server AND Details LIKE @.keyword
UNION ALL
SELECT *
FROM [Prtracker_Arizona State Edition].[dbo].[Problem Re
ports]
WHERE [Workstation OS] LIKE @.platform AND [Server OS] LIKE @.server A
ND
Details LIKE @.keyword
... // And so on for 15 more databases
RETURN
GOHi,
I am getting the same error using DWMX 2004 / ASP / VB.
Did you found a workaround ?
If so, your help is greatly apreciated.
axiaxi2003@.hotmail.com
A
amucino
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message466748.html

Connection still apperas in SQLServer Entreprise Manager over Locks / process ID

Hello,
i have a doubt here, after a close the connection, i still can see the last stored procedure active in the LOCKS/PROCESSID in enterprise manager of SQL SERVER.
I think i'm closing the connection well

Try

IfMe.ID > 0Then

Dim strSqlAsString = "SP_CHANGECLASSCONTENTS"

Dim sqlcommandAsNew SqlCommand(strSql,New SqlConnection(ConnStr))

sqlcommand.CommandType = CommandType.StoredProcedure

sqlcommand.Parameters.Add("@.PerformActivation", SqlDbType.Bit).Value = 0

sqlcommand.Parameters.Add("@.PerformInactivation", SqlDbType.Bit).Value = 0

sqlcommand.Parameters.Add("@.PerformDelete", SqlDbType.Bit).Value = 1

sqlcommand.Parameters.Add("@.PerformUndelete", SqlDbType.Bit).Value = 0

sqlcommand.Parameters.Add("@.PermanentDelete", SqlDbType.Bit).Value = CBit(PermanentDelete)

sqlcommand.Parameters.Add("@.Class_ID", SqlDbType.Int).Value = _id

sqlcommand.Connection.Open()

sqlcommand.ExecuteNonQuery()

sqlcommand.Connection.Close()

Else

Err.Raise(10205, "CLASS", "CLASS ID is empty")

EndIf

Catch exAs Exception

_err = ex.Message

Return Err.Number

EndTry

Hi,

.NET Framework SQl data provider utilizes database connection pooling (there's one pool per unique connection string). When you close a SqlConnection, it is returned to this pool (there are min amount of connections in the pool ) and the hard connection to the db is not necessarily closed (connection ).
E.g this way usage of connections is kept efficient and opening & closing of "real connections" is kept minimum.
Here's more details
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

|||ok tks,
but what if i use many procedures like this one several times, and each one creates it's own connection. Even if i close the connection well it still appears several processes concerning this procedures in SQLSERVER. does this degrades the performance ?
tks|||

Hi,
see the previous link for exact details.
Anyways, it happens within the limits of the connection pool. If there are free connections in the pool, they are utilized to fullfill those which you instantiate and run e.g even if you instantiate new connections in code, the pool might be able to deal with them without increasing its size (the actual pooled connections are of type SqlInternalConnection not those which SqlConnection objects which you instantiate).
If the pool cannot deal with new connection requests, it starts creating new ones (within the max limit of the pool). If max limit is reached then new connection requests are queued. Note also that after a burst usage of connections, they are also dropped from the pool to the mimum limit when lifetime has expired or connectiion is severed.

E.g the pools are there to keep connection count in control in situation like these.

Connection Problem trying to execute stored procedure

I am running the following code and getting the error below:

Dim connectionstringAsString ="<%$ ConnectionStrings:ConnectionString %>"Dim ConnectionAsNew SqlClient.SqlConnection(connectionstring)Dim CommandAsNew SqlClient.SqlCommand("StoredProcedure9", Connection)

Command.CommandType = Data.CommandType.StoredProcedure

Command.Parameters.Add(

New SqlClient.SqlParameter("@.bid_id", Data.SqlDbType.Int))

Command.Parameters(

"@.bid_id").Value ="Testing"

Connection.Open()

Command.ExecuteNonQuery()

Connection.Close()

ERROR:

Format of the initialization string does not conform to specification starting at index 0.

I think my connectionstring is wrong, please help.

Thanks

CAB

Try changing:
Dim connectionstringAsString ="<%$ ConnectionStrings:ConnectionString %>"

To

Dim connectionstring as String = System.Configuration.ConnectionStrings("MyConnectionStringName").ConnectionString (or something similar to that...)

Which will grab the connectionstring out of your web.config and plug it in as the property in question. (I'm not too up on VB.NET 2.0 so I may be wrong - but pretty sure you can't use the <% %> (i.e. pre-processing tags) inside of compilable VB.NET code - i.e. I think that's your issue)
|||

Hi,

Thanks for your help, but now I am getting:

'ConnectionStrings' is not a member of 'Configuration'.

Any ideas?

|||Cabby, can you let us know which version of the .NET Framework you are using? 1.1 or 2.0?|||

Hi, I am using version 2.0

I have updated that line of code to

Dim connectionstringAsString = System.Configuration.ConfigurationManager.AppSettings("connectionstring")

and am now getting:

The ConnectionString property has not been initialized.

This seems to be a similar problem tohttp://forums.asp.net/thread/402876.aspx but I am not sure I understand what needs to be done.

Please help!

Thanks

CAB

|||That other link was for ASP.NET 1.1. See if this article helps:Configuration API Improvements in ASP.NET 2.0
|||

Thanks for the link. But that seems to be working fine. When I run:

<%

@.ImportNamespace="System.Configuration" %>

<%

@.ImportNamespace="System.Web.Configuration" %>

<

scriptrunat=serverlanguage=C#>publicvoidPage_Load(objectsource,EventArgse)

{

Response.Write("Connection String:"

+

ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

}

</

script>

<

html>

<

head><title>Retrieving Connection Strings</title>

</

head>

<

body>

<

formid="form1"runat="server"><div></div>

</

form>

</

body>

</

html>

I get:

Connection String:Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True

Which I assume means all is working with that page.

But when I run my code:

ProtectedSub DetailsView1_ItemInserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.DetailsViewInsertedEventArgs)

Dim connectionstringAsString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")Dim ConnectionAsNew SqlClient.SqlConnection(connectionstring)Dim CommandAsNew SqlClient.SqlCommand("StoredProcedure9", Connection)

Command.CommandType = Data.CommandType.StoredProcedure

Command.Parameters.Add(

New SqlClient.SqlParameter("@.bid_id", Data.SqlDbType.Int))

Command.Parameters(

"@.bid_id").Value ="Testing"

Connection.Open()

Command.ExecuteNonQuery()

Connection.Close()

Response.Redirect(

"Default.aspx")EndSub

I get:

The ConnectionString property has not been initialized.

Any ideas?

Thanks

|||

UPDATE:

I have changed the connection string to:

Dim

connectionstringAsString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString()This seems to be getting a bit further, now my error is:

Input string was not in a correct format.

which seems to be relating to the line:

Command.ExecuteNonQuery()

any ideas?

Thanks, CAB

|||

The problem is with my parameters, the stored procedure works fine if I dont try and pass it any parameters and remove :

Command.Parameters.Add(New SqlClient.SqlParameter("@.bid_id", Data.SqlDbType.Int))

I have realised that I can do this all in the stored procedure and not have to pass any variables so now all is working fine.

Thanks for all the help!!!!!

CABBY