Database Discussion Boards

Member 114130581-Feb-15 5:23

1-Feb-15 5:23

I did connection with oracle using linked server . it works fine. and when i insert record from sql to oracle .it only transfer one record .i need to transfer more than one.

Re: how can i transfer data

Umer Akram29-Jan-15 18:52

Umer Akram

29-Jan-15 18:52

you can also look into Sql Server Integration Services (SSIS) if you have high data volume for better performance plus its a great tool to learn as well if you are interested in ETL and related working.

Re: how can i transfer data

Member 114130581-Feb-15 5:24

Member 11413058

1-Feb-15 5:24

MY manager wants me to do using linked server

Re: how can i transfer data

Umer Akram3-Feb-15 18:43

Umer Akram

3-Feb-15 18:43

Proceed accordingly then Wink | ;)

and best of luck.

Modify the computed expression of computed column

indian14327-Jan-15 6:41

indian143

27-Jan-15 6:41

Hi All,

I have a database table which I want to modify the computed expression of a column, Computed expression of the column was as below

(case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0) when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then [AnnualFederalPovertyLevel] when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then [MonthlyFederalPovertyLevel] when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then [MonthlyFederalPovertyLevel] else [AnnualFederalPovertyLevel] end)

Now I want to change it to

(case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0) 
	when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then 
	(CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END)
	when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then 
	(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END) 
	when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then 
	(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END) 
	else (CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END) end)

I have tried in the below way, but it didn't work gave me the following error

ALTER TABLE dbo.ApplicationData DROP COLUMN ReportingFpl;
GO
ALTER TABLE dbo.ApplicationData ADD ReportingFpl AS (case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0) 
	when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then 
	(CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END)
	when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then 
	(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END) 
	when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then 
	(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END) 
	else (CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END) end);

Error

Msg 5074, Level 16, State 1, Line 3
The index 'IX_ApplicationData_ApplicationId-ReportingFpl_PriorityPoints-ContractorPriorityPoints' is dependent on column 'ReportingFpl'.
Msg 4922, Level 16, State 9, Line 3
ALTER TABLE DROP COLUMN ReportingFpl failed because one or more objects access this column.
Msg 2705, Level 16, State 5, Line 1
Column names in each table must be unique. Column name 'ReportingFpl' in table 'dbo.ApplicationData' is specified more than once.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Re: Modify the computed expression of computed column

Richard Deeming27-Jan-15 7:02

Richard Deeming

27-Jan-15 7:02

As the first error says, you have an index on the table called IX_ApplicationData_ApplicationId-ReportingFpl_PriorityPoints-ContractorPriorityPoints which uses the computed column, so you can't drop the column without dropping the index first.

Find any indexes which use the column;
Generate both a drop and a create script for each affected index;
Run the index drop scripts;
Drop your column;
Add the new version of the column;
Then run the index create scripts.

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

Re: Modify the computed expression of computed column

deepakdynamite28-Jan-15 0:27

deepakdynamite

28-Jan-15 0:27

Hello,

I don't think it will allow you to change Computer column expression on the Fly... Instead add new column with new expression and name it as newColumn. Delete you old column and then rename newColumn to Original column name using below command.

SQL

sp_RENAME 'Table_First.newColumn', 'YourOriginalColumnName' , 'COLUMN'

BLOB or external storage...

Kornfeld Eliyahu Peter26-Jan-15 20:57

Kornfeld Eliyahu Peter

26-Jan-15 20:57

If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage?
The facts are known about those files:
1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP)
2. No search inside the files are required
3. Files are acceded mostly (95% of the time) directly
4. History of files are important

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: BLOB or external storage...

den2k8826-Jan-15 21:32

den2k88

26-Jan-15 21:32

Blobs put heavy weight on the tables, the bigger they are the slower the operations. Also if anything corrupts on one file you'd have to restore a DB table instead of a single file or directory.

