Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, Mefisto
Can you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, MefistoCan you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, MefistoCan you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with Insert trigger

I have an insert trigger that works 99.999% of the time. The trigger is
fired via a dts process that runs every 10 minutes which inserts data into a
ForecastTonnageChanges table. Based on changes to this
ForecastTonnageChanges, I want to update the live Forecast table. The
trigger fires this event. My basic logic in the trigger is:
Delete from Forecast
where exists (select *
from inserted
where <joining key columns> )
Insert into Forecast
select ...
from inserted
When this doesn't work, I am ending up with extra records in my forecast
table. I'm wondering if another insert has happened and between the delete
and the insert in the trigger, an additional row is in the inserted table.
Is this possible? I would think implied locking via the trigger would
prevent this. But, I'm grasping for straws here.
If you have ideas on a more reliable way to implement this... please share.Hi
A trigger is fired for each statement, therefore you will not get extra rows
in the inserted/deleted tables.
As you don't give DDL and all the trigger code it is hard to say where you
are going wrong, but you may want to use profiler to see what is happening.
You should also implement error handling to make sure that the statement
succeeds and all the relivent statements in the transaction are rolled back
if a failure occurs.
John
"Erin" wrote:
> I have an insert trigger that works 99.999% of the time. The trigger is
> fired via a dts process that runs every 10 minutes which inserts data into
a
> ForecastTonnageChanges table. Based on changes to this
> ForecastTonnageChanges, I want to update the live Forecast table. The
> trigger fires this event. My basic logic in the trigger is:
> Delete from Forecast
> where exists (select *
> from inserted
> where <joining key columns> )
> Insert into Forecast
> select ...
> from inserted
> When this doesn't work, I am ending up with extra records in my forecast
> table. I'm wondering if another insert has happened and between the delet
e
> and the insert in the trigger, an additional row is in the inserted table.
> Is this possible? I would think implied locking via the trigger would
> prevent this. But, I'm grasping for straws here.
> If you have ideas on a more reliable way to implement this... please share.[/color
]|||Without any more information, could you have a forecast VIEW of the
most current rows in the ForecastTonnageChanges table instead of
physically shuffling all this data around? You obviously have a time
stamp on the new data, so that should be easy enough.

Problem with insert Time to database

Dear all,
I have insert the time to my database but it appear also the date by default.
This is my code in C# :
DateTime date = DateTime.Now;
int hour = date.Hour;
int minute = date.Minute;
int second = date.Second;
string requestedTime = hour+":"+minute+":"+second;
string query = "INSERT INTO workorder([timeRequest]) VALUES("'"+requestTime+"'");
in my database , the column timeRequest appear :1/1/1900 11:59:05 AM
I dont want the date by default to appear, i want only the time like : 11:59:05 AM in my database,
Anyone can help me?
Best Regards,
Moniphal

Use parameterized command to insert data to database. By example:

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO workorder(timeRequest) VALUES (@.TimeRequest)";
cmd.CommandType = CommandType.Text
cmd.Parameters.Add(new SqlParameter("@.TimeRequest",myDateTime));
cmd.ExecuteNonQuery();

problem with INSERT on ExecuteNonQuery

I'm trying to INSERT a new record into a SQL database using the command object. It works if I use an UPDATE, but every time I try an INSERT, I get "ExecuteNonQuery: Connection property has not been initialized". The query has been checked in enterprise manager direct and works fine. Has anybody got any ideas, please?

 Dim cmdSql As New SqlCommand("INSERT INTO table(blah) VALUES ('blah'), ConUsers")
 ConUsers.Open()
 cmdSql.ExecuteNonQuery()
 ConUsers.Close()

What are you using for your connection/connection string? is it exactly the same as the sub that works for the update statement?|||> is it exactly the same as the sub that works for the update statement?

yes, it is. The only thing that changes is the query itself and I've checked that on the database direct. I've even checked that servername/ASPNET has INSERT permission on that table. So I'm a bit stumped.|||Is that code copied and pasted? If so, you have a misplaced double quote. Should be like this:

 Dim cmdSql As New SqlCommand("INSERT INTO table(blah) VALUES ('blah')", ConUsers)

Terri

Problem with Insert in to IP to Microsoft Acess by ASP

