Increase Timeout ?

I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
>> Hi,
>> The stored procedure is very straightforward. I am currently using an
>> under powered test server. I never get the timeout on the production
>> server...
>> So how do I increase the timeout ?
>> Thanks
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>>
>sql

Increase Timeout ?

I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?
Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>
|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
>

Increase Performance with two connections

HI all,
I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
In DB2 I use a view which uses some joins against DB1 like:
select * from MyTable left outer join DB1.dbo.users on ...
This works, but I get a time problem: the view is very slow.
The reason seems to be the connection to DB1.
In SQL Management Studio I can reproduce the situation: when I´m in DB2 and
try to "USE DB1" it takes something like >=2 seconds on my local machine.
After making the "USE DB1" the view works great - very fast (SQL Server
seemed to cache the connection).
But the connection caching works only for some minutes - when "falling back"
the view is slow again.
So, I´m looking for a solution for my problem (I can´t change the
database-concept, some data from DB1 is needed also in DB2). Maybe open the
connection DB1 for ... some hours? Or somebody has better ideas?
Thanks,
TonyI assume you have two databases in the same SQL Server instance (which is what it sounds like from
your description). Check if the database DB1 has the database property autoclose turned on. If so,
turn it off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
> HI all,
> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
> In DB2 I use a view which uses some joins against DB1 like:
> select * from MyTable left outer join DB1.dbo.users on ...
> This works, but I get a time problem: the view is very slow.
> The reason seems to be the connection to DB1.
> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
> takes something like >=2 seconds on my local machine.
> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
> connection).
> But the connection caching works only for some minutes - when "falling back" the view is slow
> again.
> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data from
> DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody has
> better ideas?
> Thanks,
> Tony
>|||HI Tibor,
thanks for your reply.
Yes, Autoclose=ON is standard.
Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume
it will work).
Does Autoclose=OFF have any other effects...?
Help of SQL2005 says:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature.
The AutoClose property exposes server behavior for databases not accessed by
a user.
Thanks, Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>I assume you have two databases in the same SQL Server instance (which is
>what it sounds like from your description). Check if the database DB1 has
>the database property autoclose turned on. If so, turn it off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>|||> Yes, Autoclose=ON is standard.
Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this turned
on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes your problem.
> Does Autoclose=OFF have any other effects...?
I hope you don't have other programs that tries to do anything with the database files while no user
is connected? If you do, then they will be affected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
> HI Tibor,
> thanks for your reply.
> Yes, Autoclose=ON is standard.
> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
> Does Autoclose=OFF have any other effects...?
> Help of SQL2005 says:
> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature
> in new development work, and plan to modify applications that currently use this feature.
> The AutoClose property exposes server behavior for databases not accessed by a user.
> Thanks, Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like from
>>your description). Check if the database DB1 has the database property autoclose turned on. If so,
>>turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
>> takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>|||HI Tibor,
yes, with Autoclose=OFF the App works perfect!
But I only don´t really understand
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
which all use database DB1 - and some other database-stored procs/views.
Will they be affected?
Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any
side-effects)?
Thanks again,
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
> Standard? In your shop or in SQL Server? I believe that some edition of
> SQL Server has this turned on by default, which was a bad (IMO) choice.
> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server.
>> Avoid using this feature in new development work, and plan to modify
>> applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not accessed
>> by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is
>>what it sounds like from your description). Check if the database DB1 has
>>the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>>
>|||The application uses the database *through* SQL Server. I.e., they don't access the database files
directly.
I recommend that you have autoclose set to off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
> HI Tibor,
> yes, with Autoclose=OFF the App works perfect!
> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0) which all use database
> DB1 - and some other database-stored procs/views. Will they be affected?
> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any side-effects)?
> Thanks again,
> Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this
>> turned on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes
>> your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this
>> feature in new development work, and plan to modify applications that currently use this
>> feature.
>> The AutoClose property exposes server behavior for databases not accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
>> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like
>>from your description). Check if the database DB1 has the database property autoclose turned on.
>>If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1"
>> it takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>>
>|||Hi Tibor,
yes, I also did a little reasearch on AutoClose - schould always be off on
server and productive environments!
Thanks again for your hint! This was my solution!
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:ePINj6U6HHA.5164@.TK2MSFTNGP05.phx.gbl...
> The application uses the database *through* SQL Server. I.e., they don't
> access the database files directly.
>
> I recommend that you have autoclose set to off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
>> HI Tibor,
>> yes, with Autoclose=OFF the App works perfect!
>> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
>> which all use database DB1 - and some other database-stored procs/views.
>> Will they be affected?
>> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about
>> any side-effects)?
>> Thanks again,
>> Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of
>> SQL Server has this turned on by default, which was a bad (IMO) choice.
>> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and plan to
>> modify applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not
>> accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which
>>is what it sounds like from your description). Check if the database
>>DB1 has the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in
>> DB2 and try to "USE DB1" it takes something like >=2 seconds on my
>> local machine.
>> After making the "USE DB1" the view works great - very fast (SQL
>> Server seemed to cache the connection).
>> But the connection caching works only for some minutes - when
>> "falling back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe
>> open the connection DB1 for ... some hours? Or somebody has better
>> ideas?
>> Thanks,
>> Tony
>>
>>
>

