|
I used to work in SQL Server 2000 and it's been a while. Now getting into a project with SQL Server 2005. I used to be able to create an auto-filling sequential record ID (effectively a long unique ID) assigned to every record so I could use that as a relational field among related tables. This seems to have been replaced by the uniqueidentifier type in SQL 2005? The problem is that a GUID is effectively a formatted string and a lot of characters, where the long is preferrable for me. How can I get a long int id that is sequential, and therefore unique for each record?
Thanks!
|
|
|
|
|
CREATE TABLE example
(
[ID] bigint IDENTITY (1, 1)
) Works in both SQL Server 2000 and 2005.
|
|
|
|
|
Thanks. I guess working in SQL Server Studio Express isn't the full product and isn't giving me all the options. I'll get the SQL Server access in the next few days and try it on there directly.
|
|
|
|
|
I am using VC++ 6 to access a database using ADO. I have created a record set which contains a variable of type DBTIMESTAMP. I have trouble when I try to update, or insert a record with a field which is a time/date stamp.
I know how to do this with an SQL statement using the Execute method of the connection. What I want to do is use the AddNew or Update method of the recordset to alter, or create a record.
It seems that all values specified must be represented as strings. The string that I use for the time/date stamp however must be the wrong format as I get the type mismatch error.
Any idea what could be wromg?
Mark Kunkel
|
|
|
|
|
You should be OK. I've had no problems using adDBTimeStamp in a recordset in VB.
Be aware that the SQL Server datatype timestamp does not correspond to a date and time. It is a synonym for rowversion , which is simply a unique identifier for the row information and is modified every time the row is updated. You cannot supply data for this column type.
The database type for a date and time is datetime , or if you don't need that wide a range or precision and want to pack a few more rows to a page (can help in reducing I/O when scanning a table), smalldatetime .
|
|
|
|
|
I am trying to a create a stored procedure that accepts 4 input parameters. These parameter are used in the where clause of a select query to return a record from a sql server 2005 database. The record is the result of a three table join. I am getting an error "Must declare the scalar variable '@date'" If I re-arrange the order of the parameter-catching variables, the error will always list the first of the four. I googled the error message and couldn't find anything that helped. I wrote this based on examples in a couple books. And I don't think what I am trying to do is that uncommon.
Can someone please point me in the right direction. I am fairly knew at this.
-----------
use oee
go
create procedure dbo.sp_GetDowntime
@date smalldatetime
@dept nvarchar(3)
@equip nvarchar(25)
@shift nchar(1)
as
SELECT h.oee_date, h.dept, h.equip, h.oee_shift, d.dt_reason, r.name, d.min
FROM userdata_header h
INNER JOIN userdata_downtime d ON h.dept = d.dept AND h.oee_date = d.oee_date AND
userdata_header.equip = userdata_downtime.equip AND h.oee_shift = d.oee_shift
INNER JOIN refer_dt r ON d.dt_reason = r.dt_reasonID
WHERE h.oee_date = @date, h.dept = @dept and h.equip = @equip and h.shift = @shift
|
|
|
|
|
Try:
CREATE PROCEDURE dbo.sp_GetDowntime
@date SMALLDATETIME,
@dept NVARCHAR(3),
@equip NVARCHAR(25),
@shift NCHAR(1)
AS BEGIN
SET NOCOUNT ON
SELECT h.oee_date, h.dept, h.equip, h.oee_shift, d.dt_reason,
r.name, d.min
FROM userdata_header h
INNER JOIN userdata_downtime d
ON h.dept = d.dept
AND h.oee_date = d.oee_date
AND h.equip = d.equip
AND h.oee_shift = d.oee_shift
INNER JOIN refer_dt r
ON d.dt_reason = r.dt_reasonID
WHERE h.oee_date = @date
AND h.dept = @dept
AND h.equip = @equip
AND h.shift = @shift
RETURN (0)
END I found the following problems:- The parameters needed commas between them.
- There was a comma in the where-clause.
- The "equip" link between the two tables specified the table-names instead of the table aliases.
- I added "Set Nocount On" because many front-end programming languages by SQL-Server results otherwise.
Regards
Andy
|
|
|
|
|
Thanks, Andy. That was very helpful. Your version works perfectly, of course. I can't believe I missed the commas...where they were needed and where they weren't. I guess the error message was a misnomer. Thanks again.
|
|
|
|
|
Once I have a GB created I can create new controls as children of it, but I can't find a way to drag existing ones inside. I can place them overtop of it, but they're not bound parent-child and stay in place when the GB itself is moved on the form.
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
If you have groupbox1 then I guess this piece of code will add new controls as parent-child:
private TextBox textBox1 = new TextBox();<br />
groupbox1.Controls.Add(textbox1);
By the way, you have posted in an incorrect forum. This forum for database stuff
|
|
|
|
|
I'm not asking about a form in C#, c++, etc. The access DB has a built in system to make (ugly) forms. IT uses a vb dialect for events but doesn't expose any of what would be constructor type logic in a real language to the developer.
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
The same was true in the VB6 designer. You can cut the control you're trying to move into the group box, then select the group box, and select Paste, IIRC.
|
|
|
|
|
hi,
in the sql we have user_tab_coloumns to know the datatype of a field in a table by properly querying but my problem is i am using an oledbconnection in C# to connect to an access database and i want to know the datatype of a field of a table in that perticular database how to do that?
pls help me.
thanx in advance.
|
|
|
|
|
How about the DbConnection.GetSchema method?
|
|
|
|
|
how that helps?
|
|
|
|
|
It allows you to use c# to get the details of a database table, and its columns. Isn't that what you asked for?
|
|
|
|
|
Check out OleDbDataReader.GetDataTypeName(index) or OleDbDataReader.GetFieldType(index)
Never tried it myself but should work
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
hi,
i have 2 fields with varchar dates as follows
SDATE OUT_DATE
20070604 20070528
20070604 20070529
20070605 20070601
i have converted both in to date like following,
//SDATE
CONVERT(NVARCHAR,(substring(TFRTTV.SDATE,1,4)+'/'+substring(TFRTTV.SDATE,5,2)+'/'+substring(TFRTTV.SDATE,7,2)),111)
//OUT_DATE
CONVERT(NVARCHAR,(substring(TFRTTV.OUT_DATE,1,4)+'/'+substring(TFRTTV.OUT_DATE,5,2)+'/'+substring(TFRTTV.OUT_DATE,7,2)),111)
now i wanna filter records to show only
select SDATES ,OUT_DATE where OUT_DATE>SDATE-5
please tell me how to do this.
Regards
Ruwandi
rkherath
|
|
|
|
|
hai try this query
select sdates,out_date from tablename where date(out_date)>adddate( date(sdates),-5);
ok bye
|
|
|
|
|
hi ganesamoorthidhayalan,
i tried it it says
Server: Msg 195, Level 15, State 10, Line 3
'date' is not a recognized function name.
regards
Ruwandi
rkherath
|
|
|
|
|
hai friend,
tell me the either your are working in sql or mysql.
In mysql date is one of the predefine funtion.
You find out the date function,
warms and regards
ganesamoorthi dhayalan
|
|
|
|
|
try this query,
select sdates,outdate from tablename where out_date > dateadd(day,-5,sdate);
by
ganesamoorthi dhayalan
|
|
|
|
|
SELECT sdates, out_date FROM tableA
WHERE Convert(datetime, out_date) > DateAdd(dd, -5, Convert(datetime, sdate))
here in hte where clause converting both dates into datetime datatype and to sdate adding (-5) days i.e. subtracting 5 days from sdate. then checking whether out_date is greater.
Regards
KP
|
|
|
|
|
Hi..
Can u tel me abt calling batch files(.bat) from PL/SQL program?
PLz Help me..
Thanx
Gurudatta B. Shelke
|
|
|
|
|
use xp_cmdshell sp. This is extended sp and basically these are dll's. Let me know in case you need any more help!! Amit
|
|
|
|