Re: FIlestream of varbinary ? ( To Store files) - Database Discussion Boards

Member 356741115-Jul-13 2:02

15-Jul-13 2:02

Hi,

I have table like below

col1
5
10
15
20

I need result like

Col1 Col2
5 05--9
10 10--14
15 15--19
20 20-24

Plz help

Thank You

Anil Banjare

Re: How to find ladder in sql query

Niral Soni15-Jul-13 5:00

Niral Soni

15-Jul-13 5:00

How do you define ladder for the last value??
i.e. how do you get 24 in last row??

Apart from that, required query is -

SQL

SELECT COL1, NVL2(NEXT_COL1, COL1 || '--' || (NEXT_COL1 - 1), NULL) COL2 
FROM (
  SELECT COL1
        ,LEAD(COL1, 1, NULL) OVER (ORDER BY COL1) AS NEXT_COL1 
    FROM (SELECT LEVEL*5 COL1 FROM DUAL CONNECT BY LEVEL < 5) 
  )
;

And the output will be -

COL1	COL2
5	5--9
10	10--14
15	15--19
20	NULL

Thanks & Regards,
Niral Soni

Re: How to find ladder in sql query

karna from Hyderabad22-Jul-13 21:01

karna from Hyderabad

22-Jul-13 21:01

USE tempdb ;
GO
DECLARE @ladderTable TABLE ( Col1 INT ) ;
DECLARE @incrementValue INT = 0 ;

INSERT INTO @ladderTable ( Col1 )
VALUES ( 5 ),
( 10 ),
( 15 ),
( 20 ) ;

SELECT TOP 1
@incrementValue = Col1
FROM @ladderTable
ORDER BY Col1 ASC ;

SELECT st.Col1,
CAST(st.Col1 AS VARCHAR(20)) + '-'
+ CAST(ISNULL(dt.Col1, st.Col1 + @incrementValue) - 1 AS VARCHAR(20)) AS LadderSequence
FROM @ladderTable AS st
LEFT OUTER JOIN @ladderTable AS dt ON st.Col1 < dt.Col1
AND ( st.Col1 + @incrementValue ) >= dt.Col1 ;
GO

Thanks,
Karunakar

database course

V.14-Jul-13 22:32

14-Jul-13 22:32

I need to give an introductory database course to a bunch of people who generally are able to write simple SQL queries, but still mainly use files to save stuff. (fits files, ascii files, csv files, ...)

My course starts with presenting some pro's and con's, explaining the basic structure (tables, links, constrains, keys, indexes, ...) and diving into some basic SQL. A second lesson (still under development) would be coding against databases (creating a connection, SQL injection, ...)
I also included a lab with exercises (for lesson 1, lesson 2 to be done).

I looked at some tutorials on the internet, but still would like some advice on how to best bring this topic.

thanks in advance.

V.

(MQOTD Rules and previous Solutions )

Re: database course

Eddy Vluggen15-Jul-13 0:30

Eddy Vluggen

15-Jul-13 0:30

V. wrote:
A second lesson (still under development) would be coding against databases (creating a connection, SQL injection, ...)

I'd recommend explaining sprocs and functions, moving to Xml in the third lesson. Creating a connection to the database from a non-sql language has little to do with SQL.

What's their role? If design comes into play, you could reserve a week for the topic of database-normalization. There's examples on structures that break normalization on the wikipedia[^]; the update-anomaly is always a nice intro as people recognize it from the real world Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: database course

V.15-Jul-13 1:30

15-Jul-13 1:30

Thanks! that already helps !

V.

(MQOTD Rules and previous Solutions )

FIlestream of varbinary ? ( To Store files)

Amol_B14-Jul-13 21:22

Amol_B

14-Jul-13 21:22

After reading several documents , i am still confused whether to use Filestream or Varbinary for storing files?
my application need PDf files to be saved in database , size can be between 100 KB to 4 MB

I guess in order to use filestream we have to configure server to use that. while varbinary can work as it is.

what will be best way?Experts please comment.
Thanks.

Re: FIlestream of varbinary ? ( To Store files)

Richard MacCutchan14-Jul-13 22:29

Richard MacCutchan

14-Jul-13 22:29

Take a look at http://sqltales.wordpress.com/2012/05/15/varbinary-vs-filestream-and-other-blob-issues-3/[^].

Use the best guess

Re: FIlestream of varbinary ? ( To Store files)

Mycroft Holmes14-Jul-13 22:42

Mycroft Holmes

14-Jul-13 22:42

We use filestream only to reduce the size of the database backup which is retained for some ridiculous number of years! documents are backed up seperately.

Never underestimate the power of human stupidity
RAH

Re: FIlestream of varbinary ? ( To Store files)

Amol_B14-Jul-13 22:58

Amol_B

14-Jul-13 22:58

Thanks +5

need an sql query to extract results in the attached format

vijays.30198914-Jul-13 3:40

vijays.301989

14-Jul-13 3:40

Hi Everyone,

I need a help in sql.

Below is the details:

from the front end, i will select few sql objects
for eg, 
i am selecting 
a) ProcedureName1, View_1 from Company_BAT and 
b) GetEmpByProjID,GetAllProj, samplefunction from Company_DEV

here 
ProcedureName1,GetEmpByProjID,GetAllProj are procedures.
View_1 is a view.
samplefunction is a function.


ProcedureName1 uses	a) View_1 (View_1 uses Table_1)
			b) Table_1
			c) Table_2

View_1 uses		a) Table_1

GetEmpByProjID uses	a) Employee

GetAllProj uses	        a) Employee
			b) Project

samplefunction		a) it doesnt uses any objects

i need the output in the format as in the below screenshot in the below link:

