Database

Re: Unable to write data on to the Excel - Resolved

9-Mar-16 4:19

OK I got an Idea I will create a template file with headers then can I append the contents of the headers file + the output file of the bcp into a final file so that I can have both the headers and content.

Or you can give me how to append row or data in the beginning of the file.

Can you please give me some suggestion or link or code snippet to append header file + content file into a final file using bcp or command shell etc?

Any help is appreciated, thanks in advance I am also searching for it.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

modified 9-Mar-16 12:00pm.

indian1439-Mar-16 7:19

Re: Unable to write data on to the Excel - limitation

9-Mar-16 7:19

Thanks buddy, finally at last I could able to do it man !... hah.

Here is my code to do it may be can be helpful to somebody else, first I wrote a query to just print Headers and then query to print content then I appended those two files with cmd shell into one final file

DECLARE @cmd VARCHAR(8000)='',
@sqlContentQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@sqlTemplQuery varchar(max) = '"Select ''WA ProviderId'' WebAdmProviderId, ''WA Last Name'' LastName, ''WA First Name'' FirstName, ''WA Date of Birth'' Dob, '
+ '''PS ProviderId'' WebAdmProviderId, ''PS Last Name'' LastName, ''PS First Name'' FirstName, ''PS Date of Birth'' Dob"',
@ContentFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Content.xls"',
@TemplFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\TemplateFile.xls"',
@FinalFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Final.xls"'

SET @cmd = 'bcp '+ @sqlTemplQuery + '  queryout ' + @TemplFilePath + ' -c -C -T'
Exec xp_cmdshell @cmd

SET @cmd = 'bcp '+ @sqlContentQuery + '  queryout ' + @ContentFilePath + ' -c -C -T'
Exec xp_cmdshell @cmd

SET @cmd = 'copy  ' + @TemplFilePath +' + '+ @ContentFilePath +' ' + @FinalFilePath + ''
exec master..xp_cmdshell @cmd

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

modified 9-Mar-16 19:55pm.

indian1439-Mar-16 10:38

Migrate columns which have carriage return and line feed

9-Mar-16 10:38

But one small limitation here, bcp is creating files only in xls format (97-2003) but any body know how to do the same thing to get document in xlsx format like (2007) format? For now I can live with it but just want to acquire more knowledge on it.

Any help is appreciated, thanks in advance friends Smile | :)

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

modified 9-Mar-16 19:57pm.

indian1437-Mar-16 12:02

Re: Migrate columns which have carriage return and line feed

7-Mar-16 12:02

Hi All,

I am migrating data from one table another table in different databases. When source table has line feed and carriage return the destination table is not getting that. All I am doing is inserting the records using select statements.

Can anybody please help me by giving a suggestion how to select columns by preserving line feed and carriage return characters. I am also searching if you know sure that would be helpful.

Thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Richard MacCutchan7-Mar-16 22:04

Richard MacCutchan

7-Mar-16 22:04

Your code must be doing something to change the data. The SQL should return exactly what is stored in the database for each record.

Re: Migrate columns which have carriage return and line feed

CHill608-Mar-16 2:26

CHill60

8-Mar-16 2:26

Can you share a sample of the code?

Re: Migrate columns which have carriage return and line feed

indian1438-Mar-16 7:36

How to store procedure or function to use in asp.net

8-Mar-16 7:36

Sorry, yeah it was code issue, thanks for all your support friends.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

hmanhha4-Mar-16 15:52

Re: How to store procedure or function to use in asp.net

4-Mar-16 15:52

Sorry for a so stupid question.I am new beginer in C# and ASP.net.

Now I am writing the code for my web connect to database.
I am confuse about the function and procedure.

In VB, I store it in .vb file and then I can call in any form.

How can I do the same in website project asp.net.

Richard MacCutchan4-Mar-16 22:22

Richard MacCutchan

4-Mar-16 22:22

Much the same, see ASP.NET Database Connection[^].

How to get exactly date from Oracle Link DB in SQL server.

hmanhha3-Mar-16 16:07

Re: How to get exactly date from Oracle Link DB in SQL server.

3-Mar-16 16:07

Hi All.
I have SQL Server, link to Oracle DB.
In the SQL server I have a T-SQL command

select Moto.ne_code,
Moto.supplier_id,
Moto.ne_type_id,
Moto.sta_datetime,
from PMCALCULATE..[PM_TOOL].[MOTOROLA_CELL_HOURLY_KPI] Moto

and I get the result.
But the sta_datetime in the result is not ok

it is similar to: 2016-02-03 00:00:00 00000000

How to format it to yyyy/mm/dd hh24

Mycroft Holmes3-Mar-16 19:18

Re: How to get exactly date from Oracle Link DB in SQL server.

3-Mar-16 19:18

What you are getting is a DATETIME data type which is correct. If you want to display it in a particular format then user CONVERT to get the format you need.

You MUST store the data in your SQL Server table as DATETIME, NEVER store DATETIME as a VARCHAR.

Never underestimate the power of human stupidity
RAH

hmanhha3-Mar-16 22:53

Re: How to get exactly date from Oracle Link DB in SQL server.

3-Mar-16 22:53

I have try

select
convert(datetime2(0),Moto.sta_datetime) as Sta_Date1,

convert(datetime,Moto.sta_datetime) as Sta_Date2,

But the result is not as i want
All The result is:2016-02-03 00:00:00
I Want date only and time only

How can I do this.

Richard MacCutchan3-Mar-16 23:22

Richard MacCutchan

3-Mar-16 23:22

See the options at CAST and CONVERT (Transact-SQL)[^].

Re: How to get exactly date from Oracle Link DB in SQL server.

Mycroft Holmes4-Mar-16 12:52

Re: How to get exactly date from Oracle Link DB in SQL server.

4-Mar-16 12:52

Where are you using the result:
storing it in the database - do NOT format the data
displaying it in a UI - the UI needs to do the formatting
using it in a report - the report needs to do the formatting
exporting it to a file - this is the ONLY scenario where you should convert the datetime to a string (varchar) convert(varchar(20),DateValue,103) is an example.

Never underestimate the power of human stupidity
RAH

hmanhha6-Mar-16 16:52

Re: How to get exactly date from Oracle Link DB in SQL server.

6-Mar-16 16:52

I only Want to get Yesterday data from Oracle store in the SQL database.So that why I want to get exacly date to compare with the date in the SQL Database.

Mycroft Holmes6-Mar-16 19:56

Re: How to get exactly date from Oracle Link DB in SQL server.

6-Mar-16 19:56

If Oracle has a DATE type (no time component) then in your query convert the datetime to date and filter the incoming values. Other wise use a range of datetime to determine the date you need > '2016-4-1 11:59:59' and < '2016-4-3 00:00:01'

Never underestimate the power of human stupidity
RAH

hmanhha6-Mar-16 22:09

Re: How to get exactly date from Oracle Link DB in SQL server.

6-Mar-16 22:09

Now I Use T-SQL

select
Moto.ne_code,

Moto.sta_datetime,

convert(varchar(10),sta_datetime,111) as Sdate,
convert(time,sta_datetime) as S_Time,
Moto.province_id,
Moto.ne_id,
bss_cssr cssrv1,
tchtc traffic,
cssr cssrv2,
hosr_v2 hosrv2,
dropc dcr,
dcr_v2 dcrv2,
cavl calvol,
hansr hosr,
tchblor tchblr,
sdcchbr sdcchblkr

from OracleDBLink
where

convert(varchar(10),sta_datetime,111) = CONVERT(varchar(10), getdate()-1,111)
and ((bss_cssr <=95) or (dropc>=3) or (hansr<=95)) and cavl>=50

it seem to be OK get the result as I Want.

But I can Put it in the View.
I Want to creat a job to do it everytime

Jörgen Andersson7-Mar-16 6:35

Jörgen Andersson

7-Mar-16 6:35

Oracle has two major temporal types, Date and Timestamp, where Timestamp has higher precision but less functionality.
The easiest way to get a date without the time component is Trunc(MyDate)

Wrong is evil and must be defeated. - Jeff Ello

Convert Text to datetime

Jassim Rahma2-Mar-16 10:29

Jassim Rahma

2-Mar-16 10:29

Hi,

I have below date text and I want to know how can I convert it to valid MySQL datetime which is yyyy-mm-dd HH:mm

Nov 20 2015 10:51AM
Nov 20 2015 10:16PM
Nov 20 2015 10:01AM
Nov 20 2015 8:43PM
Nov 20 2015 8:04PM
Nov 20 2015 5:49PM
Nov 20 2015 5:38PM
Nov 20 2015 5:23PM
Nov 20 2015 5:03PM
Nov 20 2015 4:57PM
Nov 20 2015 2:10PM
Nov 20 2015 1:51PM
Nov 19 2015 12:24AM
Nov 19 2015 11:56AM
Nov 19 2015 11:32PM
Nov 19 2015 11:27PM
Nov 19 2015 10:15AM
Nov 19 2015 10:12PM

How can I do this please?

Thanks,
Jassim[^]

Technology News @ www.JassimRahma.com

Re: Convert Text to datetime

NickPace2-Mar-16 12:28

NickPace

2-Mar-16 12:28

This should work:

SELECT DATE_FORMAT(STR_TO_DATE('Nov 20 2015 10:51AM', '%b %d %Y %h:%i%p'), '%Y-%m-%d %H:%i'); #Returns 2015-11-20 10:51
SELECT DATE_FORMAT(STR_TO_DATE('Nov 20 2015 8:43PM', '%b %d %Y %h:%i%p'), '%Y-%m-%d %H:%i'); #Returns 2015-11-20 20:43

-NP

Never underestimate the creativity of the end-user.

Re: Convert Text to datetime

Mycroft Holmes2-Mar-16 19:07