Hello
Would Anyone help me '
I want insert into Microsoft Acess visitor's IP by ASP. But It is not
working
----=
=AD--
NOT WORK
<%
.=2E....
time =3D now()
ipp =3D Request.ServerVariables("remote_addr")
strSQL =3D "INSERT INTO visitor (time,ipp) VALUES ('" & time & "','" &
ipp & "')"
Set rs =3D myConnection.Execute(strSQL)
.=2E....
%>
----=
=AD--
I tested part of the ASP script then It work very well. Below:
WORK VERY WELL
<%
.=2E...
time =3D now()
strSQL =3D "INSERT INTO visitor (time) VALUES ('" & time & "')"
Set rs =3D myConnection.Execute(strSQL)
.=2E...
%>
I created Access database. There are 2 field : time and ipp. Data type
of time is date. Data tye of IPP is text.
I think: Maybe in Access database: the field IPP was problem with data
type.=20
Thank you for your help...=20
http://www.anluc.comThis should work. What error message are you getting?
I take it you know about
Request.ServerVariables("REMOTE_ADDR")This will return the ip address in asp
pages.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<minhadd@.gmail.com> wrote in message
news:1135658733.112628.33010@.o13g2000cwo.googlegroups.com...
Hello
Would Anyone help me '
I want insert into Microsoft Acess visitor's IP by ASP. But It is not
working
----_
--
NOT WORK
<%
......
time = now()
ipp = Request.ServerVariables("remote_addr")
strSQL = "INSERT INTO visitor (time,ipp) VALUES ('" & time & "','" &
ipp & "')"
Set rs = myConnection.Execute(strSQL)
......
%>
----_
--
I tested part of the ASP script then It work very well. Below:
WORK VERY WELL
<%
.....
time = now()
strSQL = "INSERT INTO visitor (time) VALUES ('" & time & "')"
Set rs = myConnection.Execute(strSQL)
....
%>
I created Access database. There are 2 field : time and ipp. Data type
of time is date. Data tye of IPP is text.
I think: Maybe in Access database: the field IPP was problem with data
type.
Thank you for your help...
http://www.anluc.com|||Thank you Hilary Cotter .
I visit your abow link : But I can not see how to INSERT INTO DB.
IF : Request.ServerVariables("REMOTE_ADDR")This will return the ip
address in asp
pages.
THEN How do I do for insert into Access DB
I need so much to insert into DB Visitor's IP.
Thank you for your help....

Problem with Historical Prediction in Sales Forecast Model

Hi,

I have built a time series model to forecast sales value

I have data from jan 2004 to jan 2006 and the sales value is

at a day level in my database. But I am aggregating it to month level in the

DSV of the mining model.

I am required to make only historical predictions using the

above model starting form jan 2004 to jan 2006 for every month.

I have set Historical_Model_Count

and Historical_Model_Gap parameter

values to 24 and 10 respectively, and trying to predict for the past few months

(PredictTImeseries(SalesValue,-1,1))

But its throwing me the following error

Error(Data Mining): A time series

prediction was requested with a start time further in the past than the

internal models of the mining model, Sales Forecast, specified in the

HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process

In fact it throws the above error irrespective of what the Historical_Model_Count and Historical_Model_Gap parameter values

are

I am not able to figure our why this problem is happening?

What should the parameter values for the above scenario?

It would also be helpful if I can get an explanation on how

these two parameters affect the historical predictions. I kind of understand

that these two parameters are important for historical predictions but don’t

know why or how.

You want the values of _Count to be 24 and _Gap to be 1. The _Count param says "make this many models", the "_Gap" param says "leave this many time slices between models". Your original parameter set was making models to 240 months (20 years!) in the past.

Note that the _Gap parameter is to be set such that you get a good idea of how the model will predict for the range that you need to predict for. For example, setting it to 1 gives you an indication of how well the model will predict the next step. Setting the parameters to _Count = 4 and _Gap=6 gives an idea of how the model will predict 6 steps into the future.

|||

Thanks Jamie,

I have few more clarification regarding time series.

Firstly

In my model the month level product sales value represented across 1^st day of every month.So that the key time column is of datetime datatype containing a sequence of dates representing the 1^st day of every month of the year.

Eg: 2006-01-01, 2006-02-01………. etc. all in (yy-mm-dd) format

But when I make prediction for next five months, though it makes monthly predictions the date part for the months are random whereas I expect the date part to be 1^st of every month.What is the reason for this and how can I overcome it.

Secondly

Predicted sales values for some time period are negative though I do not have any negative value in the training data. What is the reason for this and how can I rectify it?

