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 using IF statement

Hi,

I'm new to SQL Server Programming, I work with ASP a lot, but lately
I've been trying to create Stored Procedures, etc. I'm having a
problem writing a simple IF statement.. I don't seem to understand why
it's giving me this error. I've search around on Google Groups, but I
still don't get it.

=================
USE msdb

IF NOT EXISTS (SELECT * FROM sysjobs WHERE name = 'Scheduled Nightfax')

END
=================

My error is:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'END'.

Thanks for any help.you need a BEGIN for every END
example

DECLARE @.v BIT
SELECT @.v = 1

IF @.v = 1
BEGIN
select 'yes'
END
ELSE
BEGIN
select 'No'
END

Or without begin...end
IF @.v = 1
select 'yes'
ELSE
select 'No'

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I played with it a bit and I got the result I was looking for.

==============
USE msdb
DECLARE @.JobName varchar(255)
SELECT @.JobName = name FROM sysjobs WHERE name = 'Scheduled Nightfax'

IF @.JobName = 'Scheduled Nightfax'
PRINT 'YES'
ELSE
PRINT 'NO'
==============

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 ?. 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

inconsistent retrieval of data

I have created a sql 2000 db and imported my tables from an access database. My web site is using asp. I am getting inconsistent results when I retrieve data from the database. Even though I can see the data in the database some fields will not retrieve to the web site. When I go to the database and erase the field and then create a new field and then put data in, I will then be able to retrieve the data at the web site. The problem is some pages will be able to retrieve while others won't. Also some fields will return data while others won't. The web site worked fine with access.

Does anyone know what may be causing this?

Thank you in advance.Betcha got spaces in the column names...

or at least unique chars like ? in them

Can you show us some code?|||There are no spaces that I can see or unique chars.

RS.Open "Select * FROM tProjects where playCode = '" & playCode & "'", Conn

filePath = RS("filePath")
myVideo = Split(filePath, ",", -1, 1)

RS4.Open "Select * FROM tVideosViewed WHERE hitID="&hitID&" AND contentID="&myVideo(0), Conn

When the RS4.Open fires then I will receive an error and when I investigate it is because the array is blank and filePath is blank even though there is a value in the database.

Originally posted by Brett Kaiser
Betcha got spaces in the column names...

or at least unique chars like ? in them

Can you show us some code?|||Why not create and call stored procedures?

It'll be more effecient as well

Just curious though, do you have sql server client tools?

Can you type that same select in a query analyzer window?

What would it look like?

Also you can look at profiler to see what's being passed to the server..

my guess is the statement is malformed when it gets passed...

do you know how to start a trace?

I still thinks sprocs are the way to go in any case...|||We found the problem. On certain pages we did not have all the locktypes specified. So SQL server gave us random records back. Once That we specified the locktypes it worked fine.

Thanks for suggestions. Luckily it was an easy fix for us

Originally posted by Brett Kaiser
Why not create and call stored procedures?

It'll be more effecient as well

Just curious though, do you have sql server client tools?

Can you type that same select in a query analyzer window?

What would it look like?

Also you can look at profiler to see what's being passed to the server..

my guess is the statement is malformed when it gets passed...

do you know how to start a trace?

I still thinks sprocs are the way to go in any case...|||Help me out here...what did you do exactly?|||Originally posted by Brett Kaiser
Help me out here...what did you do exactly?

We didn't have the locktype and cursor type set for all the recordsets that we had open. There was multiple recordsets on each page. When I added:

RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic

RS2.CursorType = adOpenDynamic
RS2.LockType = adLockOptimistic

For each recordset then we received consistent results from SQL server. It was a small thing but access let us get away with it but SQL wouldn't.

Looks like we have found most of the differences from access to SQL but we need to test it for about a week.

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?

Include File Concept

Hi All
I wanted to check if there is any feature like the INCLUDE file concept in ASP.
I have set of variables that I need to declare in each of the Stored Procedures by default, so that I can maintain this list in single place, dont have to worry about changing the list for every SP in the database.
Does SQL Server provide any such feature.
If not, is this planned in YUKON?You could try creating a template for use in Query Analyzer. You can modify
one of the existing procedure and save it as another name. See BOL:Using
Templates in Query Analyzer for more information.
"Prasanna" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:FD16045E-A2F1-4534-88A2-65DD471DA1EE@.microsoft.com...
> Hi All
> I wanted to check if there is any feature like the INCLUDE file concept in
ASP.
> I have set of variables that I need to declare in each of the Stored
Procedures by default, so that I can maintain this list in single place,
dont have to worry about changing the list for every SP in the database.
> Does SQL Server provide any such feature.
> If not, is this planned in YUKON?|||Hello,
Templates are boilerplate files containing SQL scripts that help you create
objects in the database. There are however, unable to help maintain the set
of variables in a single place.
Currently, SQL Server does not provide this feature you require. As I
understand, SQL Server Yukon will provide .NET Programming Features, but
because Yukon is not a released version of SQL Server, we are unable to
guarantee it will have the feature like the INCLUDE file concept in ASP.
Thanks for understanding.
For additional information regarding .NET programming Features, please
refer to the following article:
SQL Server Yukon: .NET Programming Features
http://server1.msn.co.in/sp03/teched/pop5.html
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
Thanks for using MSDN newsgroup
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.