|
CleaKO wrote: Where can I change that?
It is set when SQL Server is installed.
CleaKO wrote: My computer thinks I have SQL Express 2005 and when I installed SQL Server 2000 it obviously didnt get updated
I don't understand that statement.
SQL Server 2005 express edition usually installed itself as a named (not default) instance. The name is, I think, SQLEXPRESS.
The full server editions will attempt to install themselves as the default SQL Server unless it is already taken. (There can only be one default, but upto 16 named instances)
|
|
|
|
|
Colin Angus Mackay wrote: The full server editions will attempt to install themselves as the default SQL Server unless it is already taken. (There can only be one default, but upto 16 named instances)
This isnt a show stopper but Im just curious if after it is installed whether it can be changed by me. There is some kind of entry in the machine.config that has a .\SQLEXPRESS in it but I cant think of anywhere else that I would change something.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: Im just curious if after it is installed whether it can be changed by me
Not that I am aware of.
CleaKO wrote: There is some kind of entry in the machine.config that has a .\SQLEXPRESS in it but I cant think of anywhere else that I would change something.
That is most likely to permit SQL Server 2005 to use CLR Stored Procedures
|
|
|
|
|
It is probably set in your machine.config. I would look there. It can be found in your C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
the v2.0.50727 might be different depending on which version of .net you are running.
Hope that helps.
Ben
|
|
|
|
|
Is this the best way to extract just the date portion of a datetime field while preserving the fact that the field is a datetime, so that date range comparisons can be made (without dealing with the time portion)?
Convert(datetime, Convert(nvarchar, transactiondatetime, 101))
Example:
select amount from deposit where
Convert(datetime, Convert(nvarchar, transactiondatetime, 101)) >= Convert(datetime, @StartDate) and
Convert(datetime, Convert(nvarchar, transactiondatetime, 101)) <= Convert(datetime, @EndDate)
where StartDate and EndDate are passed in as strings with only the date component (like '1/30/2007')
Thanks!
Marc
Thyme In The CountryInteracxPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
When I do that, I usually ensure that the end date is fixed so that the time portion is set to 23:59 and the start date's time portion is 00:00.
Also, I never use culture dependent strings. It can cause all sorts of problems. I always use the culture neutral ISO format.
I would say that if your database was always going to be used in the US then it probably doesn't matter. But a few years ago I worked on a database that was always going to be used in the UK, but somewhere along the way the cultural settings got changed and all the date/string stuff failed. After that, I switched to ISO format and have never had any problems.
|
|
|
|
|
Colin Angus Mackay wrote: When I do that, I usually ensure that the end date is fixed so that the time portion is set to 23:59 and the start date's time portion is 00:00.
I considered that as well, but figured I'd at least look at how to do it strictly in SQL. The problem is that there's a front-end UI to the report for selecting the date range, which doesn't include time. This value is then sent up to the middleware (Interacx server) that handles the query. Of course, I put logic in the client to do as you suggested, I just haven't figured out the best way to do this, because the client, being of a generic nature (Interacx client), doesn't really *know* that it's a start or end date. It's just a field in a datetime control that's associated with a parameter in the query. Gotta figure out the best way to do this.
Colin Angus Mackay wrote: I would say that if your database was always going to be used in the US then it probably doesn't matter.
Yes, it is. I considered culture issues, but this is strictly for turnkey, intranet systems here in the US. And there's already a competitor in Germany for this  system.
Marc
Thyme In The CountryInteracxPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
Marc Clifton wrote: Of course, I put logic in the client to do as you suggested, I just haven't figured out the best way to do this, because the client, being of a generic nature (Interacx client), doesn't really *know* that it's a start or end date. It's just a field in a datetime control that's associated with a parameter in the query. Gotta figure out the best way to do this.
I used to have a UDF in SQL Server that did that for me. Pity I don't work for that company any more, otherwise I could have found out how I did it. I just don't remember off the top of my head.
|
|
|
|
|
When I have done this type of stuff before usually I just worry about getting the @startdate and @enddate correct. That way I don't have to mess wtih the transactiondatetime which could be a lot of rows that I really don't want to do a convert with.
Usually I want the @startdate to have a time portion of 00:00:00 and the @enddate to have 23:59:59. Since what is passed in at the @startdate and @enddate will have a time on it I usually wack if off and add the correct time and covert it to a datetime.
Hope that helps.
Ben
|
|
|
|
|
kubben wrote: That way I don't have to mess wtih the transactiondatetime which could be a lot of rows that I really don't want to do a convert with.
Ah, good point. I imagine the performance of what I'm doing is pretty bad.
kubben wrote: Hope that helps.
Yup. Thanks!
Marc
Thyme In The CountryInteracxPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
Hello Friends,
I have a problem with recordsets.
I m getting Rs.RecordCount = -1 while calling a store procedures
here is code -
Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.RecordSet")
cmd.ActiveConnection = myCon
cmd.CommandText = "SP_Name"
cmd.CommandType = 4
cmd.CommandTimeout = 300
cmd.Parameters.Append cmd.CreateParameter("@par1",20,1,10,1)
cmd.Parameters.Append cmd.CreateParameter("@par2",20,1,10,1)
cmd.Parameters.Append cmd.CreateParameter("@par3",20,1,10,20)
cmd.Parameters.Append cmd.CreateParameter("@par4",20,1,10,Null)
Rs.CursorLocation = 3
Rs.LockType = 1
Rs.CursorType = 3
Set Rs = cmd.Execute
Response.Write "<br>RecordCount = " & Rs.RecordCount
pls give some usefull solution.
Thanks,
Ankur Bakliwal
|
|
|
|
|
What happens if you take those parameters, and call the stored proc within SQL Server ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
In Sql Server with same parameter that SP gives me 2 records as result.
Thanks,
Ankur Bakliwal
|
|
|
|
|
Then there must be a problem with the code. Not sure what, without seeing the proc.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I found this on MSDN:
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.
You are using CursorType = 3, which corresponds to a static cursor, so it should return. However, it also says:
The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount.
What provider are you using?
In general, the safest solution is to just include a count(*) in your stored procedure and return that as an output parameter.
Let me know if this works for you.
Roel
|
|
|
|
|
Hi!
I'm rather new to programming db, so... sorry if this may sound stupid.
I am using C# to create a program which does some operations with a database (basically, the project helps me better get along with databases). The .mdf file and .ldf file reside in C:\DATA\ . And on my computer, things go along smoothly.
Still, when i tried to distribute the application to a friend, i realized that i haven't build the application for anybody but me because he doesn't have the .mdf. So, he installed sql server (2000, just like mine), has put the files in C:\DATA, but he still gets an error. I know the error comes from the fact that the database is not attached to his server (asked a few friends) and i know that i must use sp_attach_db function.
The problem is that i don't know whether each time the program is run i should run this function (because if you run the program 2 times i think it gets an error) and, more important, i don't know how to really implement this function (haven't found any tutorial yet).
Another important thing is the connection string to the database. On my computer it is:
connectionString="Data Source=DRC;Initial Catalog=Consulting_co;Integrated Security=True" providerName="System.Data.SqlClient"<br /> //from app.config
as far as i know, datasource indicates the name of the sql server and this name must modify when the program is run from another place.
Please, help!
|
|
|
|
|
drc_no1 wrote: the connection string to the database. On my computer it is:
connectionString="Data Source=DRC;Initial Catalog=Consulting_co;Integrated Security=True" providerName="System.Data.SqlClient"
//from app.config
as far as i know, datasource indicates the name of the sql server and this name must modify when the program is run from another place.
Well, you only need to modify it is the database is in another place. In an organisation setting each computer will point to the same SQL Server on the network.
In your case you might want to just change Data Source=(local) instead. Specifying the server as (local), including the brackets, just means the default instance of SQL Server on this machine.
Back to attaching and detaching the database. I tend not to do this. Usually I take a backup and then restore the backup on the target machine. The other thing you have to watch out for is user accounts. Logins are stored in the master database, the user permissions are stored on a per-database level. Off the top of my head I don't recall how to match them up again (if they get mismatched), but it is something you will have to consider.
|
|
|
|
|
Hello friends,
I have a table..suppose having 3 columns.(Rate,FromQty,ToQty)
Now the Table have data on this format:
Rate FromQty ToQty
100 0 5
200 6 10
300 11 15
Now see for a particular product 0 to 5 have Rs.100.
Then 6 to 10 rate is Rs.200. and 11 to 15 is 300.
How can i get the rate of 2 qty.(i.e Rs.100 (0-5))
Pls can anybody send me the proper select statement..
thanks.
|
|
|
|
|
select rate where FromQty >= 0 and ToQty <= 5 ?
I'm not sure that I understand the question.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
No. i can enter any value through store procedure.it may be 2/8/13/19.
suppose i enter 2 then the row will be 100 0 5.
if i enter 8 then the row will be 200 6 10.
if i enter 13 then the row will be 300 11 15.
like this.
now if i generate this statement "select * from mratedetail where productcategoryid=49 and (toqty<=14.0 or fromqty>=14.0)" i have got all rows.
but in case of i want just "300 11 15".
thanks
|
|
|
|
|
Test270307 wrote: (toqty<=14.0 or fromqty>=14.0
That obviously won't work. I think if you use AND instead of OR, it may well work.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I have many fields and many rows.
so if i put or then it return so many records.
doesn't work for "or".
thanks
|
|
|
|
|
Test270307 wrote: doesn't work for "or".
That's why I said you need to use AND
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
select rate from tbl where FromQty <= @amt and ToQty >= @amt
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
SELECT Rate
FROM MyRateTable
WHERE 2 >= FromQty
AND 2 <= ToQty
Or, more generically,
SELECT Rate
FROM MyRateTable
WHERE @qty >= FromQty
AND @qty <= ToQty
Unless the FromQty and ToQty columns are indexed, the db will have to do a full scan to select ALL the rows that meet the @qty >= FromQty, then will have to do another full scan of all the selected rows to determine which of them meet the @qty <= ToQty requirement.
Alternately, if you're using a product that supports the "TOP" keyword, and you have the ToQty column indexed in assending order, you could use
SELECT TOP 1 Rate, ToQty
FROM MyRateTable
WHERE @qty <= ToQty
ORDER BY ToQty
and just ignore the ToQty in the return. This will guarantee good performance as the query will recognize the ORDER BY clause and use the existing index for the select.
Lastly, if your table really is as simple as the data you have used for your example, a mathematical formula would be better.
int getRate( int myQty )
{
if( myQty = 0 ) return 100;
else return (1+((myQty-1)/5))*100;
}
which could also be converted to SQL if you wanted.
Hope this helps.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