Thirdly

In one of your earlier posts you had said that the time series algorithm does not have any built in time intelligence but uses the key time column as a time sequence stamp. So If have to make predictions for a particular time period where the time slice for each time period is 25 days or 50 days etc, then I understand that the input data used to train the model should be in the same time sequence.

Or

Can I specify the span of the time period according to which the prediction needs to be made?

Basically how can I use the same time series model to make monthly, yearly, quarterly, daily or predictions or for custom time period like I have mentioned above.

|||Additional questions answered in other thread

problem with exports in pdf

Hi
I am trying to export the report in pdf file with version 5.0. In pdf file
, it leaves the blank pages in between. At the same time returns half of the
information on one page and continues half on another page. For some data,
it gives acsii characters in the pdf file and when in the designer view ,
the same report looks correct.
This happens intermittently. For some data , the same RDL works fine whereas
for some, it doesnt give the proper formatting.
Please provide the solution and suggestions if any.
Thanks & Cheers,
TruptiTo keep the PDF export from leaving blank pages or having columns on one
page and others continued on the next, make sure the BODY size of the report
is not even 1 pixel more than the PAGE SIZE - (the MARGINS).
--
Adrian M.
MCP
"Trupti" <gettrupti@.hotmail.com> wrote in message
news:Oa9qP57IFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi
> I am trying to export the report in pdf file with version 5.0. In pdf
> file , it leaves the blank pages in between. At the same time returns half
> of the information on one page and continues half on another page. For
> some data, it gives acsii characters in the pdf file and when in the
> designer view , the same report looks correct.
> This happens intermittently. For some data , the same RDL works fine
> whereas for some, it doesnt give the proper formatting.
> Please provide the solution and suggestions if any.
> Thanks & Cheers,
> Trupti
>|||Dear Trupti
I am experiencing the same problems - and at least in my case it does not
have anything to do with the definition of the report width.
I have checked my report's and body's width over and over again - everything
is as it should be. And according to the piece of advice Microsoft is dishing
out setting the correct report and body width should do the trick - but it
does not. And it seems as if nobody is really working on solving this problem
because there are no better answers to be found in this newsgroup - and the
problem is known for a long time now!
According to my own experiences, reports with tables do just fine. It is
reports that contain a matrix that have this pdf-problem. We have even
istalled and tested the beta version of SP2, but with no better results. I am
at the end of my rope. I really wish somebody would come up with an
explanation and solution soon!
Greetings to all poor souls out there suffering from the same phenomenon...
"Trupti" wrote:
> Hi
> I am trying to export the report in pdf file with version 5.0. In pdf file
> , it leaves the blank pages in between. At the same time returns half of the
> information on one page and continues half on another page. For some data,
> it gives acsii characters in the pdf file and when in the designer view ,
> the same report looks correct.
> This happens intermittently. For some data , the same RDL works fine whereas
> for some, it doesnt give the proper formatting.
> Please provide the solution and suggestions if any.
> Thanks & Cheers,
> Trupti
>
>|||Send a description of the problem/issue to:
mswish@.microsoft.com
http://www.microsoft.com/mswish
That way you don't just wait for a 'solution' - you can have an active share
in encouraging Microsoft to fix the problem.
--
Adrian M.
MCP
"j schuetz" <jschuetz@.discussions.microsoft.com> wrote in message
news:B16D99CB-D4A7-48A3-9CA0-627B73C94904@.microsoft.com...
> Dear Trupti
> I am experiencing the same problems - and at least in my case it does not
> have anything to do with the definition of the report width.
> I have checked my report's and body's width over and over again -
> everything
> is as it should be. And according to the piece of advice Microsoft is
> dishing
> out setting the correct report and body width should do the trick - but it
> does not. And it seems as if nobody is really working on solving this
> problem
> because there are no better answers to be found in this newsgroup - and
> the
> problem is known for a long time now!
> According to my own experiences, reports with tables do just fine. It is
> reports that contain a matrix that have this pdf-problem. We have even
> istalled and tested the beta version of SP2, but with no better results. I
> am
> at the end of my rope. I really wish somebody would come up with an
> explanation and solution soon!
> Greetings to all poor souls out there suffering from the same
> phenomenon...
>
> "Trupti" wrote:
>> Hi
>> I am trying to export the report in pdf file with version 5.0. In pdf
>> file
>> , it leaves the blank pages in between. At the same time returns half of
>> the
>> information on one page and continues half on another page. For some
>> data,
>> it gives acsii characters in the pdf file and when in the designer view
>> ,
>> the same report looks correct.
>> This happens intermittently. For some data , the same RDL works fine
>> whereas
>> for some, it doesnt give the proper formatting.
>> Please provide the solution and suggestions if any.
>> Thanks & Cheers,
>> Trupti
>>|||I have deposited a detailed documentation with the Microsoft support of my
home country - but up to now nothing has resulted of that either.