Also it would be more scalable as it would allow you to use different backend servers to store the files and have a rough load balancing/availability service without heavily involving the DB server, all at the advantage of performance.

Geek code v 3.12
GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- r++>+++ y+++*
Weapons extension: ma- k++ F+2 X

Re: BLOB or external storage...

GuyThiebaut26-Jan-15 21:48

GuyThiebaut

26-Jan-15 21:48

I would go for external storage.

You can set up a series of meta tables with locations/mappings of drives and folders and store the files outside of the database.
You could also create a series of stored procedures to check that the files are in the locations held in the database, every day or so, reporting back on discrepancies - just to catch the rare occasion where a file has been moved or deleted.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Re: BLOB or external storage...

Jörgen Andersson26-Jan-15 22:13

Jörgen Andersson

26-Jan-15 22:13

What are you most comfortable with?

I would personally go for BLOBs.
Pros:

You get one point of backup.
Lowered chance of tampered or missing files.
You only need to set permissions in one place

Cons:

If badly implemented performance will suffer
If your database is stored on a filesystem you will get an overhead
No direct access to files

There's a paper[^] from MS on the subject

Wrong is evil and must be defeated. - Jeff Ello

Re: BLOB or external storage...

Richard Deeming27-Jan-15 1:50

Richard Deeming

27-Jan-15 1:50

Just to confuse matters, you might also want to look at file tables: Smile | :)

FileTables (SQL Server)[^]
SQL Server 2012 FileTable: My first experience[^]
Using FileTables in SQL Server 2012[^]

FileTables (SQL Server)[^]:

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

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

Re: BLOB or external storage...

Jörgen Andersson27-Jan-15 3:06

Jörgen Andersson

27-Jan-15 3:06

Cool.

Wrong is evil and must be defeated. - Jeff Ello

Re: BLOB or external storage...

deepakdynamite28-Jan-15 0:30

deepakdynamite

28-Jan-15 0:30

If you are using SQL SERVER 2012 then what do you say about using File Table

Datediff function

Ambertje26-Jan-15 2:18

Ambertje

26-Jan-15 2:18

Hello,

I want to calculate the difference between 2 dates and times in seconds.

SQL

, Datediff(SECOND,
        convert(datetime, [START_DATE] + ' ' + [START_TIME]),
        convert(datetime, [END_DATE] + ' ' + [END_TIME]))                                               AS M_Time_Worked

Nog problem with the function but when I come across a record where the Start_Date is empty then I get an overflow (I know that you can calculate max 68 years worth of seconds)

I've tried to use a Case statement but it wont work like this:

SQL

, CASE WHEN START_DATE IS NULL THEN START_DATE = END_DATE
    ELSE
    Datediff(SECOND,
        convert(datetime, [START_DATE] + ' ' + [START_TIME]),
        convert(datetime, [END_DATE] + ' ' + [END_TIME]))   END                                         AS M_Time_Worked

Does anyone know how I can handle empty values for a DateDiff() please?

Kind regards,
Ambertje

Re: Datediff function

Chris Quinn26-Jan-15 2:25

Chris Quinn

26-Jan-15 2:25

SQL

, Datediff(SECOND,
        convert(datetime, [START_DATE] + ' ' + [START_TIME]),
        convert(datetime, isnull([END_DATE],[START_DATE]) + ' ' + isnull([END_TIME],[START_TIME])))                                               AS M_Time_Worked

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: Datediff function

Ambertje26-Jan-15 2:34

Ambertje

26-Jan-15 2:34

SQL

declare @t table(START_DATE varchar(10), END_TIME_1 varchar(8), START_TIME_1 varchar(8), END_DATE varchar (10))
insert @t values('', '20:12:52', '', '01/18/2015')

select Datediff(SECOND,
             convert(datetime, [START_DATE] + ' ' + [START_TIME_1]),
             convert(datetime, [END_DATE] + ' ' + [END_TIME_1]))