Incorrect value returned from Stored Procedure

I have an asp.net 1.1 website that uses sql server 2000 and vb.

I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run it

in the query analyzer the correct value is returned.

 Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB ' Obtain Order ID from QueryString Dim OrderID As Integer = CInt(Request.Params("ID")) ' Get the customer ID too Dim myNewCustomerId As Integer = 0 myNewCustomerId = orderHistory.GetOrderCustomer(OrderID) Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer ' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection) ' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure ' Add Parameters to SPROC Dim parameterOrderID As New SqlParameter("@.order_id", SqlDbType.Int, 4) parameterOrderID.Value = orderID myCommand.Parameters.Add(parameterOrderID) Dim parameterOrderCustID As New SqlParameter("@.customer_id", SqlDbType.Int, 4) parameterOrderCustID.Value = ParameterDirection.Output myCommand.Parameters.Add(parameterOrderCustID) 'Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() ' Return the customer_id (obtained as out paramter of SPROC) If parameterOrderCustID.Value <> 0 Then Return CInt(parameterOrderCustID.Value) Else Return 0 End If End Functionthe stored procdure isCREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID]( @.order_id int, @.customer_id int OUTPUT)AS/* Return the customer_id from the Orders. */SELECT @.customer_id = customer_id FROM nlb_Orders WHERE order_id = @.order_idGO

I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2.

Any ideas would be appreciated

Thanks

Pete

Seems you made a small mistake:

parameterOrderCustID.Value = ParameterDirection.Output

It should be:

Dim CustomerID As Integer
parameterOrderCustID.Value = CustomerID
parameterOrderCustID.Direction=ParameterDirection.Output

Incorrect Syntax Near MyCompany_SearchForJobs

I am moving my site to ASP.NET 2.0 from 1.1...in doing so I am moving some functionality over verbatim. One of them is a search feature for our job board. The code to call the stored proc has not changed...it basically looks for values that are provided on a search page and passes them to the database. If a field is not specified in the UI, a System.Db.Null is passed...in the past I did not even have to pass this back as my query worked as is...now I am getting the above exception and I cannot figure out what it is! If I pass all nulls back it works...as soon as I specify a value bam it fails. I have removed the parameter definitions defaulting to null...no difference...any ideas??
ALTER PROCEDURE MyCompany_SearchForJobs
(
@.state int = null,
@.city NVarChar (75) = null,
@.serviceArea int = null,
@.jobType int = null,
@.dateSearch DateTime = null
)
AS
SELECT
MyCompany_JobPostings.*, MyCompany_OrgInfo.OrgName AS OrgName, MyCompany_States.State AS DisplayState
FROM
MyCompany_JobPostings, MyCompany_OrgInfo, MyCompany_States
WHERE
MyCompany_JobPostings.State = IsNull(@.state , MyCompany_JobPostings.State)
AND
MyCompany_JobPostings.City Like IsNull(@.city, MyCompany_JobPostings.City)
AND
MyCompany_JobPostings.PositionType = IsNull(@.jobType, MyCompany_JobPostings.PositionType)
AND
MyCompany_JobPostings.OrgId IN (SELECT OrgId FROM MyCompany_OrgInfo WHERE ServiceArea = IsNull(@.serviceArea, ServiceArea) OR ServiceArea2 = IsNull(@.serviceArea, ServiceArea2))
AND
MyCompany_JobPostings.Status = 5
AND
MyCompany_JobPostings.JobPostedDate = IsNull(@.dateSearch, MyCompany_JobPostings.JobPostedDate)
AND
MyCompany_JobPostings.OrgId = MyCompany_OrgInfo.OrgId
AND
MyCompany_JobPostings.State = MyCompany_States.StateId
ORDER BY
MyCompany_JobPostings.JobPostedDate DESC

I figured it out...:) It was my lovely assignment code that was messing it up. I had code that chose which stored procedure to call...one with date ranges and one without. I moved the line of code that tells what type of Command it was above the assignment of the Command object and thus reinitializing it to a "text" proc. Goofy but it works now thankfully. :)

Incorrect syntax near 'HelloWorld'

Hello,

I'm testing a CLR SP in SQL Server 2005 using Visual Basic .NET Express

The code is :

Imports System.Data.SqlServer
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class SQLCLR
Public Shared Sub HelloWorld()
SqlContext.Pipe.Send("Hello World from SQLCLR !!")
End Sub
End Class

I built the project and copy the HelloWorld.dll to a local dir.
Then I registed the assembly in SQL SERVER:

create assembly HelloWorld from 'd:\xi\HelloWorld.dll' with permission_set = safe

This is successfull but when I execute:

create procedure HelloWorld as external name HelloWorld.SQLCLR.HelloWorld

Msg 6505, Level 16, State 1, Procedure HelloWorld, Line 1
Could not find Type 'SQLCLR' in assembly 'HelloWorld'.

I don't know what's happening since the name of the class is right (SQLCLR)