Problem with Embedded Image

Hi,

I have a report with background embedded image. I have placed few textboxes on top of them to display data. Every time I set the location of the textbox, it is behaving strangely by rendering at one location one time and changing location next time when it is rendered. I am viewing it in PDF format.

How can I fix the location of textboxes that renders at one location all the time ?

Thanks,

Shyamkumar123

I am having this problem and others, too. I have a large background image, and I'm placing textboxes on top. I designed it with just dummy data in the textboxes, got it laid out just fine. But after putting actual data in and conditionally hiding some textboxes, the problem shows up in the Preview tab in SQL Studio and in an Adobe Acrobat export. Text boxes do not render in their correct locations.

Furthermore, I cannot get the text box to line up at all where I want it. In the Preview, a textbox will render higher up than I intend. If I move it downward in the Layout tab, the textbox renders much lower than I intend. I have my choice of rendering above or below where I want it, but I just can't render it at the correct location even using direct manipulation of the location attributes of the textbox properties. If I correct a rendering by 0.01 inches, it moves a half inch downward on the rendering and pdf.

Maybe it's a rounding error in the application with regard to the textbox locations... But I had no problems when I laid out the form with plain text in all textboxes and none of them hidden. All textboxes are set to CanGrow = False

Suggestions are welcome...

|||

Shyam,

I found a workaround to my problem. It fixes the problem of Layout not looking like Preview or Export to PDF. I had to set all textboxes to hidden = false. Then instead of making them conditionally hidden, I made them all visible but made the data conditionally empty.

Moral of the story: don't hide textboxes.

Problem with Embedded Image

Hi,

I have a report with background embedded image. I have placed few textboxes on top of them to display data. Every time I set the location of the textbox, it is behaving strangely by rendering at one location one time and changing location next time when it is rendered. I am viewing it in PDF format.

How can I fix the location of textboxes that renders at one location all the time ?

Thanks,

Shyamkumar123

I am having this problem and others, too. I have a large background image, and I'm placing textboxes on top. I designed it with just dummy data in the textboxes, got it laid out just fine. But after putting actual data in and conditionally hiding some textboxes, the problem shows up in the Preview tab in SQL Studio and in an Adobe Acrobat export. Text boxes do not render in their correct locations.

Furthermore, I cannot get the text box to line up at all where I want it. In the Preview, a textbox will render higher up than I intend. If I move it downward in the Layout tab, the textbox renders much lower than I intend. I have my choice of rendering above or below where I want it, but I just can't render it at the correct location even using direct manipulation of the location attributes of the textbox properties. If I correct a rendering by 0.01 inches, it moves a half inch downward on the rendering and pdf.

Maybe it's a rounding error in the application with regard to the textbox locations... But I had no problems when I laid out the form with plain text in all textboxes and none of them hidden. All textboxes are set to CanGrow = False

Suggestions are welcome...

|||

Shyam,

I found a workaround to my problem. It fixes the problem of Layout not looking like Preview or Export to PDF. I had to set all textboxes to hidden = false. Then instead of making them conditionally hidden, I made them all visible but made the data conditionally empty.

Moral of the story: don't hide textboxes.

Problem with DTS

