Re: Order in database - Database Discussion Boards

Re: Generate a MAc adress with a stored procedure

bluepoison21-Sep-09 21:30

21-Sep-09 21:30

I need to generate Mac address because we Program a Mac address in different kind of PCB's (like network cards) We want to trace the Mac address that we program in the PCB's. We get difefrent ranges that we use for that.

Re: Generate a MAc adress with a stored procedure

Luc Pattyn21-Sep-09 12:06

Luc Pattyn

21-Sep-09 12:06

Hi,

Mac addresses are 8-byte (unsigned) numbers that typically get shown in hex with or without dashes in between bytes. You could hold their numeric value in a System.UInt64 (for .NET programming languages), show them with ToString("X16"), and handle them as NUMERIC in SQL according to this[^].

I think that beats the alternative of handling them as strings, since then you would have to implement some code to "hex increment" a string...

BTW: I would rather not have to store a range of MAC addresses, unless I were a manufacturer of network equipment that is.

Smile | :)

Luc Pattyn

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

Local announcement (Antwerp region): Lange Wapper? Neen!

unable to connect java with sql database to validate user login..........

elazor20-Sep-09 23:30

elazor

20-Sep-09 23:30

how to connect sql to java netbeans6.7 for validating user detail .................

Re: unable to connect java with sql database to validate user login..........

Abhishek Sur21-Sep-09 11:56

Abhishek Sur

21-Sep-09 11:56

Is it Related to Database Confused | :confused:

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB
Don't forget to click "Good Answer" if you like to.

Order in database

srikantha_nagaraj20-Sep-09 1:11

srikantha_nagaraj

20-Sep-09 1:11

Hi,

Actually i want rows in the table in an order. How can i force the tables to keep that order? is there any command for that. Actually i am inserting the data row from asp.net front end.

Re: Order in database

Eddy Vluggen20-Sep-09 1:37

Eddy Vluggen

20-Sep-09 1:37

srikantha_nagaraj wrote:
Actually i want rows in the table in an order.

Sort the table.

srikantha_nagaraj wrote:
How can i force the tables to keep that order?

By definition, you can't. A table is a collection of rows that's saved in a random order. You can read them back and order by any field that's present within that selection. You could also add a custom field to indicate the order of the rows, but you'd still need to 'order by' that particular field.

You can, alternatively, create a stored procedure that selects all your rows and sort them. Next, hide the table and give the user only access to your sproc. That would not stop anyone from re-sorting the dataset though.

I are Troll Smile | :)

Re: Order in database

srikantha_nagaraj20-Sep-09 2:02

srikantha_nagaraj

20-Sep-09 2:02

Thanks for the answer. Smile | :)

Re: Order in database

J4amieC21-Sep-09 1:44

J4amieC

21-Sep-09 1:44

Eddy Vluggen wrote:
srikantha_nagaraj wrote:
How can i force the tables to keep that order?

By definition, you can't.

That's not strictly true, Tables will be stored in the order of their clustered index. This then forms the "default" order.

Can someone justify their vote of 2?

J4amieC21-Sep-09 4:13

J4amieC

21-Sep-09 4:13

Because it escapes me.

Re: Can someone justify their vote of 2?

Eddy Vluggen21-Sep-09 5:30

Eddy Vluggen

21-Sep-09 5:30

I added a five, that should even it out.

J4amieC wrote:
That's not strictly true, Tables will be stored in the order of their clustered index. This then forms the "default" order

The rows in a RDBMS are not interdependent, same goes for the columns. They might not be returned in the order that they're saved. Because there is no relation between the rows themselves (except that they belong to the same table) there is no order in the collection. This may vary depending on each implementation of course.

Now, that's just theory that I swallowed a long time ago. I found that the most simple table in Access has a storage-order, and I have seen people abuse it. Most of the time, it's easier to add a custom column and sort on that.

I've been reading up after your comment, and a clustered index[^] seems indeed to be the solution Smile | :)

Yes?

Re: Order in database

i.j.russell21-Sep-09 5:28

i.j.russell

21-Sep-09 5:28

That's not strictly true either! The only way to guarantee output order is using an order by clause.

Re: Order in database

Eddy Vluggen21-Sep-09 5:33

Eddy Vluggen

21-Sep-09 5:33

That makes it also not strictly false Laugh | :laugh:

public enum strictBool { true, false, EFileNotFound }

Re: Order in database

stendulkar5420-Sep-09 12:22

stendulkar54

20-Sep-09 12:22

Use Order By clause of SQL. It orders the output.

Re: Order in database

Ashfield21-Sep-09 8:14

Ashfield

21-Sep-09 8:14

You have already asked this question and had correct replies. A database does NOT guarantee the order records are returned in UNLESS you have an order by clause. If there is a clustered index or primary key then this is the default order, but even then it is NOT guaranteed (certanly by SQL Server) as when you have multiple worker processe, unless an order by is specified he results are returned as each worker process completes, which MAY not be the expected order.

If you hav any further doubts try reading up on it, there are thousands of articles on google

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Log file of Database increases in every replication

Abdul Rahman Hamidy18-Sep-09 23:57

Abdul Rahman Hamidy

18-Sep-09 23:57

Dear All,
I have created replication in a single Database more than 20 times, since three years.
now i have seen some thing amazing,
In every replication the size of log file increases double the size of original file.
as If the first log file is 1 GB then the new one is 2 GB
I used to shrink this too, but i could not get any success, why its like this?
what should i use in order to decrease this log.

Note: I am using SQL 2005

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: Log file of Database increases in every replication

Eddy Vluggen19-Sep-09 1:31

Eddy Vluggen

19-Sep-09 1:31

Abdul Rahman Hamidy wrote:
I used to shrink this too, but i could not get any success, why its like this?

