Re: Back up database - Database Discussion Boards

ABBASI_RA18-Feb-05 23:52

18-Feb-05 23:52

HI How i can create a rownumber column in my view results?
like this sample:

select f1 from table1
go

results:

f1
---
www
aaa
vvv
bbb

I WANT THIS FORM:
rowno f1
---- ---
1 www
2 aaa
3 vvv
4 bbb

thanks

Re: RowNumber in results.

Mike Ellison19-Feb-05 19:53

Mike Ellison

19-Feb-05 19:53

Hi there. It depends on the database you are using - which one?

Re: RowNumber in results.

ABBASI_RA20-Feb-05 1:37

ABBASI_RA

20-Feb-05 1:37

hi I use MS SQL2000 Ent.

Re: RowNumber in results.

Mike Ellison22-Feb-05 9:48

Mike Ellison

22-Feb-05 9:48

Okay. Unfortunately, there isn't a simple way (like Oracle's ROWNUM) to do this in SQL Server 2000. SQL Server 2005 will support a function for this, but in 2000 you have basically two directions you can go. One is to use a temporary table with an identity column - probably in a stored procedure so you can reuse it like you would a view. http://www.sqlteam.com/item.asp?ItemID=1491[^]

Another would be to use a specially constructed auto-join query designed to rank the results.
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133[^]

I suppose you could also create a stored procedure that loops through a cursor, but the performance would probably be bad.

Re: RowNumber in results.

SimonS20-Feb-05 1:47

SimonS

20-Feb-05 1:47

You could create a temp table with an autonumber field for the "rowno" and do a INSERT SELECT from your current resultset.

Else, have a look at this [^]which might be of help.

Cheers,
Simon

sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.

article :: animation mechanics in SVG blog:: brokenkeyboards
"Most of us are programmers, but a few use VB", Christian Graus

Re: RowNumber in results.

ABBASI_RA20-Feb-05 2:40

ABBASI_RA

20-Feb-05 2:40

hi i saw that page thank u
BUT
i want to use it in views.
i cant create an extra table
tanks again

Back up database

GoldenStar18-Feb-05 22:56

GoldenStar

18-Feb-05 22:56

Hi all

I have been reading through the SQL Server 2000 database book, and I know that there are 3 types of back-up database: full backup, differential backup and transaction log backup. So which one is the best recommendation?

I'd appreciate for anyone whoi can help me out!

Cheers,

Kelly

Re: Back up database

Colin Angus Mackay19-Feb-05 1:10

Colin Angus Mackay

19-Feb-05 1:10

Kelly Giang wrote:
full backup, differential backup and transaction log backup. So which one is the best recommendation?

It depends on what you want to achieve...

A full backup creates a backup of the whole database

A differential backup creates a backup of just the differences between the last backup and now

A transaction log backup creates a backup of just the transaction log since the last backup.

Restoring a full backup means you can recover to the point of the backup. Restoring a differential backup ontop of that means you can recover to the point of the last differential backup, and a transaction log backup can be used to allow you to restore to an exact point in time or to the point of failure.

You should read the SQL Server 2000 Books-online[^] for more information.

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: Back up database

GoldenStar20-Feb-05 10:13

GoldenStar

20-Feb-05 10:13

Thanks a lot Colin!

Cheers,

Kelly

Some help with this SQL statement

Tom Wright18-Feb-05 7:05

Tom Wright

18-Feb-05 7:05

Can someone help me with this SQL statement? I'm wanting a bigint with a datetime stamp similar to:20050211102341780

Here's my SQL statement:

<br />
INSERT INTO TSK2 EXECUTE ('SELECT' ' DATEPART([YEAR], CURRENT_TIMESTAMP), DATEPART([MONTH], CURRENT_TIMESTAMP), DATEPART([DAY], CURRENT_TIMESTAMP), <br />
                      DATEPART([HOUR], CURRENT_TIMESTAMP), DATEPART([MINUTE], CURRENT_TIMESTAMP), DATEPART([SECOND], CURRENT_TIMESTAMP) <br />
                      DATEPART(MILLISECOND, CURRENT_TIMESTAMP)'' AS TSK2_DATE_TIME, 0 AS TSK2_PRIORITY, 341 AS TSK2_ACTION')<br />

Thanks

Tom Wright
tawright915@yahoo.com

Re: Some help with this SQL statement

Michael Potter18-Feb-05 9:02

Michael Potter

18-Feb-05 9:02

I won't claim that it is efficient but if you only use it when inserting records it shouldn't effect performance that heavily.

SELECT 
     CAST(
     REPLACE(
     REPLACE(
     REPLACE(
     REPLACE(
          CONVERT(VARCHAR(30),CURRENT_TIMESTAMP,21)
     ,'-','')
     ,':','')
     ,'.','')
     ,' ','') 
     AS BIGINT)

Re: Some help with this SQL statement

Tom Wright18-Feb-05 12:48

Tom Wright

18-Feb-05 12:48

Awesome....works great!

Thanks

Tom Wright
tawright915@yahoo.com

Append Column to Row

Anonymous18-Feb-05 4:30

Anonymous

18-Feb-05 4:30

Is it possible to append a selected column onto the end of a selected row? For instance, I have 2 selects. The first only returns 1 row. The second returns many rows, but only in 1 column. Can I easily append all of the rows from the 2nd select onto the end of the row in the first select, without using a cursor? Even better, we'd like to concatenate all of the rows from the 2nd select into one long string, and just append that string onto the end of the 1 selects row. What might be the best approach to this?

Thanks!

Re: Append Column to Row

Michael Potter18-Feb-05 9:18

Michael Potter

18-Feb-05 9:18

Assuming you are using SQL Server

Here is a little sample of creating a comma delimited list from a set of rows. You can run it in SQL Query Analyzer by replacing [Name]/[Weather] with field/table of your choice. Careful that you don't pick a query that goes beyond the 8000 character limit.

This doesn't get rid of the cursor when assembling the final table since you will have to reset @sample to null at each row iteration. It does get rid of the nasty 'rows to 1 column' issue though.

DECLARE @sample VARCHAR(8000)
SELECT @sample = ISNULL(@sample + ', ','') + [Name]
FROM [Weather]

PRINT @sample

Migrating from Acess to Oracle

ashish kabra18-Feb-05 2:38

ashish kabra

18-Feb-05 2:38

I have Table in ACCEss. i want it to be exported or moved to oracle. I had tried by making ODBC connection string and exporting the table.

It shows table in tablespace (i.e Whem we fire
Select * from tab; )

But when we fire desc or any select query on table

it shows object does not exists/....

In short i want to export mine table from access to oracle

I m having many table's that r to be moved to oracle with data. Suggest Possible way: confused:

SQL Server row size

Talal Sultan18-Feb-05 0:51

Talal Sultan

18-Feb-05 0:51

hi people,

I have a question concerning the text/ntext data types. I have read that the maximum number of characters that we can place in an SQL row is 8060. It sounds quite strange since the text data type can hold more than 2 million. Now when I declare a field as "text", I notice that the size is 16, which is probably 16 bytes. This probably means that in fact, the field, is just like the interger field. Is it correct to say so? Is the "text" field in fact a pointer to a location where the 2 million-character (or more) data is located?
I have tried to insert manually (using the SQL Enterprise Manager) a 3000-character text into a field of "text" data type, but it doesn't permit so.
My question is, how can I insert text in a "text" data type field?

thanks,
Talal

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Re: SQL Server row size

Mike Dimmick18-Feb-05 4:28

Mike Dimmick

18-Feb-05 4:28

The largest you can set a char or varchar column to is 8000 bytes. An nchar or nvarchar column can be up to 4000 characters. The size of the whole row, including overhead, must be less than 8060 bytes.

The TEXT data type can actually hold up to 2GB of data. NTEXT is limited to 2^30 - 1 characters (again, 2GB of data). In the typical case, the row holds a 16-byte pointer to the location of the actual data. An additional index is created on the table to indicate where the text/image data is located.

To insert text data manually, use the SQL WRITETEXT or UPDATETEXT commands in Query Analyzer.

Stability. What an interesting concept. -- Chris Maunder

Re: SQL Server row size

Talal Sultan18-Feb-05 8:36

Talal Sultan

18-Feb-05 8:36

Thanks for the reply, I checked those functions and the help that comes with the Query Analyzer. I managed to insert data but when I tried to retrieve the field, still using the QA, I only got a portion of it, eventhough I had the size to 0 in the READTEXT function, which should normally return a chunk of 4KB (as the help says).
I'm thinking this might be just because of QA, because when I put a size that is bigger than the real size of the text, I get an error that says that the size is greater than the actual size, and it gives me the correct number of characters, which in my case is 2800.
So I suppose that the result given by the QA is just a fixed number of characters, regardless of the real size of the text?

To use this programmatically, how would I proceed? Would I use a function or a stored procedure? or is there a special way?

thanks a lot for your help.

Talal

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Olap Cubes

Yulianto.17-Feb-05 14:24

Yulianto.

17-Feb-05 14:24

Did anybody knows how to move Olap cubes from one computer to another. So far I have to do it manually on the other computer. Thanks

<italic>Work hard and a bit of luck is the key to success.

You don`t need to be genius, to be rich.

Re: Olap Cubes

Mike Ellison17-Feb-05 14:34

Mike Ellison

17-Feb-05 14:34

Hi there. Are you talking about olap cubes in Microsoft Analysis Services? or a different framework?

If this is in Analysis Services, you can use the Analysis Services manager to create an archive file of the cube database, then copy it over to the other machine and restore it there.

Re: Olap Cubes

Yulianto.17-Feb-05 14:47

Yulianto.

17-Feb-05 14:47

I`m using Microsoft OLAP Service. Should I use Microsoft Analysis Services? Thanks

<italic>Work hard and a bit of luck is the key to success.

You don`t need to be genius, to be rich.

Re: Olap Cubes

Anonymous18-Feb-05 5:40

Anonymous

18-Feb-05 5:40

Hi there. Perhaps I'm ignorant about this - I thought they were one and the same.

Re: Olap Cubes

GWSyZyGy18-Feb-05 7:21

GWSyZyGy

18-Feb-05 7:21

Maybe ... maybe not. "OLAP Services" could mean the general technology OR the OLAP engine in MS SQL 7. Microsoft renamed it "Analysis Services" with SQL2000.

Re: Olap Cubes

Yulianto.18-Feb-05 15:09

Yulianto.

18-Feb-05 15:09

I`m using the OLAP engine in MS SQL 7. Isn`t it different with the Analysis Service in SQL2000?

<italic>Work hard and a bit of luck is the key to success.

You don`t need to be genius, to be rich.

Inserting and Selecting .wav files from and to MySQL table

j1webb17-Feb-05 11:01

j1webb

17-Feb-05 11:01

I am a VB.Net developer and I am trying to use VB.Net, a web service and a MySQL table on a Linux server to save and retrieve .wav files.

I have been able to insert and retrieve .wav files to a MS SQL table by using the image datatype in the MSSQL table. I have converted a .wav file to both a byte array and a base64 character type and saved it (inserted) to a MSSQL table with no trouble. And I have used the select statment to retrieve that same .wav file and my code works.

However, I really need to create a webservice to store .wav files into a MySql table on a Linux server. We currently have numerous webservices that work well with MySql but inserting a .wav file has proven to be a real challenge. I am not trying to use a webservice during the testing, I make a direct connection to the MySql db and use ODBC SQL statements for my Inserts and Selects.

I have used numerous reader and writer routines that I have found on the Internet but none of the have worked.

There is not much on the internet about how to save .wav files in a database, but I am making an assumption I could use some of the same code that is used to save and retrieve image files. This assumption has worked when I am using MSSQL tables but it doesn't seem to work with MySql tables.

Does anyone have any idea as to how to do this?

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