Hi there.
I have the following environment:
Windows 2000 Server SP4, SQL Server 2000 Sp3.
There are some DTS which have been creating some time ago. They are
scheduled are have been runngin until now. Now the jobs fail. Can't say
what has been changed. Haven't been responsible for those systems and
those who are say they didnt change anything (same story as always ;)
...)
The DTS have been created when connecting with an Windows Domain
Account. When I connect with this Windows Account I can open and run
those DTS. But when login as local Admin directly on the server and open
Enterprise Manager with the local Admin User and try to open the DTS I
get an error:
Error Source : Microsfot OLE DB Provider for SQL Server
Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
not exist or access is denied.
The DTS is a Local Package (I have some Meta Data Service Packages ...
same there).
The simlest DTS has two SQL Server connection using a SQL User. First a
Delete on the first connection is made, then some data is copied from
connection2 to connection1.
As I said, when I login with Domain User Account I can open it, edit it,
run it .. no problems. But with the local Admin of the SQL Server ...
error.
SQL Server and SQL Agent are running as local System Accounts.
Why am I getting this error? I think it cant be the Connections in the
DTS, because there SQL Users are used which exists and work.
The Local Administartor is also a SQL System Admin, as are the Domain
Admins.
Any hints? Anyone knows where I could investigate further?
mfg
Marc Eggenberger
Someone removed the BUILTIN\Administrators login from SQL Server, as they
should have, and have replaced it with the [NT AUTHORITY\SYSTEM] group;
however, they should have followed proper Change Control policies. I'm
surprised you haven't been having more problems.
At the VERY least, you should be running the MSSQL and SQLAGENT service
under local accounts, NOT SYSTEM. And, if you can, run the services under a
Domain Account.
So you know, SYSTEM = [NT AUTHORITY]\SYSTEM and is not a Local User but a
GLOBAL AD GROUP. The members of this group are all the <Machine Name>$
server accounts that are created whenever you add a server as a Domain Member.
Next, if you use a Domain Account for the services, you can grant explicit
privleges to those accounts on other server network shares.
As you know, DTS runs in the process space and under the security context of
the user that executes the DTSRun command. When the package runs as a job,
it uses the server's memory space and the SYSTEM account because that is what
the SQLAgent is running as and where it is running.
You can not log in as SYSTEM and SYSTEM and Local Administrator are two
different security contexts. Another good reason to have an explicitly
defined account to run the SQL Server services under: you can log in to the
server as that user and "see" what the services would see when they log in.
Sincerely,
Anthony Thomas
"Marc Eggenberger" wrote:

> Hi there.
> I have the following environment:
> Windows 2000 Server SP4, SQL Server 2000 Sp3.
> There are some DTS which have been creating some time ago. They are
> scheduled are have been runngin until now. Now the jobs fail. Can't say
> what has been changed. Haven't been responsible for those systems and
> those who are say they didnt change anything (same story as always ;)
> ...)
> The DTS have been created when connecting with an Windows Domain
> Account. When I connect with this Windows Account I can open and run
> those DTS. But when login as local Admin directly on the server and open
> Enterprise Manager with the local Admin User and try to open the DTS I
> get an error:
> Error Source : Microsfot OLE DB Provider for SQL Server
> Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
> not exist or access is denied.
> The DTS is a Local Package (I have some Meta Data Service Packages ...
> same there).
> The simlest DTS has two SQL Server connection using a SQL User. First a
> Delete on the first connection is made, then some data is copied from
> connection2 to connection1.
> As I said, when I login with Domain User Account I can open it, edit it,
> run it .. no problems. But with the local Admin of the SQL Server ...
> error.
> SQL Server and SQL Agent are running as local System Accounts.
> Why am I getting this error? I think it cant be the Connections in the
> DTS, because there SQL Users are used which exists and work.
> The Local Administartor is also a SQL System Admin, as are the Domain
> Admins.
> Any hints? Anyone knows where I could investigate further?
>
> --
> mfg
> Marc Eggenberger
>

Problem with DTS