http://vijays301989.blogspot.com/b/post-preview?token=QH4f3z8BAAA.jAJqT5poBN3LnJ_eDbQDgA.vZNULScDlF40PLWlpXSZKA&postId=6529902930179783663&type=POST


PLease advise, how to obtain result in this format.

backup in sqllocaldb

dharmaa.m12-Jul-13 7:57

dharmaa.m

12-Jul-13 7:57

hi,i have a problem!!!
How can I get the Sqllocaldb of database backup????
thankyou

Re: backup in sqllocaldb

NotPolitcallyCorrect12-Jul-13 8:36

NotPolitcallyCorrect

12-Jul-13 8:36

Try looking here[^]

Re: backup in sqllocaldb

dharmaa.m12-Jul-13 10:03

dharmaa.m

12-Jul-13 10:03

Thanks for the reply .but I do not want backup in managment studio.
I want the backup (sqllocaldb) to do the coding in Visual 2012.
is It possible??/

Mysql query with date conditions not working in server Pin

ven75312-Jul-13 4:11

ven753

12-Jul-13 4:11

i want to show report based on date condition and show it in gridview. It is working in my local machine but not working in server after hosting.

query is SELECT * FROM table WHERE DATE(colmn1) between 'startdate' and 'enddate'

What could be the problem.

If anybody knows please reply me.

Thanks in advance

Re: Mysql query with date conditions not working in server Pin

Eddy Vluggen12-Jul-13 4:52

Eddy Vluggen

12-Jul-13 4:52

ven753 wrote:
What could be the problem.

It could be the format of the date. Why is there a DATE-function on colmn1? Is it a varchar?

Please don't crosspost. The question should not be in the .NET forum.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Databse for broadcasting company

aman wangde11-Jul-13 12:34

aman wangde

11-Jul-13 12:34

i work in a broadcasting company i just need a suggestion to build a database for the company .The workflow is the data form nas,ftp,airbox, which will go in the database to validate the data and then it flow towards workstation is it possible ?

Re: Databse for broadcasting company

Eddy Vluggen12-Jul-13 7:57

Eddy Vluggen

12-Jul-13 7:57

aman wangde wrote:
The workflow is the data form nas,ftp,airbox

Look up what a workflow is.

aman wangde wrote:
then it flow towards workstation is it possible ?

Nas and FTP, sure, you can write applications to synchronize them with a database. What's an Airbox?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Export data in SQL 2008

vkEE11-Jul-13 9:05

vkEE

11-Jul-13 9:05

Hi,

I just used the Import Export functionality in sql 2008 to export a table having about one million rows. I have exported to a flat file, and delimited with pipes.

For some reason, about 23,000 rows below, the one of the fields expands and as a result pulls down to the next row, creating a blank row, the data hence after is fine. Now and then we are getting these blank rows.

I have confirmed this data is of length 40 characters long or less.

What could be causing the odd line breaks in the flat file?

any ideas appreciated!!

Thanks!

Re: Export data in SQL 2008

Richard Deeming11-Jul-13 10:40

Richard Deeming

11-Jul-13 10:40

Is there a carriage-return or line break embedded in the field?

If you can isolate one of the affected rows, try dumping the characters from the affected field:

SQL

DECLARE @FieldValue varchar(100);
SELECT @FieldValue = AffectedField FROM AffectedTable WHERE Key = @AffectedKey;

WITH E1(N) AS
(
    SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1
    UNION ALL SELECT 1 
    UNION ALL SELECT 1
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1
    UNION ALL SELECT 1
),
cteNumbers (N) As
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    FROM 
        E1 a, E1 b -- Cross-join = 100 rows
)
SELECT TOP(LEN(@FieldValue))
    N,
    Ascii(Substring(@FieldValue, N, 1)) As AsciiCode,
    Substring(@FieldValue, N, 1) As Character
FROM
    cteNumbers
ORDER BY
    N
;

You're looking for either a 10 or a 13 in the AsciiCode column.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

bcp command in sql 2008

vkEE11-Jul-13 6:45

vkEE

11-Jul-13 6:45

Hi,

What is wrong with this syntax?

I have used this so many times in sql 2005. Kind of new to sql 2008.
===
bcp "Select * From Webs.About" queryout mytest.dat -T -c
===

the error message is
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'queryout'.

Re: bcp command in sql 2008

NickPace11-Jul-13 7:10

NickPace

11-Jul-13 7:10

Not sure if this is it, but try enclosing mytest.dat in quotation marks. Otherwise, it looks like it should work to me.

-NP

Never underestimate the creativity of the end-user.

Re: bcp command in sql 2008

NickPace11-Jul-13 7:24

NickPace

11-Jul-13 7:24

Also, make sure you are running the bcp utility from the command prompt. I think if you try running it from a new query window within SMS that is exactly the error you will get. I'm pretty sure the quotation marks I mentioned in my previous post won't help unless the file name has a space in it.

-NP

Never underestimate the creativity of the end-user.

Re: bcp command in sql 2008

vkEE11-Jul-13 8:07

vkEE

11-Jul-13 8:07

How can I ran this through a new query window in SQL?

Using xp_cmdshell?

Thanks!!

Re: bcp command in sql 2008

NickPace11-Jul-13 9:23

NickPace

11-Jul-13 9:23

Sure, you can use xp_cmdshell to run a bcp command from a query window. You might have to first turn on the ability for SSMS to use a Windows command shell as it is usually off by default. If needed, run the following commands first:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

Then run your xp_cmdshell using: EXEC xp_cmdshell '(your bcp command here)'

If needed, turn back off the ability to use Windows command shell with:

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE

-NP

Never underestimate the creativity of the end-user.

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