It's there for your own security[^], I guess that the Recovery Model for the database is set to "Full". (Right click database, properties, options)

There's an article here[^] that might help in shrinking it Smile | :)

I are Troll Smile | :)

Re: Log file of Database increases in every replication

Abdul Rahman Hamidy19-Sep-09 1:35

Abdul Rahman Hamidy

19-Sep-09 1:35

thx, i think link can help me alot

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

[Message Deleted]

anushh18-Sep-09 7:12

anushh

18-Sep-09 7:12

[Message Deleted]

Re: Is there any possiblity to name tables in sql server

Luc Pattyn18-Sep-09 7:49

Luc Pattyn

18-Sep-09 7:49

I don't think you can call them "Urgent!"
However you could call them "Urgent1", "Urgent2", "Urgent3", etc.

A database wouldn't be very useful if you couldn't name your tables, would it?

Smile | :)

Luc Pattyn

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

Local announcement (Antwerp region): Lange Wapper? Neen!

SSIS Database copying

imnotso#18-Sep-09 2:17

imnotso#

18-Sep-09 2:17

Hi,

I have a master DB server, I need to update 4 more DB servers with data from 10 tables on the master. All tables and DB structures are the same on all servers. We need 4 because of traffic load.

So....

I was thinking of setting up a SSIS .DTSX package. Easy for 1 server to another. How do I get it look at 3 more ? Configuration XML file? Can I put in 4 database names, username and passwords and get it to FORLOOP? Do i have the data in a separate table, load it into an object in the package and FORLOOP that?

Or do I create 4 packages and schedule them next to each other ! That would be the easiest.

Any help would be welcome.

Harvey

Re: SSIS Database copying

J4amieC18-Sep-09 3:08

J4amieC

18-Sep-09 3:08

I would go with 1 package, and configure it to run 4 times with different configurations for the destinations.

Re: SSIS Database copying

imnotso#18-Sep-09 3:11

imnotso#

18-Sep-09 3:11

The 4 different configurations can all be put in 1 XML file? Any ideas would be gladly received.

Odd problem (conversion or openquery?) only appearing in a stored procedure but not when running it adhoc

alex3_1417-Sep-09 23:17

alex3_14

17-Sep-09 23:17

Here's the code I can run with no problems:

declare @AnneeSelectionnee numeric
select @AnneeSelectionnee = 2009
declare @DateDernierImport datetime
select @DateDernierImport =  'Nov 14 2008 12:00AM'

SELECT
    LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
    LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
   FROM T_ASFSTAG0
   WHERE   (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
    ((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or
    (isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))

Please note that T_ASFSTAG0 is a view that is partly defined as follows:

FROM         OPENQUERY(ISERIES_SQLDBF, 'SELECT * FROM tstadvdbf.ASFSTAG0') AS DerivedTable1

And the following is the relevant code from the stored procedure that fails:

print @AnneeSelectionnee--2009
print @DateDernierImport--Nov 14 2008 12:00AM

print '------before the select-----------'
print convert(datetime,convert(varchar,@DateDernierImport,103),103)
print '------right before the select-----------'

 SELECT --line 60--
    LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
    LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
   FROM T_ASFSTAG0
   WHERE   (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
    ((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or
    (isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))

print '------after the select and before the insert-----------'

That select statement (on "line 60") reports an error. We never see the "after the select and before the insert" print message. I thought it might have been a conversion problem but as long as an AND statement works like && does in C# (and, AFAIK, it does) then we should have no problems with the (isdate(x) AND convert(datetime(x)).

This is the error I receive:

Server: Msg 7399, Level 16, State 1, Procedure P_IMPORT_STAGES_PREPA, Line 60
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x80070057:  One or more arguments were reported invalid by the provider.].

Any help or hints is greatly appreciated.

Alex

Re: Odd problem (conversion or openquery?) only appearing in a stored procedure but not when running it adhoc

ElectricLlama17-Sep-09 23:38

ElectricLlama

17-Sep-09 23:38

IMHO the only way to debug this stuff is hack it down to its simplest level and start adding columns until the error occurs. You will either find a mysterious undocumented bug or an obvious headslapping one.

So maybe progressively alter your SP as follows:

SELECT 'A' As C1, 'B' As C2, 'C' As C3
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee)
/*
AND ((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or (isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))
*/

Does that work? keep adding bits in till it breaks.

Also having a select * anywhere in code makes me nervous so maybe it could help things if you alter your view to select specific columns from the table.

Re: Odd problem (conversion or openquery?) only appearing in a stored procedure but not when running it adhoc

alex3_1418-Sep-09 0:05

alex3_14

18-Sep-09 0:05

I went one higher and did the following:

print '------right before the select-----------'
--2009
--Nov 14 2008 12:00AM
 SELECT --line 67--
    LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
    LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
   FROM T_ASFSTAG0
  /* WHERE   (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
    ((isdate(T_ASFSTAG0.STCRT1)=1 and (convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1))>=convert(datetime,convert(varchar,@DateDernierImport,103),103))) or  
    (isdate(T_ASFSTAG0.STMAJ1)=1 and (convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1))>=convert(datetime,convert(varchar,@DateDernierImport,103),103))))  
*/print '------after the select and before the insert-----------'

Essentially, it's just the referencing of the view that makes it fail.

By the way, the view is defined with specific fields...

SELECT     STANS1, STSTA1--,... snip many others
FROM         OPENQUERY(ISERIES_SQLDBF, 'SELECT * FROM tstadvdbf.ASFSTAG0') AS DerivedTable1

The error I got was:

Server: Msg 7399, Level 16, State 1, Procedure P_IMPORT_STAGES_PREPA, Line 67
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x80070057:  One or more arguments were reported invalid by the provider.].

Alex

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.