Hi there.
I have the following environment:
Windows 2000 Server SP4, SQL Server 2000 Sp3.
There are some DTS which have been creating some time ago. They are
scheduled are have been runngin until now. Now the jobs fail. Can't say
what has been changed. Haven't been responsible for those systems and
those who are say they didnt change anything (same story as always ;)
...)
The DTS have been created when connecting with an Windows Domain
Account. When I connect with this Windows Account I can open and run
those DTS. But when login as local Admin directly on the server and open
Enterprise Manager with the local Admin User and try to open the DTS I
get an error:
Error Source : Microsfot OLE DB Provider for SQL Server
Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
not exist or access is denied.
The DTS is a Local Package (I have some Meta Data Service Packages ...
same there).
The simlest DTS has two SQL Server connection using a SQL User. First a
Delete on the first connection is made, then some data is copied from
connection2 to connection1.
As I said, when I login with Domain User Account I can open it, edit it,
run it .. no problems. But with the local Admin of the SQL Server ...
error.
SQL Server and SQL Agent are running as local System Accounts.
Why am I getting this error? I think it cant be the Connections in the
DTS, because there SQL Users are used which exists and work.
The Local Administartor is also a SQL System Admin, as are the Domain
Admins.
Any hints? Anyone knows where I could investigate further?
mfg
Marc EggenbergerSomeone removed the BUILTIN\Administrators login from SQL Server, as they
should have, and have replaced it with the [NT AUTHORITY\SYSTEM] group;
however, they should have followed proper Change Control policies. I'm
surprised you haven't been having more problems.
At the VERY least, you should be running the MSSQL and SQLAGENT service
under local accounts, NOT SYSTEM. And, if you can, run the services under a
Domain Account.
So you know, SYSTEM = [NT AUTHORITY]\SYSTEM and is not a Local User but
a
GLOBAL AD GROUP. The members of this group are all the <Machine Name>$
server accounts that are created whenever you add a server as a Domain Membe
r.
Next, if you use a Domain Account for the services, you can grant explicit
privleges to those accounts on other server network shares.
As you know, DTS runs in the process space and under the security context of
the user that executes the DTSRun command. When the package runs as a job,
it uses the server's memory space and the SYSTEM account because that is wha
t
the SQLAgent is running as and where it is running.
You can not log in as SYSTEM and SYSTEM and Local Administrator are two
different security contexts. Another good reason to have an explicitly
defined account to run the SQL Server services under: you can log in to the
server as that user and "see" what the services would see when they log in.
Sincerely,
Anthony Thomas
"Marc Eggenberger" wrote:

> Hi there.
> I have the following environment:
> Windows 2000 Server SP4, SQL Server 2000 Sp3.
> There are some DTS which have been creating some time ago. They are
> scheduled are have been runngin until now. Now the jobs fail. Can't say
> what has been changed. Haven't been responsible for those systems and
> those who are say they didnt change anything (same story as always ;)
> ...)
> The DTS have been created when connecting with an Windows Domain
> Account. When I connect with this Windows Account I can open and run
> those DTS. But when login as local Admin directly on the server and open
> Enterprise Manager with the local Admin User and try to open the DTS I
> get an error:
> Error Source : Microsfot OLE DB Provider for SQL Server
> Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server doe
s
> not exist or access is denied.
> The DTS is a Local Package (I have some Meta Data Service Packages ...
> same there).
> The simlest DTS has two SQL Server connection using a SQL User. First a
> Delete on the first connection is made, then some data is copied from
> connection2 to connection1.
> As I said, when I login with Domain User Account I can open it, edit it,
> run it .. no problems. But with the local Admin of the SQL Server ...
> error.
> SQL Server and SQL Agent are running as local System Accounts.
> Why am I getting this error? I think it cant be the Connections in the
> DTS, because there SQL Users are used which exists and work.
> The Local Administartor is also a SQL System Admin, as are the Domain
> Admins.
> Any hints? Anyone knows where I could investigate further?
>
> --
> mfg
> Marc Eggenberger
>

Problem with DTS