from @t

Re: Datediff function

Chris Quinn26-Jan-15 2:42

Chris Quinn

26-Jan-15 2:42

That is because you are storing empty strings, not null values. You also have two empty times, which wil cause problems with the query.

You also should not store dates and times as strings - use the DateTime column type provided to hold them properly.

SQL

declare @t table(START_DATE DateTime, END_DATE DateTime)
insert @t values ('01-jan-2015 20:12:52', Null)

select Datediff(SECOND,[START_DATE], ISNULL([END_DATE],[START_DATE]))
from @t

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: Datediff function

Ambertje26-Jan-15 3:12

Ambertje

26-Jan-15 3:12

Hi Chris,

I need my dates as string because I'm working with a Data warehouse DB.

SQL

, CAST(Replace(convert(date, [START_DATE], 101), '-', '') as numeric(8,0))                              AS [START_DATE]

, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.START_TIME,1,8)), 6, 0)AS nvarchar(6))   AS START_TIME

, CAST(Replace(convert(date, END_DATE, 101), '-', '') as numeric(8,0))                                  AS END_DATE

, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.END_TIME,1,8)), 6, 0)AS nvarchar(6))     AS END_TIME

--DATEDIFF(interval, Starting_date datetime, ending_date datetime)
,Datediff(SECOND,
     convert(datetime, [START_DATE] + ' ' + [START_TIME]),
     convert(datetime, [END_DATE] + ' ' + [END_TIME]))                                                  AS M_Time_Worked

This is actual code where I Cast all dates to nvarchar.

So how can I change this code in a way that I can set the Start_Date (when empty) to the value of End_Date and Start_Time to the value of End_Time when empty?

I'm not an experienced developer, I'm still learning and hope you can learn me something new.

Kind regards,
Ambertje

Re: Datediff function

Chris Quinn26-Jan-15 3:18

Chris Quinn

26-Jan-15 3:18

SQL

,Datediff(SECOND,
     convert(datetime, [START_DATE] + ' ' + [START_TIME]),
     convert(datetime, CASE WHEN RTRIM([END_DATE]) = '' THEN [START_DATE] ELSE [END_DATE] END + ' ' + CASE WHEN RTRIM([END_TIME]) = '' THEN [START_TIME] ELSE [END_TIME] END))                                                  AS M_Time_Worked

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: Datediff function

Ambertje26-Jan-15 3:26

Ambertje

26-Jan-15 3:26

Still an error:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Re: Datediff function

Jörgen Andersson26-Jan-15 21:17

Jörgen Andersson

26-Jan-15 21:17

Ambertje wrote:
I need my dates as string because I'm working with a Data warehouse DB.

Why would the date format be a problem in a Data Warehouse?

Wrong is evil and must be defeated. - Jeff Ello

Re: Datediff function

PIEBALDconsult26-Jan-15 5:57

PIEBALDconsult

26-Jan-15 5:57

Are you looking for ISNULL(START_DATE , END_DATE) ?

Re: Datediff function

Ambertje26-Jan-15 22:21

Ambertje

26-Jan-15 22:21

Good morning to you all,

It's Start_Date and Start_Time that can be empty.
So I think ISNULL(Start_Date, Start_Time) is what I need.

How can I implement this in my code?

SQL

,Datediff(SECOND,
     convert(datetime, [START_DATE] + ' ' + [START_TIME]),
     convert(datetime, [END_DATE] + ' ' +   END_TIME]))                                                  AS M_Time_Worked

Kind regards,
Ambertje

Re: Datediff function

Corporal Agarn27-Jan-15 3:46

Corporal Agarn

27-Jan-15 3:46

Ambertje wrote:
So I think ISNULL(Start_Date, Start_Time) is what I need.

That is not how ISNULL works. You would need something like

SQL

ISNULL(Start_Date, EndDate) + ' ' + ISNULL(Start_Time, Endtime)

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