Any advice would appreciated...

I am guessing that you have used Visual Studio 2005 to develop your assembly. Visual Studio adds a default namespace and you need to incorporate that in the external name.
(I think the default namespace is same as the name of the assembly) Try:

create procedure HelloWorld as external name
HelloWorld.[HelloWorld.SQLCLR].HelloWorld

Look at project properties in VS and see if there is a default namespace. Either remove that or add the namespace in your external name.

Hope that helps.

Thanks,
-Vineet.

Incorrect syntax near ?. Help please

OS: Windows2k
Platform: ASP.Net 2.0.50727
DB Server: SQL Server 2000

I'm getting an error that I don't understand, could someone help me please?

In the interest of brevity I threw most of the query out (it's a somewhat long join).

--
query += "AND TOURNAMENT_ROUNDS.END_TIME BETWEEN ? AND ?";

System.Data.SqlClient.SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@.from",System.Data.SqlDbType.DateTime).Value = from;
cmd.Parameters.Add("@.to", System.Data.SqlDbType.DateTime).Value = to;

SqlDataAdapter da = new SqlDataAdapter(query, connection);
da.SelectCommand = cmd;

DataSet ds = new DataSet();

int i = 0;
try
{
connection.Open();
da.Fill(ds);

--

The da.Fill(ds) is what's throwing. I was under the impression that ADO.Net would handle the syntax so that this shouldn't be a problem. Any idea about what direction I should be looking? If you need me to post more I'll be happy to do that.

I've verified that the above is where the exception is throwing by taking out the particular AND clause, and the query worked as expected.

Thanks for the help,

Michael

Micael

Check out the article below there is an example of using sqlCommand Parameters and building up some sql that should get you on your way

http://www.knowdotnet.com/articles/dynamicsqlparameters.html

HTH

|||

Thank you, that solved my problem.

Do you have any links regarding articles/tutorials on SQL Injection attacks in ASP.Net?

- Michael

sql

Incorrect syntax near ?.

Hi Guys,

I have moved my asp.net app from access db over to MS SQL 2005 DB.

And I have got a slight problem when I go to view any product

for example if I type in the url ofhttp://domain.com/catalog/Details.aspx?AdNum=1

I get this error

Server Error in '/catalog' Application.
------------------------

Incorrect syntax near '?'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '?'.

I have attached the details.aspx.

I await for some suggestions.

Thanks

Matthew

------

1<%@. Page MasterPageFile="Classy.master"Explicit="True" Language="VB" Debug="True" %>23<asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">4Ad Detail - <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />5</asp:Content>67<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >89<script runat="server">10Sub Page_Load(ByVal SenderAs Object,ByVal EAs EventArgs)11If Not IsPostBackThen12 If Request.QueryString("AdNum") =""Then13 Response.Redirect("default.aspx")14End If15 EditLink.NavigateUrl ="confirm.aspx?AdNum=" & Request.QueryString("AdNum")16End If17 End Sub1819 Protected Sub DetailsView1_PageIndexChanging(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewPageEventArgs)2021End Sub22</script>2324  25 <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"26 CellPadding="4" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" ForeColor="#333333"27 GridLines="None" Height="65px" Width="100%" Font-Names="Arial" Font-Size="8pt" OnPageIndexChanging="DetailsView1_PageIndexChanging">28 <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />29 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />30 <Fields>31 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />32 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />33 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />34 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />35 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />36 <asp:TemplateField HeaderText="Email">37 <ItemTemplate>38 <asp:HyperLink ID="HyperLink1" runat="server" Text=Email NavigateUrl='<%# Eval("Email", "mailto:{0}") %>' />39 </ItemTemplate>40</asp:TemplateField>41 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />42 </Fields>43 </asp:DetailsView>44<p><i>To respond to this ad, just click the email address45above to send the poster46 a message.</i></p>47If you created this ad, you can48<asp:hyperlink id="EditLink" runat="server" >edit or delete it.</asp:hyperlink> <br>49 <asp:SqlDataSource ID="SqlDataSource1" runat="server"50 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"51 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"52 SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = ?)">53 <SelectParameters>54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />55 </SelectParameters>56 </asp:SqlDataSource>5758</asp:content>

When you use SQLDataSource, you need to use the named parameter instead of the "?" which is correct when you were using Access db. Change your SelectCommand to: SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = @.AdNum)">|||

Thanks for your help, it now works.

|||

This piece of code is suppose to allow me to edit / delete records, I can update the info and press update but it doesn't update the database. And I can press Delete record and it doesn't delete the record out of the database.

I don't get any error messages.

Below is the code:

1<%@. Page MasterPageFile="Classy.master"Explicit="True" Language="VB" Debug="True" %>2<%@. ImportNamespace="System.Data" %>3<%@. ImportNamespace="System.Data.SqlClient" %>45<asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">6Edit Ad</asp:Content>78<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >910<script runat="server">11Protected Sub DetailsView1_ItemUpdated(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewUpdatedEventArgs)12 Response.Redirect("default.aspx")13End Sub1415 Protected Sub DetailsView1_ItemDeleted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewDeletedEventArgs)16 Response.Redirect("default.aspx")17End Sub1819 Protected Sub DetailsView1_ItemCommand(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.DetailsViewCommandEventArgs)20If e.CommandName ="Cancel"Then21 Response.Redirect("default.aspx")22End If23 End Sub24</script>2526To make changes, click Edit, make your changes, then click Update.To delete27 this ad, just click the Delete button.28 <br />29 <br />30<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="100%" AutoGenerateRows="False" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None" OnItemUpdated="DetailsView1_ItemUpdated" OnItemDeleted="DetailsView1_ItemDeleted" OnItemCommand="DetailsView1_ItemCommand">31 <Fields>32 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />33 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />34 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />35 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />36 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />37 <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />38 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />39 <asp:BoundField DataField="UserPassword" HeaderText="UserPassword" SortExpression="UserPassword" />40 <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" />41 </Fields>42 <RowStyle BackColor="#FFFBD6" />43 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />44</asp:DetailsView>45 46<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"47 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"48 DeleteCommand="DELETE FROM [Ads] WHERE [AdNum] = @.AdNum AND [Title] = @.Title AND [Category] = @.Category AND [Description] = @.Description AND [Price] = @.Price AND [Phone] = @.Phone AND = @.Email AND [State] = @.State AND [UserPassword] = @.UserPassword"49 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"50 SelectCommand="SELECT [AdNum], [Title], [Category], [Description], [Price], [Phone],, [State], [UserPassword] FROM [Ads] WHERE ([AdNum] = @.AdNum)"51 UpdateCommand="UPDATE [Ads] SET [Title] = @.Title, [Category] = @.Category, [Description] = @.Description, [Price] = @.Price, [Phone] = @.Phone, = @.Email, [State] = @.State, [UserPassword] = @.UserPassword WHERE [AdNum] = @.AdNum AND [Title] = @.Title AND [Category] = @.Category AND [Description] = @.Description AND [Price] = @.Price AND [Phone] = @.Phone AND = @.Email AND [State] = @.State AND [UserPassword] = @.UserPassword">5253 <SelectParameters>54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />55 </SelectParameters>56 <DeleteParameters>57 <asp:Parameter Name="original_AdNum" Type="Int32" />58 <asp:Parameter Name="original_Title" Type="String" />59 <asp:Parameter Name="original_Category" Type="String" />60 <asp:Parameter Name="original_Description" Type="String" />61 <asp:Parameter Name="original_Price" Type="Decimal" />62 <asp:Parameter Name="original_Phone" Type="String" />63 <asp:Parameter Name="original_Email" Type="String" />64 <asp:Parameter Name="original_State" Type="String" />65 <asp:Parameter Name="original_UserPassword" Type="String" />66 </DeleteParameters>67 <UpdateParameters>68 <asp:Parameter Name="Title" Type="String" />69 <asp:Parameter Name="Category" Type="String" />70 <asp:Parameter Name="Description" Type="String" />71 <asp:Parameter Name="Price" Type="Decimal" />72 <asp:Parameter Name="Phone" Type="String" />73 <asp:Parameter Name="Email" Type="String" />74 <asp:Parameter Name="State" Type="String" />75 <asp:Parameter Name="UserPassword" Type="String" />76 <asp:Parameter Name="original_AdNum" Type="Int32" />77 <asp:Parameter Name="original_Title" Type="String" />78 <asp:Parameter Name="original_Category" Type="String" />79 <asp:Parameter Name="original_Description" Type="String" />80 <asp:Parameter Name="original_Price" Type="Decimal" />81 <asp:Parameter Name="original_Phone" Type="String" />82 <asp:Parameter Name="original_Email" Type="String" />83 <asp:Parameter Name="original_State" Type="String" />84 <asp:Parameter Name="original_UserPassword" Type="String" />85 </UpdateParameters>8687 </asp:SqlDataSource>8889</asp:content>

I appricate your help

Thanks Matthew

|||

Hi,

You can get information through these links:

http://www.asp.net/learn/dataaccess/tutorial50vb.aspx?tabid=63

http://forums.asp.net/thread/1172520.aspx

Incorrect syntax near '?'

Hello. When I run my application (it's used to place orders) in VB.net I get the following message: "Failed to complete order! Reason: Incorrect syntax near 'champnr'. Incorrect syntax near '?'. At System.Data.Sqlclient.Sqlcommand.ExecuteReader(CommandBehaviour cmdBehaviour, Runbehavior runbehavior, Boolean returnStream)
Here is a part of the code:
Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("beskrivning", System.Data.SqlDbType.NVarChar, 0, "beskrivning"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestallarnr", System.Data.SqlDbType.Int, 0, "bestallarnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestdatum", System.Data.SqlDbType.DateTime, 0, "bestdatum"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bolagsnr", System.Data.SqlDbType.Int, 0, "bolagsnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("champnr", System.Data.SqlDbType.NVarChar, 255, "champnr"))
Any ideas? I'm not using any stored procedures. Maybe I should?
Hi,

Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (@.beskrivning, @.bestallarnr, @.bestdatum, @.bolagsnr, @.champnr, @.costcenter, @.doknr, @.doktypnr, @.ordernr, @.projekt, @.timenr, @.ttkommentar, @.volvokomment)

Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add("@.beskrivning", System.Data.SqlDbType.NVarChar)

and so on...

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Incorrect syntax near ''?''

Hello. When I run my application (it's used to place orders) in VB.net I get the following message: "Failed to complete order! Reason: Incorrect syntax near 'champnr'. Incorrect syntax near '?'. At System.Data.Sqlclient.Sqlcommand.ExecuteReader(CommandBehaviour cmdBehaviour, Runbehavior runbehavior, Boolean returnStream)
Here is a part of the code:
Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("beskrivning", System.Data.SqlDbType.NVarChar, 0, "beskrivning"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestallarnr", System.Data.SqlDbType.Int, 0, "bestallarnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bestdatum", System.Data.SqlDbType.DateTime, 0, "bestdatum"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("bolagsnr", System.Data.SqlDbType.Int, 0, "bolagsnr"))
Me.NewOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("champnr", System.Data.SqlDbType.NVarChar, 255, "champnr"))
Any ideas? I'm not using any stored procedures. Maybe I should?
Hi,

Me.NewOrder.CommandText = "INSERT INTO TTOrder (beskrivning, bestallarnr, bestdatum, bolagsnr, champnr, " & _
"costcenter, doknr, doktypnr, ordernr, projekt, timenr, ttkommentar, volvokomment" & _
"ar) VALUES (@.beskrivning, @.bestallarnr, @.bestdatum, @.bolagsnr, @.champnr, @.costcenter, @.doknr, @.doktypnr, @.ordernr, @.projekt, @.timenr, @.ttkommentar, @.volvokomment)

Me.NewOrder.Connection = Me.sqlOrderDb
Me.NewOrder.Parameters.Add("@.beskrivning", System.Data.SqlDbType.NVarChar)

and so on...

HTH, Jens Suessmeyer.

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

This post was very helpful to me, thanks

incorrect syntax after server up for a few days

Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

--
Exception occurred System.Runtime.InteropServices.COMException (0x80040E14): Line 1: incorrect syntax near 'CDO'.
at ADODB.ConnectionClass.Execute (String CommandText, Object& RecordsAffected, Int32 Options)
--

There are a few applications in the server. If certain service is stopped, my program continues to run. So I am sure that the MSSQL connections have been taken up, which causes the error. How to prove it? And is there any way to reserve some DB connections to a particular application only?

Thanks for any hint!

I attach my codes below. Anything wrong with the way that I handled the ADODB?

------------------
Public Sub SendAllEmails()
Try
Dim cn As ADODB.Connection = openConn()
Dim rs As ADODB._Recordset
Dim rs2 As ADODB._Recordset
sqlstmt = "select * FROM EMAILTABLE"
rs = cn.Execute(sqlstmt)

While Not rs.EOF

Dim MAIL_ADD_USED As String = rs.Fields("MAIL_ADD_USED").Value.ToString

sqlstmt = "select * from NAMETABLE where EMAIL = '" & MAIL_ADD & "'"
rs2 = cn.Execute(sqlstmt)

If Not rs2.EOF And ErrMsg = "" Then

ErrMsg = SendMail(MAIL_ADD, REPORT_TITLE)

If Not ErrMsg Is Nothing And ErrMsg.Equals("Success") Then
MAIL_DATE_SENT = Now.ToString
MAIL_STATUS = "S"

'wait to make sure the email is sent
System.Threading.Thread.Sleep(1000 * 30)
Else
MAIL_STATUS = "F"
End If

End If

rs2.Close()

sqlstmt = "update EMAILTABLE set " & _
" MAIL_STATUS = " & MAIL_STATUS & "," & _
" MAIL_ERRMSG = null" & _
" where EMAIL = " & MAIL_ADD

cn.Execute(sqlstmt)

rs.MoveNext()

End While

rs.Close()
cn.Close()

Catch e As Exception
EventLog1.WriteEntry("Exception: " & e.ToString)
End Try

End Sub
----------------------Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

--

Are you using VB.Net? Then how come Recordset come into existence...:S
See the BOL to use VB.net (ADO.Net)|||Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

Thanks.|||Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

Thanks.

Check these...
sending mail (http://www.c-sharpcorner.com/UploadFile/sushmita_kumari/SendingMail101062006054220AM/SendingMail1.aspx?ArticleID=91ece6d8-eaaf-41ab-ac6f-533dc215eacf)

Ado.net stored proc use (http://aspalliance.com/673_CodeSnip_Calling_a_Stored_Procedure_from_ASPNE T_20)

All about Ado.net (http://aspalliance.com/articles/LearnADONET.aspx)

And you can always use Profiler to check the status of your server.And to kill process check this
Kill Process (http://msdn2.microsoft.com/en-us/library/ms173730.aspx)

Inconistent time out error during update operation.

Using our ASP.net application we are getting inconsistent results when we are trying to update a table with more than 15 update queries sequentially.

We are using Merge replication in our SQL server database. On updating one row, it invokes a trigger to update another table and one more trigger for the merge replication. At only few
instances, the update operation fails and it reports "Time out expired" error.

We are not able to simulate this error in the Test environment since it does not have replication. Can any one help me in resolving this issue.

Please advise .

Hi Krithi,

Is this SQL 2000 or SQL 2005?
Would the updates be touch a lot of rows? If so, I can understand the timeout because, it seems like there are multiple triggers here and also replication adds its own triggers on the tables. That bing the case, if the number of rows touched are large, timeouts can occur.

And are you doing the update operations through isqlw/Management Studio?
If so, can you increase the timeout value in the options in isqlw/Management Studio and try again?

Incompatible data types in SQL EXP 2005

I'm trying to read a data table with all text fields using C# in ado.net. However, when my query runs, it always returns this error:

"The data types text and varchar are incompatible in the equal to operator. "

The SQL statment is "SELECT field1, field2 FROM table WHERE field1 = 'value'"

If I leave out the WHERE clause, it runs fine. There are NO varchar fields in my entire table (It's a test table). I've tried using both parameter objects and Convert (See commented section) with no luck. A packet trace shows the SQL string is being delivered in tact and the SQL server is returning the error. I don't know if it's a C# client issue, or a SQL Server 2005 Exp issue. The table was created using MS SQL Server Management Studio Express I'm currently using the SqlClient object like this:

<script runat="server">
DataSet dsData = new DataSet();
DataTable dtData = new DataTable();

void Page_Load()
{
string strConn = ConfigurationSettings.AppSettings["authstr"];
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

//string strSQL = "SELECT password_fld, pharmacy_fld FROM Esker_Tbl WHERE CONVERT(text, username_fld ) = '@.prmuser'";
//SqlParameter spuser = new SqlParameter("@.prmuser", SqlDbType.Text);

//string struser = "bobhope";
//spuser.Value = struser;
try
{
SqlConnection Conn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSQL, Conn);
SqlDataAdapter Adp = new SqlDataAdapter(Cmd);

Conn.Open();
Adp.Fill(dsData, "USERS1");
dtData = dsData.Tables["USERS1"];
dgData.DataSource = dtData;
dgData.DataBind();
dgData.Visible = true;

}
catch (Exception ex)
{
string strmessage = "";
strmessage = "Unable to access database: " + ex.Message;
lblMessage.Text = strmessage;
}
return;
}

Have you pulled out the query and tried to run it in Management Studio to break out the conversion type differences? I don't have your data structure so I'm not sure what the issue is.

If you're trying to compare two text data types, you should use the CONTAINS or other search parameters. In fact, the TEXT data type is being phased out in favor of nvarchar(max), which can hold gigs of data per row. Check out this article for more:

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

|||

Hi Mark:

Not sure if this is an error caused by copy-paste the code into the forum, but if the code sample you provided here is correct, then it seems like you should've seen a compile error here:

string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

There is an extra " .

if that's an copy-paste typo, could you please provide what is the data type declared for User_ID in User_ID table?

thanks

Incompatible data types in SQL EXP 2005

I'm trying to read a data table with all text fields using C# in ado.net. However, when my query runs, it always returns this error:

"The data types text and varchar are incompatible in the equal to operator. "

The SQL statment is "SELECT field1, field2 FROM table WHERE field1 = 'value'"

If I leave out the WHERE clause, it runs fine. There are NO varchar fields in my entire table (It's a test table). I've tried using both parameter objects and Convert (See commented section) with no luck. A packet trace shows the SQL string is being delivered in tact and the SQL server is returning the error. I don't know if it's a C# client issue, or a SQL Server 2005 Exp issue. The table was created using MS SQL Server Management Studio Express I'm currently using the SqlClient object like this:

<script runat="server">
DataSet dsData = new DataSet();
DataTable dtData = new DataTable();

void Page_Load()
{
string strConn = ConfigurationSettings.AppSettings["authstr"];
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

//string strSQL = "SELECT password_fld, pharmacy_fld FROM Esker_Tbl WHERE CONVERT(text, username_fld ) = '@.prmuser'";
//SqlParameter spuser = new SqlParameter("@.prmuser", SqlDbType.Text);

//string struser = "bobhope";
//spuser.Value = struser;
try
{
SqlConnection Conn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSQL, Conn);
SqlDataAdapter Adp = new SqlDataAdapter(Cmd);

Conn.Open();
Adp.Fill(dsData, "USERS1");
dtData = dsData.Tables["USERS1"];
dgData.DataSource = dtData;
dgData.DataBind();
dgData.Visible = true;

}
catch (Exception ex)
{
string strmessage = "";
strmessage = "Unable to access database: " + ex.Message;
lblMessage.Text = strmessage;
}
return;
}

Have you pulled out the query and tried to run it in Management Studio to break out the conversion type differences? I don't have your data structure so I'm not sure what the issue is.

If you're trying to compare two text data types, you should use the CONTAINS or other search parameters. In fact, the TEXT data type is being phased out in favor of nvarchar(max), which can hold gigs of data per row. Check out this article for more:

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

|||

Hi Mark:

Not sure if this is an error caused by copy-paste the code into the forum, but if the code sample you provided here is correct, then it seems like you should've seen a compile error here:

string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

There is an extra " .

if that's an copy-paste typo, could you please provide what is the data type declared for User_ID in User_ID table?

thanks

Including MSDE in Windows package, containing C# app

Hi,
I am trying to create a windows installer package, that will install:
- .NET framework
- MSDE
- My app..
Can anyone offer some advice, or point me in the right direction.
How would the package know if .NET framework is already installed, or if
MSDE is already installed.
Any advice appreciated
Thanks
Paul,
There is a deployment tool kit for MSDE.
http://www.microsoft.com/sql/msde/te...ployreskit.asp
If you use windows installer included with visual studio. It will
automatically add
an install dependency for the .net framework for a c# program.
Karl
"Paul Aspinall" wrote:

> Hi,
> I am trying to create a windows installer package, that will install:
> - .NET framework
> - MSDE
> - My app..
> Can anyone offer some advice, or point me in the right direction.
> How would the package know if .NET framework is already installed, or if
> MSDE is already installed.
> Any advice appreciated
> Thanks
>
>

In VS2005 where is Buisness Intelligence Project Option

Dear All,

I am using Visual studio .Net 2005. and i want to create report using sql server reporting services
for sql server 2000.
there is Buisness Intelligence Projects option in Visual studio .Net 2003 but this option
is not present in my vs2005 editor.
where is it exactaly resides or is there any way to do that.

Hi,

VS2005 does not have BI projects. These items are now part of SQL2005. You can install BI items from SQL2005 setup.

In the pie chart,how to change the color for every piece?

I work with .NET 2005.

thank you ...

Brian Weckler has a post on using a custom chart palet:

First create a custom function to return a series color based on the group value. To be consistent, the function should use a hash table to map the same value to the same color:

Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
If mapping.ContainsKey(groupingValue) Then
Return mapping(groupingValue)
End If
Dim c As String = colorPalette(count Mod colorPalette.Length)
count = count + 1
mapping.Add(groupingValue, c)
Return c
End Function

Create your chart and set the series value appropriately. Hide the legend in the chart properties dialog. Now create a table with with two columns and a single row grouped by the group expression used as the graph series. Set the color of the data point in the chart as well as the background color of the first column in the table to:

=Code.GetColor(Fields!Series.Value)

http://blogs.msdn.com/bwelcker/archive/2005/05/20/perfect-from-now-on-custom-chart-legends.aspx

|||thank you! I'll try it .|||Only problem with this solutions is that e.g. if you use this code on a bar chart the color of the bar doesn't matches the color in the chart legend. I wonder why. I've haven't if this is the same with the pie charts.

In the pie chart,how to change the color for every piece?

I work with .NET 2005.

thank you ...

Brian Weckler has a post on using a custom chart palet:

First create a custom function to return a series color based on the group value. To be consistent, the function should use a hash table to map the same value to the same color:

Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
If mapping.ContainsKey(groupingValue) Then
Return mapping(groupingValue)
End If
Dim c As String = colorPalette(count Mod colorPalette.Length)
count = count + 1
mapping.Add(groupingValue, c)
Return c
End Function

Create your chart and set the series value appropriately. Hide the legend in the chart properties dialog. Now create a table with with two columns and a single row grouped by the group expression used as the graph series. Set the color of the data point in the chart as well as the background color of the first column in the table to:

=Code.GetColor(Fields!Series.Value)

http://blogs.msdn.com/bwelcker/archive/2005/05/20/perfect-from-now-on-custom-chart-legends.aspx

|||thank you! I'll try it .|||Only problem with this solutions is that e.g. if you use this code on a bar chart the color of the bar doesn't matches the color in the chart legend. I wonder why. I've haven't if this is the same with the pie charts.

In SQLServer SI in ORACLE NO

Questo Statement in SQL Server funziona.
In Oracle PL/SQL se lo lancio funziona
Quando lo devo far funzionare da Vb.net mi si pianta e non va avanti . Sta
l a pensare.
Come mai ?

UPDATE proc_azienda
SET cod_fase_sign = (SELECT MAX(pfa.COD_FASE)
FROM PROC_FASE_RIGA pfa
WHERE (COD_GRUPPO = 'x09') AND
((TIP_DATI = 'I') OR (TIP_DATI = 'S')) AND
pfa.cod_processo = '02')
WHERE cod_gruppo = 'x09' AND
cod_processo = '02'

C' un'altro Statement:

UPDATE proc_azienda
SET cod_fase_sign = Fase
FROM (SELECT ep.cod_processo,
app.cod_azienda,
MAX(cast(ep.cod_fase as int)) as Fase
FROM esp_proc_prospetti ep,
APP_PROSP_AZ_8377 app,
(SELECT pa.cod_processo,
pa.cod_azienda,
pa.cod_fase_sign
FROM proc_azienda pa
WHERE pa.cod_gruppo = 'x09' and
pa.cod_processo = '05' and
pa.cod_fase_sign is null
GROUP BY pa.cod_processo,
pa.cod_azienda,
pa.cod_fase_sign) pnull
WHERE ep.cod_gruppo = 'x09' and
ep.cod_processo = app.cod_processo and
ep.cod_processo = pnull.cod_processo and
ep.cod_prospetto = app.cod_prospetto
GROUP BY ep.COD_PROCESSO,
app.COD_AZIENDA) FaseAzienda ,
proc_azienda pa
WHERE pa.cod_gruppo = 'x09' and
pa.cod_processo = FaseAzienda.cod_processo and
pa.cod_azienda = FaseAzienda.cod_azienda
Questa sintassi sembra regolare per SQL Server ma non per ORACLE.
Come deve essere ... ?

Grazie mille

S.Hi
Opinione di I can't capisco questo, essendo monolingue e non italiano!! Il
pesce de Babele non che utile neanche, ma ho pensato la prova di I'd!
La clausola del FROM nella dichiarazione del UPDATE non sostenuta nel
Oracle.
L'invio del DDL per il vostro TABLE della tabella structures(CREATE
TABLE...) ed i dati di esempio (come dichiarazione del INSERT) pu aiutare.
Ad una congettura desiderate qualcosa come:
UPDATE proc_azienda
SET cod_fase_sign = (SELECT MAX(pfa.COD_FASE)
FROM PROC_FASE_RIGA pfa
WHERE (pfa.COD_GRUPPO = proc_azienda.cod_gruppo) AND
(TIP_DATI = 'I' OR TIP_DATI = 'S') AND
pfa.cod_processo = proc_azienda.cod_processo)
WHERE cod_gruppo = 'x09' AND
cod_processo = '02'
Rimuova il
WHERE cod_gruppo = 'x09' AND cod_processo = '02'
al fiammifero su altri valori.
La vostra seconda dichiarazione lo ha perso completamente, ma quando ottiene
che complicato io trova solitamente che il metodo errato!
John

Hi

I can't say I understand this, being monolingual and not Italian!! Babel
Fish is not that helpful either, but I thought I'd try!

The FROM clause in the UPDATE statement is not supported in Oracle.

Posting the DDL for your table structures(CREATE TABLE...) and example data
(as INSERT statements) may help..

At a guess you want something like:

UPDATE proc_azienda
SET cod_fase_sign = (SELECT MAX(pfa.COD_FASE)
FROM PROC_FASE_RIGA pfa
WHERE (pfa.COD_GRUPPO = proc_azienda.cod_gruppo) AND
(TIP_DATI = 'I' OR TIP_DATI = 'S') AND
pfa.cod_processo = proc_azienda.cod_processo)
WHERE cod_gruppo = 'x09' AND
cod_processo = '02'
Remove WHERE cod_gruppo = 'x09' AND
cod_processo = '02' to match on other values.
Your second statement lost me totally, but when it gets that complicated it
I usually find that it is the wrong approach!
John
"Saimon" <saimon181072@.supereva.it> wrote in message
news:cgupi0$kss$1@.newsreader.mailgate.org...
> Questo Statement in SQL Server funziona.
> In Oracle PL/SQL se lo lancio funziona
> Quando lo devo far funzionare da Vb.net mi si pianta e non va avanti . Sta
> l a pensare.
> Come mai ?
> UPDATE proc_azienda
> SET cod_fase_sign = (SELECT MAX(pfa.COD_FASE)
> FROM PROC_FASE_RIGA pfa
> WHERE (COD_GRUPPO = 'x09') AND
> ((TIP_DATI = 'I') OR (TIP_DATI = 'S')) AND
> pfa.cod_processo = '02')
> WHERE cod_gruppo = 'x09' AND
> cod_processo = '02'
> C' un'altro Statement:
> UPDATE proc_azienda
> SET cod_fase_sign = Fase
> FROM (SELECT ep.cod_processo,
> app.cod_azienda,
> MAX(cast(ep.cod_fase as int)) as Fase
> FROM esp_proc_prospetti ep,
> APP_PROSP_AZ_8377 app,
> (SELECT pa.cod_processo,
> pa.cod_azienda,
> pa.cod_fase_sign
> FROM proc_azienda pa
> WHERE pa.cod_gruppo = 'x09' and
> pa.cod_processo = '05' and
> pa.cod_fase_sign is null
> GROUP BY pa.cod_processo,
> pa.cod_azienda,
> pa.cod_fase_sign) pnull
> WHERE ep.cod_gruppo = 'x09' and
> ep.cod_processo = app.cod_processo and
> ep.cod_processo = pnull.cod_processo and
> ep.cod_prospetto = app.cod_prospetto
> GROUP BY ep.COD_PROCESSO,
> app.COD_AZIENDA) FaseAzienda ,
> proc_azienda pa
> WHERE pa.cod_gruppo = 'x09' and
> pa.cod_processo = FaseAzienda.cod_processo and
> pa.cod_azienda = FaseAzienda.cod_azienda
> Questa sintassi sembra regolare per SQL Server ma non per ORACLE.
> Come deve essere ... ?
> Grazie mille
> S.