Hi there.
I have the following environment:
Windows 2000 Server SP4, SQL Server 2000 Sp3.
There are some DTS which have been creating some time ago. They are
scheduled are have been runngin until now. Now the jobs fail. Can't say
what has been changed. Haven't been responsible for those systems and
those who are say they didnt change anything (same story as always ;)
...)
The DTS have been created when connecting with an Windows Domain
Account. When I connect with this Windows Account I can open and run
those DTS. But when login as local Admin directly on the server and open
Enterprise Manager with the local Admin User and try to open the DTS I
get an error:
Error Source : Microsfot OLE DB Provider for SQL Server
Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
not exist or access is denied.
The DTS is a Local Package (I have some Meta Data Service Packages ...
same there).
The simlest DTS has two SQL Server connection using a SQL User. First a
Delete on the first connection is made, then some data is copied from
connection2 to connection1.
As I said, when I login with Domain User Account I can open it, edit it,
run it .. no problems. But with the local Admin of the SQL Server ...
error.
SQL Server and SQL Agent are running as local System Accounts.
Why am I getting this error? I think it cant be the Connections in the
DTS, because there SQL Users are used which exists and work.
The Local Administartor is also a SQL System Admin, as are the Domain
Admins.
Any hints? Anyone knows where I could investigate further?
--
mfg
Marc EggenbergerSomeone removed the BUILTIN\Administrators login from SQL Server, as they
should have, and have replaced it with the [NT AUTHORITY\SYSTEM] group;
however, they should have followed proper Change Control policies. I'm
surprised you haven't been having more problems.
At the VERY least, you should be running the MSSQL and SQLAGENT service
under local accounts, NOT SYSTEM. And, if you can, run the services under a
Domain Account.
So you know, SYSTEM = [NT AUTHORITY]\SYSTEM and is not a Local User but a
GLOBAL AD GROUP. The members of this group are all the <Machine Name>$
server accounts that are created whenever you add a server as a Domain Member.
Next, if you use a Domain Account for the services, you can grant explicit
privleges to those accounts on other server network shares.
As you know, DTS runs in the process space and under the security context of
the user that executes the DTSRun command. When the package runs as a job,
it uses the server's memory space and the SYSTEM account because that is what
the SQLAgent is running as and where it is running.
You can not log in as SYSTEM and SYSTEM and Local Administrator are two
different security contexts. Another good reason to have an explicitly
defined account to run the SQL Server services under: you can log in to the
server as that user and "see" what the services would see when they log in.
Sincerely,
Anthony Thomas
"Marc Eggenberger" wrote:
> Hi there.
> I have the following environment:
> Windows 2000 Server SP4, SQL Server 2000 Sp3.
> There are some DTS which have been creating some time ago. They are
> scheduled are have been runngin until now. Now the jobs fail. Can't say
> what has been changed. Haven't been responsible for those systems and
> those who are say they didnt change anything (same story as always ;)
> ...)
> The DTS have been created when connecting with an Windows Domain
> Account. When I connect with this Windows Account I can open and run
> those DTS. But when login as local Admin directly on the server and open
> Enterprise Manager with the local Admin User and try to open the DTS I
> get an error:
> Error Source : Microsfot OLE DB Provider for SQL Server
> Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
> not exist or access is denied.
> The DTS is a Local Package (I have some Meta Data Service Packages ...
> same there).
> The simlest DTS has two SQL Server connection using a SQL User. First a
> Delete on the first connection is made, then some data is copied from
> connection2 to connection1.
> As I said, when I login with Domain User Account I can open it, edit it,
> run it .. no problems. But with the local Admin of the SQL Server ...
> error.
> SQL Server and SQL Agent are running as local System Accounts.
> Why am I getting this error? I think it cant be the Connections in the
> DTS, because there SQL Users are used which exists and work.
> The Local Administartor is also a SQL System Admin, as are the Domain
> Admins.
> Any hints? Anyone knows where I could investigate further?
>
> --
> mfg
> Marc Eggenberger
>

Problem with downloading SQL Server 2005 Express

Hello,

I have a problem downloading Microsoft SQL Server 2005 Express Edition with Advanced Services.

Every time I download the complete file (234 mb) I get an error that the downloaded file is corrupt. I've tried also downloading it on other locations but every time the file is corrupt.

Is there another way to obtain this version of SQL server 2005 ?

Where are you downloading from? The update is on Microsoft Update or the Microsoft download center?

Thanks,

Peter Saddow

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.developersdex.com ***Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:
>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

Problem with DateAdd & GetDate()

Hi,
I've been struggling trying to figure out this code to no avail. I need to
query a date time field on an external database to give me data if the
Closed_Time has changed within the last day. The Closed_Time field looks
like this 9/1/2005 11:59:00 AM. The code I've been attempting to use is
(Closed_Time > DATEADD(day, - 7, GETDATE()) and when that is executed a
Lexical Element error message is returned. I've searched on the boards for
other variations of this code and none have worked so far.
I'm hoping someone can shed a little light on it for me.
Thanks.What error message?
Also look up DATEDIFF in Books Online, if you haven't already.
ML
http://milambda.blogspot.com/|||Whats the exact error message ?
HTH, Jens Suessmeyer.|||ML wrote:
> What error message?
> Also look up DATEDIFF in Books Online, if you haven't already.
>
Why would DATEDIFF be relevant?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> I've been struggling trying to figure out this code to no avail. I need
> to
> query a date time field on an external database to give me data if the
> Closed_Time has changed within the last day. The Closed_Time field looks
> like this 9/1/2005 11:59:00 AM. The code I've been attempting to use is
> (Closed_Time > DATEADD(day, - 7, GETDATE()) and when that is executed a
> Lexical Element error message is returned. I've searched on the boards
> for
> other variations of this code and none have worked so far.
Assume the current date/time is 20060126 11:07:13.003. What does the
evaluation of the expression "DATEADD(day, - 7, GETDATE()" yield? It is
likely not the value you desire. However, your description doesn't match
this query - you said "... has changed within the last day" yet your
expression subtracts 7 days from the current date/time.
At a minimum, the following should help you understand datetime values and
how they should be used.
http://www.karaszi.com/sqlserver/info_datetime.asp
And as everyone else suggested, post the error message - exactly as it
appears. Perhaps it would help to define/identify what "external database"
means - I've never seen sql server return an error message with the text
"lexical element error" in any form.|||Jason wrote:
> Hi,
> I've been struggling trying to figure out this code to no avail. I
> need to query a date time field on an external database to give me
Using OPENQUERY?

> data if the Closed_Time has changed within the last day. The
> Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code
> I've been attempting to use is (Closed_Time > DATEADD(day, - 7,
> GETDATE()) and when that is executed a Lexical Element error message
> is returned. I've searched on the boards for other variations of
> this code and none have worked so far.
> I'm hoping someone can shed a little light on it for me.
>
I suggest asking for help in a group devoted to the type of external
database you are using. It sounds as if that rdbms requires a different
syntax for the DATEADD function than that required by Transact-SQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The exact error message is
ADO Error Driver) Expected lexical element not found :)
I was informed that it could be an ODBC issue. I get data from a company
and I use their own ODBC connection to pull it. We're under the assumption
that we are limited becasue of the connection. With that in mind I just
attempted this Closed_Time > DATEADD(day, -1, 1/26/2006 10:02:25 AM)) and th
e
same error was given. If I'm limited by the ODBC connection, what other way
can I get records that were just updated in the last day.
Thanks for all the help, I appreciate it.
Jason
"Bob Barrows [MVP]" wrote:

> Jason wrote:
> Using OPENQUERY?
>
> I suggest asking for help in a group devoted to the type of external
> database you are using. It sounds as if that rdbms requires a different
> syntax for the DATEADD function than that required by Transact-SQL.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||The query is taking place via a SQL task in a DTS package.
"Bob Barrows [MVP]" wrote:

> Jason wrote:
> Using OPENQUERY?
>
> I suggest asking for help in a group devoted to the type of external
> database you are using. It sounds as if that rdbms requires a different
> syntax for the DATEADD function than that required by Transact-SQL.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||"ODBC" tells us nothing. Is it an Oracle database? Foxpro? MySQL? DB2? (I've
never seen a Jet error message containing the word "Lexical" so I think
Access can be ruled out)
You have to use syntax that works in the external database. We cannot help
you with that without knowing the type of database involved. And even then,
the chances of finding somebody who is knowledgeable about that database in
a SQL Server group are not great.
Could you ask a developer in that "company" to write a query that does what
is needed and send it to you?
Bob Barrows
Jason wrote:
> The exact error message is
> ADO Error Driver) Expected lexical element not found :)
> I was informed that it could be an ODBC issue. I get data from a
> company and I use their own ODBC connection to pull it. We're under
> the assumption that we are limited becasue of the connection. With
> that in mind I just attempted this Closed_Time > DATEADD(day, -1,
> 1/26/2006 10:02:25 AM)) and the same error was given. If I'm limited
> by the ODBC connection, what other way can I get records that were
> just updated in the last day.
> Thanks for all the help, I appreciate it.
> Jason
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Ok it looks like in order to query that database I need to use this syntax.
(Closed_Time > { ts '2006-01-25 00:00:00' })
Is there then a way for me to store the current date in this format in
another table perhaps and then pass it along to this query or would that fai
l
on me as well? I'm just looking to automate it as much as possible.
Thanks.
"Bob Barrows [MVP]" wrote:

> "ODBC" tells us nothing. Is it an Oracle database? Foxpro? MySQL? DB2? (I'
ve
> never seen a Jet error message containing the word "Lexical" so I think
> Access can be ruled out)
> You have to use syntax that works in the external database. We cannot help
> you with that without knowing the type of database involved. And even then
,
> the chances of finding somebody who is knowledgeable about that database i
n
> a SQL Server group are not great.
> Could you ask a developer in that "company" to write a query that does wha
t
> is needed and send it to you?
> Bob Barrows
> Jason wrote:
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegr oups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>
|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.
|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegr oups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/in...e.asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegroups
.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
--
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>